Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    18

    Record Locking Multi User Environment

    I've googled and i have not come across the steps on actually locking a record. I have a DB that I would autogenerate serial numbers. The sites i've come across explains that I will need to have a one field table with the number i will be using, I got that. But it then explains "When a new sequence number is needed, retrieve the number. Lock the table so no other user can pull the same number until the current number is used. This will prevent duplicate numbers and breaking the sequence."



    Help

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,658
    You found this instruction on a site referencing Access? Provide link to that source. I have never seen anything about programmatically 'locking' a table. Review http://msdn.microsoft.com/en-us/libr...ffice.10).aspx
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Aug 2011
    Posts
    18

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,658
    Considering that that article must have been written in 2003 (indicated by reference to approaching year 2004), it probably has Access 2003 capabilities in mind. Access 2003 did have security features that were dropped with Access 2007. The 'table locking' that Paul instructs you to use was probably part of abandoned functionality.

    This is how I handle generation of unique ID: http://forums.aspfree.com/microsoft-...ta-403208.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Aug 2011
    Posts
    18
    Appreciate it. Now it makes more sense. Thank you

  6. #6
    Join Date
    Aug 2011
    Posts
    18
    Here is my non-vba approach:

    1. Created a table with only one record, it holds the serial number.
    2. Created a table that holds new record with sequential numbers.
    3. Created an update query that increments the serial number by one.
    4. Created an append query for data entry
    5. Created a form for data entry, it has an embedded macro On Open, that opens the update query and adds 1 to the serial number. The field that requires the SN, i use DMAX("SN","tblSerial"). Record Locks set to All Records.
    6. On the form, a button has an embedded macro On Click that runs the append query, adding the record to the table that holds records.

    Tested it with two users creating a document number that requires unique serial numbers.

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

Similar Threads

  1. Replies: 0
    Last Post: 03-14-2012, 07:23 AM
  2. Replies: 3
    Last Post: 09-22-2011, 03:35 PM
  3. Record Locking
    By jlclark4 in forum Database Design
    Replies: 10
    Last Post: 06-15-2011, 02:22 PM
  4. Replies: 6
    Last Post: 05-04-2011, 06:17 PM
  5. Replies: 13
    Last Post: 05-18-2010, 01:12 PM

Posting Permissions

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