UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Datatype    
Image:NotifInfo.gif This page has been marked as requiring review

Complete review requested: Author inexperienced in Access and Databases.

If this page has been reviewed please remove the {{REVIEW}} template from the page's source markup.

Why are datatypes necessary? One of the aims of data management is efficiency. If someone calls out "I've thrown something towards you", you need to rapidly analyse the properties of the object to decide how to deal with it. If you know these properties in advance, you can react efficiently. For a small, clean, edible berry which you like, you might feel confident to catch it in your mouth, thereby completing a highly efficient throw and catch. DataTypes allow data management systems to store and transmit data efficiently, because they know the properties in advance.

Where are DataTypes used? The main property for a Field in an Access Table is the DataType, which defines the data structure of the Field. If you attempt to input data which is beyond the constraints of the field's datatype, you will receive an error when the Field loses focus. If you use the Access Interface to create a Table, you must set the DataType for each Field before Access will display its Field Properties. Not all of the Field Properties you see here are part of the DataType. Datatypes are also referenced in code used to access the data, such as SQL and VBA, often with Synonyms.

Example DataType: YES/NO. This simple DataType has only one Property, which is a Value constraint. A YES/NO piece of data must have exactly one Value, from a list of two possibilities.

What is a DataType? It is a pre-defined set of properties which are available to describe a piece of data. Each property in the set has strictly-defined limitations.

Datatype Synonyms: when accessing your data or passing it between different languages, the datatype is passed along too. Each programming language has a list of datatypes which it can handle, often with Synonyms for the Field's "real" Datatype. You must use a valid synonym for the language you are using, and ensure it corresponds to the "real" Datatype of the field you are dealing with.

Setting or Modifying a field's Datatype:When you create a field, it will be assigned the default datatype unless you specify otherwise. Datatypes can be created or modified in several ways: Via the Access interface By using DDL to execute SQL. Through DAO or ADOX. When importing or linking Tables, the datatype is included in the field definition. The above list may be incomplete.

Setting Field Datatypes: You can create fields with different datatypes in the Access table creation interface. To obtain some datatypes you must first set the datatype and then adjust the field properties. Example: To set a field to Decimal datatype, you first set the datatype to Number, then set the Field Size to Decimal. Some datatypes which Access can recognise cannot be set via the interface, but only by using DAO, ADO or ADOX, or by joining a table with a field of the required datatype. Some datatypes which are available in other database applications are not available in Access, for example TimeSpan.

Complex Datatypes: Some datatypes can be stored as lists of multiple values within the same field. If you choose to use this feature, you should be aware that it affects the Normalisation of your database, and can cause other issues down the line. A search for "Look-up Fields in MS Access" will provide more information on this.

Datatype Synonyms: Datatype names are not consistent. Different database storage applications use different names, as do the various languages available to access the data (DDL, DAO etc.) So great care should be taken to ensure that you know which set of properties and constraints applies to your data when storing and accessing it. Example: Access 2010 constrains the Text datatype to 0-255 characters. From Access 2013 on, this datatype is called "Short Text". DAO calls it "dbText", and ADOX calls it "adVarWChar"

Linked fields Datatype compatibility: In most cases, linked fields should have the same datatype, otherwise "Type Mismatch" Errors will occur. One exception is the Autonumber field. If you are linking to Autonumber fields, you should set the datatype of the linked field to Long Integer. In the interface, this is available as a Field Size of the "Number" Datatype. Although it may be possible to link Autonumber fields to Text fields, it will greatly limit the functionality available to your link.

Data Loss: Mishandling datatypes can cause loss or truncation of data. For example, if you change a Field Size from LONG Text to Short Text, any records which held more than 255 characters will be truncated. Changing data back and forth between Number Datatypes may also change the data, sometimes radically. Inconsistent datatypes across multiple tables with the same fields may also cause data loss (see [1]).

Datatype Conversion: Type Conversion Functions are available in VBA to convert DataTypes, for example CStr.

Date/Time DataType Issues: This DataType requires particular care. Access can introduce errors when you input dates, because in some cases it has to guess which order you have entered Day and Month. Type conversion with dates may also produce inaccurate results, particularly if Windows Regional Settings are involved in the Date/Time format.

Decimal Separator. If your Windows Regional Settings use a Decimal Separator other than the Decimal Point "." you should document yourself on how to ensure this works correctly.

Edit Discussion
Custom Search

Thank you for your support!
This page has been accessed 2,122 times.  This page was last modified 22:44, 19 June 2018 by Jaiket.   Disclaimers