Data Types


You can use the DataType property to specify the type of data stored in a table field. Each field can store data consisting of only a single data type.
You can set this property only in the upper portion of table Design view.
In Visual Basic, you can use the ADO Type property to set a field's data type before appending it to the Fields collection.
Memo, Hyperlink, and OLE Object fields can't be indexed.


Set the Format property to specify a predefined display format for Number, Date/Time, Currency, and Yes/No data types.


Changing a field's data type after you enter data in a table causes a potentially lengthy process of data conversion when you save the table. If the data type in a field conflicts with a changed DataType property setting, you may lose some data.



Summary

Data TypeRange 
ARRAY  
BINARYfixed length binary maximum length 8,000 bytes(Access only)
Enables you to store any type of data in a field. No transformation of the data is made in this type of field.
BIGINT-2 E 63 to 2 E 638 bytes (64 bits) - Do not use it unless you really need the extra capacity.
BITTrue or False
Yes or No
On or Off
Integer, Either 1 or 0. This is BINARY in Access
CHAR (Access Only)
A fixed length maximum of 8,000 chars. Using CHARS are faster than VARCHARs. CHAR(n) are fixed length single byte character strings
CHARACTER (SQL Server only)
A fixed length data type that stores a combination of text and numbers up to 255 characters. This is CHAR in Access
COUNTER Stores a long integer value that automatically increments whenever a new record is inserted.
CURSOR Reference to a cursor object.
DATE (Added SQL-92)
DATETIME100 to 9999 years8 bytes (64 bits)
DATETIME2 (SQL Server only)
DATETIMEOFFSET (SQL Server only)
DECIMAL-10E28 - 1 to 10E28 - 1Fixed-precision scale numeric data. The synonym is NUMERIC
DEC  
DOUBLE PRECISION  
FLOATFloating precision -1.79E+308 to 1.79E+3088 bytes (64 bits) - Stores double-precision floating point values. The synonym for FLOAT is DOUBLE PRECISION and FLOAT[(n)]. Should not be used as primary keys, integers should be used instead. FLOAT closely resembles Oracles NUMBER datatype
IMAGEvariable length binary maximum length 2^31-1Used to store Object Linking and Embedding (OLE) objects. For example pictures, audio and video.
INT  
INTEGER-2.147 E31 to 2.147 E314 bytes (32 bits)
INTERVAL Added SQL-92
MONEY-2^TO632^63-18 bytes (64 bits) - Stores currency values and numeric data used in mathematical calculations.
NCHAR NATIONAL CHAR Text fixed length unidcode maximum of 4,000 chars
NUMERIC-10E28 - 1 to 10E28 - 1Fixed precision / scale numeric data
NVARCHAR Text variable length unicode maximum of 4,000 chars NATIONAL CHARACTER VARYING
NTEXT Text variable length unicode maximum of 2^30-1 chars
REALFloating precision -3.40E+38 to 3.40E+384 bytes (32 bits) - Stores single-precision floating point values. The synonym for REAL is FLOAT[(n)]. Rounding errors can occur when using the FLOAT or REAL datatypes
SMALLDATETIME Jan 1, 1900 - Jun 6, 2079
SMALLINT-2 E 15 to 2 E 15-32,768 to 32,767
SMALLMONEYMoney -214,748.36 to 214,748.364 bytes (32 bits)
SQL_VARIANT These can store any datatype except TEXT or IMAGE. You should try and avoid using this. It cannot be part of a primary or foreign key. It cannot be used in any calculations
TABLE Stores a result set for later processing
TEXT A variable length data type that stores a combination of text and numbers up to 255 characters. The length is determined by the Field size property. The sting can contain ASCII characters including letters, numbers special characters and non printing characters. Text variable length maximum of 2^31-1 chars. This is the equivalent to Oracle's LONG. Rarely used
TIME (Added SQL-92)
TIMESTAMP (Added SQL-92)
TINYINT0 to 2551 byte (8 bits)
UNIQUEIDENTIFIER A unique identification number used with remote procedure calls. Stores a globally unique identifier, GUID
VARBINARY variable length binary maximum length 8,000 bytes. RAW is the equivalent Oracle's data type
VARCHAR Text variable length maximum of 8,000 chars. The synonym is CHAR VARYING
XML It must conform to well-formatted XML criteria (which is untyped) and you have the option of specifying a schema (typed)

When working with decimals you can specify the precision (the total number of digits stored) and the scale (the maximum number of digits to the right of the decimal point)




Dates

The numeric representation of dates is called Julian (or serial) date
Access designates day 0 as 12/30/1899 and increments all other dates starting from this date
For example 7/7/93 is stored as 34157 which represents 34,157 days since 12/30/1899
Negative numbers represent dates prior to 12/30/1899.



Times

