Results 1 to 2 of 2
  1. #1
    kfizz is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    2

    Multi-Computer Access Front-end to MySQL: How to prevent duplicate records?

    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.

  2. #2
    Stingaway is offline Efficiency Junkie
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Location
    The deep south. Keep going until you hit water basically.
    Posts
    224
    I would suggest either making the record number field a unique autonumber on the MySQL side or creating an autonumber field and generating the record number off of it (i.e. Record Number = X and the Unique Number, or some sort of formula like that).

    Once the MySql database assigns a unique id to the record, it should alleviate the issue regardless of who is accessing what on the front end.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dcount prevent duplicate
    By ayamali in forum Programming
    Replies: 20
    Last Post: 04-16-2013, 09:31 PM
  2. Prevent Duplicate Entry
    By kilosierra71 in forum Forms
    Replies: 4
    Last Post: 07-28-2011, 03:08 PM
  3. Replies: 12
    Last Post: 06-16-2011, 01:35 PM
  4. Replies: 2
    Last Post: 02-12-2011, 09:54 PM
  5. Multiply front end on same computer?
    By efk0107 in forum Access
    Replies: 5
    Last Post: 01-25-2011, 08:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums