Universally unique autonumbers?

[J W Crosby]

I need to be able to generate an autoID that would be
unique to a particular database, but also unique to about
5 other similar databases. If I use the normal
autonumber, I could potentially get a, say #23, in each of
the databases. I don't want that.

Is there a way to "seed" the autonumber, or to add an "01-
" in front of it, or ??

I fear I haven't been clear enough, so thanks for plodding
through my feeble attempt at explaining.

Thanks.

Jerry

 

[Lynn Trapp]

Jerry,
Can you explain why having duplicate ID's in different databases would be a
problem? You certainly can concatenate a number (say, for the number of the
database) in front of your ID's.

--
Lynn Trapp
MS Access MVP
(e-mail address removed)
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

 

[TC]

You can create an automatically-generated GUID field, but that is really not
what you want. There are many problems in using those fields subsequently.

A better bet would be, to add a new field SiteID to each table. Set the
default value of that field to a different code, in each site. (Or populate
the field at runtime using a Site ID value from the registry, for example.)
Then, multiple records with the same autonumber value (say 23) would be
distinguished by the fact that they had different SiteID's.

You would >not< have to add SiteID to the primay keys of the tables in the
individual sites; ie. nothing changes except to add that field & populate
it. But if you wanted to download the data from several sites into a single
table, that table would have to be keyed by SiteID + (existing primary key
from the source table); for example, SiteID + PersonID, not just PersonID.

HTH,
TC

 

[david epsom dot com dot au]

Also, yes, you can seed an Auto-number, and we used to
do that, but Autonumber has had so many problems in the
last 4 years that no-one would want you to depend on
that. Even if you seed the Auto-number fields, you
still need a site-specific ID field in case your auto-numbers
get corrupted :~).

(david)

 

[Guest]

I think I may have enough information now to solve the
problem, but I will answer your question, Lynn. I'll try
it by examples.

I have 4 identical databases being used by 4 people
inputing data relative to their own geographic area. Each
database has 2 tables (for sake of argument).

Table 1, basic data:
StudentID: autonumber (PK)
Name
Address
Instrument
Etc.

Table 2, student history:
StudentID: linked to StudentID in Table 1, one-to-
many
YearOfCompetition
Score
Etc.

So, as you can see, all four areas would have (at least
theoretically) a record in Table 1 with a StudentID of 23,
along with several (possibly) records in Table 2, each
with a StudentID of 23.

The problem comes when I want to combine the data from all
4 areas so I can run a universal report. To do that, I
figured I'd need to combine (i.e. append) all the Table 1s
and do the same with all the Table 2s. But, that would
create duplicate StudentIDs in Table 1, which wouldn't be
allowed. If each of the 4 areas had universally unique
StudentID numbers, I could easily and quickly append all
the tables and run the reports. There would be no
duplication.

Probably more info than you wanted, but my best attempt at
answering your question.

Thanks.

Jerry