Access's datatypes

[Bonj]

Does anyone know of a link to a list of all Access's datatypes, ideally
showing name, how many bytes each of them takes up, and min/max values.
Must be specifically for Access.

 

[Rick Rothstein]

Does anyone know of a link to a list of all Access's datatypes,
ideally

showing name, how many bytes each of them takes up, and min/max values.
Must be specifically for Access.

Are you asking for this?

http://msdn.microsoft.com/library/d...y/en-us/odbc/htm/odbcjetaccess_data_types.asp

Rick - MVP

 

[Bonj]

mmmm... it *nearly* is. I basically could do with a list of all the
sub-datatypes within 'Number' - and how many bytes they take up. For
instance, how many bytes does a "Number (Single)" (SQL_REAL) take up? It's
just to show somebody who's convinced that we can save space in our
(*apparently* space-critical) Access system by using a smallint divided by
1000 instead of a float for percentages and what not, and he just will not
let it drop until I prove that we are absolutely optimizing the size of the
DB. (the Access database is only 77MB at the moment...not exactly bursting at
the seems, but apparently it will eventually expand such that we've got so
many databases they won't fit on a CD, which is how they are going to be
distributed.)
If I can show that a float takes up the same size as a 16-bit int, then I
won't have to bother changing it, which would be a right P.I.T.A as it will
probably have absolutely zero impact on anything if I do - but I suspect it
takes up 32 bits.... (sigh)... but still. I have to look into it.

 

[Bill Edwards]

Look at Access online help ( DataType Property and FieldSize Property)
Below is cut and paste from the FieldSize Property help topic (Access
2000)(Not sure how it will format):

Byte Stores numbers from 0 to 255 (no fractions). None 1 byte
Decimal Stores numbers from -10^38 -1 through 10^38 -1 (.adp)
Stores numbers from-10^28 -1 through 10^28 -1 (.mdb) 28 12bytes
Integer Stores numbers from -32,768 to 32,767 (no fractions). None 2
bytes
Long Integer (Default) Stores numbers from -2,147,483,648 to
2,147,483,647 (no fractions). None 4 bytes
Single Stores numbers from
-3.402823E38 to
-1.401298E-45 for negative values and from
1.401298E-45 to 3.402823E38 for positive values. 7 4 bytes
Double Stores numbers from
-1.79769313486231E308 to
-4.94065645841247E-324 for negative values and from
1.79769313486231E308 to 4.94065645841247E-324 for positive values. 15 8
bytes
Replication ID Globally unique identifier (GUID) N/A 16 bytes

 

[Allen Browne]

Sizes:
--------
YesNo 1 byte (-1 for True, 0 for False, Null not permitted).
Byte 1 byte, unsigned (0 to 255).
Integer 2 byte, signed (-32768 to 32767)
Long 4 byte, signed
Single 4 byte, signed
Double 8 byte, signed
Currency 8 byte (fixed point number, 4 places)
Date/Time 8 byte
ReplicationID 16 byte
Decimal user-configurable

Then there's the overhead for managing the column: 1 extra byte for each
Number field, as well as other overhead per record.

To verify the field size, use:
? CurrentDb().TableDefs("MyTable").Fields("MyField").Size

Changing longs to integer will not improve performance, unless you are still
on a 16-bit o/s (Windows 3.x), and working with the Byte type will be even
slower.

For a summary of names and constants used in the interface, DDL, DAO and
ADOX, see:
http://members.iinet.net.au/~allenbrowne/ser-49.html

 

[Ralph]

mmmm... it *nearly* is. I basically could do with a list of all the
sub-datatypes within 'Number' - and how many bytes they take up. For
instance, how many bytes does a "Number (Single)" (SQL_REAL) take up? It's
just to show somebody who's convinced that we can save space in our
(*apparently* space-critical) Access system by using a smallint divided by
1000 instead of a float for percentages and what not, and he just will not
let it drop until I prove that we are absolutely optimizing the size of the
DB. (the Access database is only 77MB at the moment...not exactly bursting at
the seems, but apparently it will eventually expand such that we've got so
many databases they won't fit on a CD, which is how they are going to be
distributed.)
If I can show that a float takes up the same size as a 16-bit int, then I
won't have to bother changing it, which would be a right P.I.T.A as it will
probably have absolutely zero impact on anything if I do - but I suspect it
takes up 32 bits.... (sigh)... but still. I have to look into it.

Lookup FieldSize Property in MSAccess Help.
Byte 1 byte
Decimal 12 bytes
Integer 2 bytes
Long Integer 4 bytes
Single 4bytes
Double 8 bytes

Still I would test it with your records - your problem domain - one can
occasionally be surprised.

-ralph