The short version: I have an Access database that acts a a front-end to a MySQL server. There are two (mostly identical) ADB's that are in two separate offices. Is there a way to "lock" a record once it has been opened in office A such that office B would have to edit a different record?
The long version: I'm using two similar Access DB files as a front-end to a central MySQL server. The two clients will be accessing the MySQL server in the same time periods throughout the day. I have a form which grabs the next available record number from a table in the database (e.g. if the last record was X348, it would increase that by one and use X349) and use that as the key for the next record. However, I've noticed that if I use both client programs at the same time, it's possible they'll grab the same key. (I should note that this key is not a primary key in itself, it is combined with another field to make a multi-field primary key.)
Is there some way to force the second client to use the next, next record in this event? Is there a way to lock -- either on the Access or MySQL side -- the current record being processed from being edited?
I'm open to any suggestions. Thanks.