Results 1 to 10 of 10
  1. #1
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107

    locking and unlocking a table using VBA code


    I have a SQL table that contains one record for control purposes. In this one record, there is a Next Order Number field. I have a subroutine that opens this table, grabs the Next Order Number, then writes records to several different tables using this Next Order Number. I would like to either lock the control table (preferably) or the one record the whole time this subroutine is running to prevent a second user from being able to grab the Next Order Number until the first user is done with it to insure I don't get duplication errors. This would be a much simpler solution that trying to write error trap routines. Any suggestions?

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Could you just increment the number in SQL table and save it, then continue to run the process for current user.

    NextOrderNumber = 1
    user1 starts process, add 1 to NextOrderNumber so equal 2. run user1 process where NextOrderNumber = 2
    NextOrderNumber = 2 now
    user2 starts process, add 1 to NextOrderNumber so equal 3. run user2 process where NextOrderNumber = 3

  3. #3
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    We can, and we do. The problem is we have a custom software package that is also using this file for the same purpose, and under specific circumstances we lose a record every once in a while. We can put error traps in the database logic, but locking the file would be SO much easier.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Here is an idea.

    To this one record table, add another field and call it "Status".
    When your subroutine first runs, change this field value to "In Use".
    Then at the very end of your subroutine, have it clear that "Status" field.

    Now, amend the very beginning of your subroutine when a person first initiates this process, and if the "Status" field is set to "In Use", return a message telling them that they cannot do it right now.

  5. #5
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    JoeM, unfortunately that is not an option. These tables were designed for our custom software package and changes to their structure can have dire consequences. I was hoping that locking and unlocking a file was something that was fairly straight-forward in VBA, but apparently it is not. I believe I will just have to restructure my logic and put error trapping in to catch attempts to write records with key field duplication. This is do-able...just not the solution I was hoping for.

    But I'm wondering if locking the record might be an option. I know that Access locks a record while it is "dirty". Is there a way to mark the record as dirty in code until I have finished my subroutine?

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    These tables were designed for our custom software package and changes to their structure can have dire consequences.
    Could you simply add a new, one record table for this purpose?
    Then it wouldn't mess with any of your current existing tables.

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Try this:

    Open the table into a recordset. One of the options in the OpenRecordSet method is dbDenyRead which "Prevents other users from reading Recordset records (table-type only)." (from MS Access 2010 VBA Help)

    If that is a SQL server table, I don't know if that will work; I don't use SQL server.

    As long as that recordset is open, the one record in it should be locked for update by any other process.

    One question: How does your custom software package react if it encounters a locked table? Is there a trap in it to handle what it would possibly think is an error?

  8. #8
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    No. I need to block the users in the custom software, and that wouldn't work. However, the custom software does force a user to wait if they are trying to access a record that is locked by another user. That is why I wonder if there is a way to mark the control file record as dirty until the Access database has finished writing the necessary records in the other tables.

  9. #9
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    I tried an experiment in which I opened the control table in Access and changed the amount in the Next Order Number field without saving or moving to another field, thus leaving the record in "dirty" mode. I then went into our custom software and tried pulling up the Next Order Number in the Order Entry screen. It pulled it in and updated the Next Order Number in the control table, so I was not able to block the custom software from opening, editing, and updating the control table like I thought I could. It looks like my only solution is to edit my database coding to trap duplication errors and make the necessary corrections in order to avoid losing records created in the database. I apologize for leading everyone on this wild goose chase. I appreciate everyone's input.

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Are you using Access as the database or are you using SQL server as the database?

    If you are using Access as the backend, you can use Autonumber in the table properties or you can use some VBA and Dmax + 1
    https://msdn.microsoft.com/en-us/lib.../ff835050.aspx

    If you are using SQL server I would have to look at my notes for the correct term. It's been a while.

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

Similar Threads

  1. Unlocking standard form layout
    By New_2_Access in forum Forms
    Replies: 4
    Last Post: 06-22-2012, 11:54 AM
  2. Locking/Unlocking a Control
    By Kirsti in forum Programming
    Replies: 9
    Last Post: 05-27-2012, 03:55 PM
  3. Locking Entries in a Table
    By DDEB in forum Access
    Replies: 1
    Last Post: 05-23-2012, 02:14 PM
  4. Unlocking an Access database?
    By kp123 in forum Access
    Replies: 6
    Last Post: 02-07-2012, 08:48 AM
  5. Replies: 3
    Last Post: 07-15-2011, 08:31 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