Field Data Storage

[TastyWheat]

I found this page as a pretty good reference for field data types, but there's some things I still want to know.

• What's the per-character storage on a Memo and Hyperlink field (ignoring compression)?
• Are AutoNumber types (Long, Replication ID) unsigned?
• Do null [non-text] fields still use their full capacity?

 

[boblarson]

I found this page as a pretty good reference for field data types, but there's some things I still want to know.

• What's the per-character storage on a Memo and Hyperlink field (ignoring compression)?
• Are AutoNumber types (Long, Replication ID) unsigned?
• Do null [non-text] fields still use their full capacity?

What's the per-character storage on a Memo and Hyperlink field (ignoring compression)? - can't answer that one for you.

Are AutoNumber types (Long, Replication ID) unsigned? No, they can be positive OR negative.

Do null [non-text] fields still use their full capacity? - I believe not. Fields in Access do not pad themselves like SQL Server. A Null in Access is an absence of data. Once you put something in and then remove it, it isn't truly null anymore and I believe that may be the case even if you set it = Null, but a true null is not storing anything.

Jeff Conrad, or someone else might tell me I'm wrong about any, or all, of that but from what I've gleaned in various sources, that's the info I have.

 

[TastyWheat]

Thanks for the input.

My third question is actually the most important. When modifying a well-established database I'm very hesitant to add new fields to tables. If all of the previous records are going to pad themselves I think I'd rather take a performance hit by adding a new table.

The other two questions are more or less for my own curiosity. I rarely use Memo or Hyperlink fields and I can't imagine overflowing a Long.

 

[Ian Mac]

Third answer first, as it's the most important.

The help files from 2003 say (It's worth looking at those first, honest)

Microsoft Access provides two field data types to store data with text or combinations of text and numbers: Text and Memo.

Use a Text data type to store data such as names, addresses, and any numbers that do not require calculations, such as phone numbers, part numbers, or postal codes. A Text field can store up to 255 characters, but the default field size is 50 characters. The FieldSize property controls the maximum number of characters that can be entered in a Text field.

Use the Memo data type if you need to store more than 255 characters. A Memo field can store up to 65,536 characters. If you want to store formatted text or long documents, you should create an OLE Object field instead of a Memo field.

Both Text and Memo data types store only the characters entered in a field; space characters for unused positions in the field aren't stored.

You can sort or group on a Text field or a Memo field, but Access only uses the first 255 characters when you sort or group on a Memo field.

Second question has been answered by Bob, but, also from help files:

About AutoNumber field size and replicated databases (MDB)
Note The information in this topic applies only to a Microsoft Access database (.mdb).

If you plan to replicate your database, you need to consider what field size is appropriate for the AutoNumber field used as a table's primary key. If you use an AutoNumber field as the primary key for a table in the replicated database, set its FieldSize property to either Long Integer or Replication ID.

If fewer than 100 records are routinely added between synchronizing replicas, use a Long Integer setting for the FieldSize property to take up less disk space.
If more than 100 records are routinely added between synchronizing replicas, you should use Replication ID for the FieldSize property setting to prevent records from being assigned the same primary key value in each replica. Note, however, that an AutoNumber field with a Replication ID field size produces a 128-bit value that will require more disk space.

First: Admittedly I don't know, nor can I find it. The old Access 97 helpfiles had all the memory size for each data type.

As for your performance hit I couldn't comment. It sounds like a beast of a database if your worried about data sizes this much.

 

[rtdc]

>You can sort or group on a Text field or a Memo field, but Access only uses >the first 255 characters when you sort or group on a Memo field.

This answered a problem I had, I could'nt work out why I was only getting 255 characters off a memo field.

Even though the answer was'nt for me thanks for posting it Ian.

RTDC

 

[dfenton]

Quoting the help file:

If fewer than 100 records are routinely added between synchronizing replicas, use a Long Integer setting for the FieldSize property to take up less disk space.
If more than 100 records are routinely added between synchronizing replicas, you should use Replication ID

That quotation from the Help file shows how unreliable MS's own documentation is. I have never once used a ReplicationID in place of an Autonumber in any of the dozens of replicated apps I've created in the 10 years or so I've been doing Jet replication. And some of those apps have been in operation since 1998, and there hasn't been a single collision between replicas using random autonumbers.

Microsoft basically does not understand Jet Replication, and because of that, you need to take everything MS's documentation says with a grain of salt. Consider what you'd run up against if you took MS's advice:

http://trigeminal.com/usenet/usenet011.asp?1033