Question Autonumber logic and reliability

[alex]

Hi,

I'm using several disconnected laptop computers with an Access application. At night, they export their new content on a server, which has the same application.

I was wondering if I can really rely on the Autonumber primary keys...for example, if user "Alex" with ID "47" fills a document on a laptop, I need to be a 100% sure that when the data is copied at night, ID "47" does correspond to "Alex" too on the server.

I've done several tests, and noticed that if I delete a line from a table, the AutoNumber ID won't be reused later (that's good!). But, if I delete the content of the whole table, the AutoNumber ID will restart at 1 next time I enter data (less good!)

So as long as I don't empty the whole table, I should be safe with autonumbers...

Does that make sense?
Thanks!

 

[alex]

By the way,

the Access databases are never connected on an intranet or internet. Their new content is manually exported and imported on a USB drive.

 

[Banana]

IIRC whenever you do a compact & repair, Access reset the seed to the largest value available.

However, you should be able to insert the IDs directly via append query so you don't have to worry about keep ID same between two sources.

 

[gemma-the-husky]

surely the biggest issue is making sure all the document numbers are correct, and don't clash, when new data is brought into the master dbs.

if each user has their own sequence, then its no problem. but if they are trying to share a sequence then its harder.

replication is generally used to try to ensure that primary keys are unique between versions, as generally an autonumber is insufficient.

depemds how you have it swet up

 

[dfenton]

surely the biggest issue is making sure all the document numbers are correct, and don't clash, when new data is brought into the master dbs.

if each user has their own sequence, then its no problem. but if they are trying to share a sequence then its harder.

replication is generally used to try to ensure that primary keys are unique between versions, as generally an autonumber is insufficient.

The OP said that there is no network, so replication is not an option.

The usual way to do this is to *not* use an Autonumber as your PK, or to use a compound key with Autonumber and a second field that indicates the source database. I wouldn't bother with the Autonumber myself, and instead just generate the two-column key in code.

 

[gemma-the-husky]

david

i thought the op said he had several independent databases, that he was then trying to merge into 1

so you need some strategy to ensure that each dbs issues non-clashing keys

you are suggesting that the keys in each dbs includes some indicator as to the source dbs, which is fair enough

but is replication not available in a distributed system such as this?

 

[dfenton]

i thought the op said he had several independent databases, that he was then trying to merge into 1

Yes.

so you need some strategy to ensure that each dbs issues non-clashing keys

you are suggesting that the keys in each dbs includes some indicator as to the source dbs, which is fair enough

but is replication not available in a distributed system such as this?

Replicas have to be synched in place. That means if there's never a network connection, you can't use replication.

In this instance, that means the synch has to be manually programmed and some method used to insure no collision of PKs. Random Autonumbers could be one solution, but not a fool-proof one. The longer the time between your programatic synch of the databases, the more likely you'll get a collision.

Thus, I suggested 2-column PKs, with one column being a meaningless number whose sequence you don't care about, and the other identifying the source database.