The Best Solution?, Creating Unique ID

Hi everyone, I have a database and I need to share it with 4 regional offices. They won't go for a VPN so they asked that the 4 databases be merged into the master copy every three months. I know about replication but after reading up on it I think it would be to much trouble to implement/manage.

What I would like to do is simply copy and paste the records from each regional database into the master copy (there will never be duplicates). There are about 20 tables in the database. This of course leads to the problem of uniquely identifying each record in each database. I can't use an autonumber as you can't paste those records without losing the autonumber value, so I need to make a unique key made up of; A:Regional Office ID, eg Cork,Dublin Belfast and a number...so in effect I would have
CO1
CO2...for Cork records
BE1
BE2...for Belfast records, and so on.

I have a working example that I could modify (attached) but is this the best way of doing this in my instance?

I know you're not supposed to build intelligence into the database and have read some posts about keeping the unique ID values in seperate columns and making composite keys.

Could some of the more experienced developers look at my sample and tell me if I should implement it?. I know I will have to implement on every form in the database.

Thanks for any advice

Meltdown,

Just a couple of comments.

I would definitely split the ID into its component parts:

Location
Contact

You can easily fix them up for queries:

TheID: [Location] & Format([Contact], "0000#")

Much easier than taking them apart all of the time. Will make life
a lot easier, when you consolidate your tables.

As for assigning them, I wouldn't use the BeforeUpdate event, I'd
use the BeforeInsert event:

Me.Location = Nz(DMax("[Contact]", "tblSample"), 0) + 1

No criteria (Same family within each database) and I really don't
see the need for records and another table.

Just some thoughts,
Wayne

I would use a composite primary key too. One field identifying the location, and one "autonumber".

I'm not sure I agree 100% with WayneRyan. Using DMax in the before insert event of the form, means you "collect" the number when you start entering a new record. In a multiuser setup, you'll very easily end up with two users getting the same "Max Number", dupe PK... whilst when using the before update, it is retrieved whilst saving, giving much less possibility of two users getting the same number.

I didn't look to closely on your code, but it seems the essential parts where there, using a separate table for the last number, putting a lock on it while retrieving the next... it's among the better approaches in multiuser setups, I think, and could very well be used for the numbering part.

I just got at bit intrigued by the quote

I know you're not supposed to build intelligence into the database and have read some posts about keeping the unique ID values in seperate columns and making composite keys.

Click to expand...

There are two different "religions" on how to choose primary keys. One is referred to as Surrogate Keys, the other Natural Keys. The most fanatic surrogate key fans would say that if you don't use Autonumber as Primary Key, then it's wrong, composite primary keys are EVIL, and there shouldn't be any "intelligence" (or business meaning) built in A search on the terms with your favourite web search engine will probably produce some interesting reading

MD,

See! Conversation is good!

We seem to agree that splitting your two fields is good. I hope we have
convinced you of that.

Now, whether or not to use the two-table approach ... The chances that
two users execute your "DMax line of code" is approximately the same as
that of two users "reserving" the same NextNumber in the multi-line code.
Well, maybe a little bit less.

Regardless, with the Location field, whether you generate the number,
or use an AutoNumber - it will be unique.

I definitely would NOT use the BeforeUpdate event though ... I don't feel
that a Contact should get a new ID everytime they update a record.

Will sit back and watch for now.

Wayne

Oh that's the reason

If me.newrecord then
' assign the composite
end if

Wayne and Roy, thank a lot for the replies, interesting reading. I'll go with seperate columns for the data and see how it goes.