Microsoft Office Tutorials and References
In Depth Information
Access Data Types
AutoNumber value. As a result, if you insert a new record and you see it’s assigned
an AutoNumber value of 401, then you can’t safely assume that there are already 400
records in the table. The actual number is probably less.
An AutoNumber value doesn’t represent anything, and you probably won’t spend
much time looking at it. The AutoNumber field’s sole purpose is to make sure you
have a unique way to point to each record in your table. Usually, your AutoNumber
field is also the primary key for your table, as explained on page 741.
Using AutoNumbers without revealing the size of your table
AutoNumber values have one minor problem: They give a clue about the number of
records in a table. You may not want a customer to know that your brand-new food
and crafts company, Better Butter Sculptures, hasn’t cracked 12 customers. So you’ll
be a little embarrassed to tell him he’s customer ID number 6.
The best way to solve this problem is to start counting at a higher number. You can
fool Access into generating AutoNumber values starting at a specific minimum. For
example, instead of creating customer IDs 1, 2, and 3, you could create the ID
values 11001, 11002, 11003. This approach also has the advantage of keeping your IDs
a consistent number of digits, and it lets you distinguish between IDs in different
tables by starting them at different minimums. Unfortunately, to pull this trick off,
you need to fake Access out with a specially designed query, which you can learn
about in the online Appendix C.
Alternatively, you can tell Access to generate AutoNumber values in a different way.
You have two choices:
• Random AutoNumber value. To use random numbers, change the New Values
field property from Increment to Random. Now you’ll get long numbers for
each record, like 212125691, 1671255778, and -1388883525. You might use
random AutoNumber to create values that other people can’t guess. (For example, if
you have an Orders table that uses random values for the OrderID field, you can
use those values as confirmation numbers.) However, random AutoNumbers
are rarely used in the Access world.
• Replication IDs. Replication IDs are long, obscure codes like 38A94E7B-2F95-
4E7D-8AF1-DB5B35F9700C that are statistically guaranteed to be unique. To
use them, change the Field Size property from Long Integer to Replication ID.
Replication IDs are really used only in one scenario—if you have separate copies
of a database and need to merge the data together in the future. The next section
explains that scenario.
Both of these options trade the easy-to-understand simplicity of the ordinary
AutoNumber for something a little more awkward, so evaluate them carefully before
using these approaches in your tables.