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?
