Results 1 to 2 of 2
2005-02-16, 16:50 #1
- Join Date
- Nov 2001
- Thanked 0 Times in 0 Posts
User-friendly IDs for a replicated database (Access 03)
My database has one person doing data input and a number of people who need to read it. Readers used to share a copy of the backend in a shared folder on a local file server, and the data inputter periodically updated it. We've now had to move the backend to a remote file server accessed over a WAN (I know - it wasn't my idea!) and performance is unacceptably slow. So I'm considering replication - giving all users their own replica of the backend and synchronising them via the WAN (they probably only need to synchronise once a day or so).
Making the database replicable results in the Autonumber fields used for ID numbers being converted from Incrementing to random long integers. I can see that in general this is necessary so that multiple users adding records don't create them with the same number (although in my case only one user is actually allowed to add records). But I still need a "user-friendly" ID number for users to refer to items (e.g. "machine 6 is required for test 1033", rather than "machine 23,475,334 is required for test -34,654,644"). My best idea so far is to have a second field in each table for the "user-friendly ID", and to have a BeforeInsert procedure in each data input form that calls the DMax function to find the highest user-friendly IDs in the table so far, increments the result and writes it in to the new record. But I believe that functions like DMax are fairly slow on big tables. Can anyone think of a better solution?
2005-02-16, 17:11 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: User-friendly IDs for a replicated database (Access 03)
You could use an auxiliary table with only one record, and a number field (long integer) for each user-friendly ID. These fields hold the most recently assigned ID values. When you are about to add a new record. retrieve the appopriate value (very fast, because there is only one record) from the auxiliary table, increment it by 1, and store the new value both in the auxiliary table and in the record to be added to the data table.