Times in Access are stored as fractions of a day
An hour is equivalent to 1/24 of a day (0.041667)
A minute is equivalent to 1/1440 of a day (0.000694)
For example 3:00am is stored as 1/8 of a day (or 0.125)



Displaying yesterdays date (SQL Server)
SELECT DATEADD(dd,-1,DATEADD(dd,0,DATEDIFF(dd,0,GETDATE())))


--SQL Server Dates are always saved in this format so use this format when quering AND dateadd(minute, @UtcOffsetMin, [ServiceInstance/StartTime]) > '11-Mar-2009'



SQL Server Date/Time Functions

DATEADD 
DATEDIFF 
DATENAME 
DATEPART 
DAY 
GETDATE 
GETUTCDATE 
MONTH 
YEAR 

there are three main techniques available to handle image files in tables with MS Access and these are:
Store the image in an OLE field and use a bound object frame to display the image.
Store the path to the image in a text field, using an image control to display the image.
Store the image as a binary large object bitmap (BLOB) in an OLE field, extract the image when required and use an image control to display the image.


Each technique has advantages and disadvantages. However, the first technique is by far the most problematic as judged by the number of newsgroup posts complaining about OLE (Object Linking and Embedding) server errors (this is the application which is used to display the image e.g. MS Paint) and numerous other problems displaying the images. Another often cited complaint regarding this technique concerns the increase in the database size, resulting from storing images in this way due to the fact that Access creates and stores a bitmap version of the image as well. For example, I created an MS Access 2000 mdb with a single table consisting of two fields; "id" (an auto number field) and "image" (an OLE field). I then used the Access form wizard to create a single form, which allows images to be added and displayed.
Figure 1 shows a screenshot of the form created by the wizard. The overall size of the mdb was 124kb without any images. Next, I opened a 4kb gif in Internet Explorer and placed the mouse over the image and selected "copy" from the pop-up menu. After opening the database form and pasting the image into the object frame, I compacted and closed the database and rechecked the mdb file size, which was now 156b. A size increase of 32kb may not sound much but take into consideration the fact that the original image was only 4kb and also consider the overall increase in size when adding a large number of images. This technique takes minutes to set up and can be accomplished without writing any code, but it is really only feasible in situations where a relatively small number of images need to be stored.


The second technique is generally regarded as the preferred technique and is fairly easy to implement. The path to the image is stored in a text field and the image is displayed using a standard image control. All that has to be done is to set the image control's "picture" property to the path that we stored, which can be achieved using VBA. This technique keeps the database size to a minimum, increasing the overall performance of the application. To make this system more user friendly I would recommend that the "file open" dialog (see "Further Help" below for more details) be used to allow the user to browse to an image rather than laboriously entering the path. One disadvantage of using this technique comes to light when using continuous forms in Access. Unfortunately when we set the image control's "picture" property to the path that we stored on a continuous form it becomes apparent that we are unable to distinguish between each individual image control and we find that the same image is displayed for each record.


The easiest way around this is to use single forms only and to update the image control's "picture" property when browsing to the next/previous image (you could use the form's "Form_Current" event to accomplish this - see the listing in Figure 2). Another disadvantage with this technique is that the images cannot be password protected in situations where they need to be stored securely as the images are not stored inside the database file.


The final technique can be tricky to set up but has the advantage of storing images inside the database, meaning that password protection is possible and also avoids the bloat associated with the first technique, as the images are stored reflecting their true size. For this technique I would again recommend using the "file open" dialog, allowing the user to browse to an image. We can use either DAO or ADO (in Access 2000 or 2002) to grab the image file and read it into the OLE field in chunks. To display the image we have to extract it from the database to a temporary file and like the second technique, use an image control to display it by setting the control's "picture" property to the path of the temporary file. Once we are finished with the temporary file we can use the VBA "kill" command to delete it.
Unfortunately, as with the second technique, the same problem with Access continuous forms exists. If displaying the images using single forms is not adequate you could take the time to create a customized form to display more than one individual image, although unfortunately this task is made awkward by the fact that Access does not support control arrays. In addition, this technique also incurs a marginal overhead when compared to the second, as the image files first have to be extracted from the database before we can view them.


On a final note, of the three techniques discussed, I prefer to use the last technique as the images are neatly stored away inside the database file and we do not have to suffer the bloat associated with the first. For a working example of storing images as binary large object bitmaps (BLOB) in an OLE field, please feel free to download and evaluate my Access Image Albums application. A free example of storing files in this way using DAO with viewable source code can be found by downloading the getBinary zip file , an Access 97 file that can also be converted to Access 2000 or 2002 depending on which version you are running. All links for the sources, downloads and further help can be found below.


Sources
Storing files as binary large object bitmaps (BLOB) in Access using DAO: MS Knowledge Base Article Q103257
Storing files binary large object bitmaps (BLOB) in Access using ADO: VB Square Article





© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext