none
How do I avoid table access collisions? RRS feed

  • Question

  • I don't know if this feature is built in or there is a vba command or option that will protect the integrity of a table.

    I have an environment that requires multiple access databases (one for each laptop).  They are not connected to a network most of the time.  During this time, data is collected to an ms access database locally.  At the end of the day the laptops get access to the network and via VBA updates a table in a 'master' database file that's in the cloud. My question is how do I lock down the 'master' database so no two (or more) laptops conflict when updating the 'master' database file?

    Thank, -Pat

    Friday, April 13, 2018 11:58 PM

Answers

  • You are correct in understanding my environment.  Besides adding the On Error code, I found the MS Access options to do the job. I needed to open the database in Exclusive mode. The "True" in the set dbMaster command below does the trick.

    Dim dbMaster as DAO.Database
    set dbMaster = OpenDatabase("filename",True)

    Just to be safe, I also open the recordset in exclusive mode.  The dbDenyWrite + dbDenyRead takes care of it for the table.

    Dim rstMaster as DAO.Recordset
    set rstMaster = dbMaster.OpenRecordset("tablename",dbOpenDynaset, dbDenyWrite + dbDenyRead)

    Thanks for all you inputs.
    - Pat

    • Marked as answer by PSD1953 Sunday, April 15, 2018 5:59 PM
    Sunday, April 15, 2018 5:58 PM

All replies

  • Hi Pat,

    Is the master database an Access database file? If so, when you said "cloud," what exactly did you mean?

    Saturday, April 14, 2018 12:27 AM
  • Yes, it's an access database also.  It's really on a drive that is shared to all the laptops when they are on the network.

    I should have added this info.  I use DAO to setup two recordsets.  One to the local table, the other to the table in the master database.  I append new records and update changes to the master database table.  It's take a second or two, but I'm worried when two systems try to update the master database table at the same time.  Odds are low, but not zero.

    Thanks, -Pat

    • Edited by PSD1953 Saturday, April 14, 2018 1:58 AM
    Saturday, April 14, 2018 1:26 AM
  • The first thing I would try is to insert an error handler in your update VBA routine on two of the machines sort of like:

    On Error Goto UpdateError

    'Your Append and Update VBA routines

    UpdateError:

    DoCmd.CancelEvent

    MsgBox "The table could not be updated because someone else is currently updating it. Please try again later."

    Exit Function (or Exit Sub)

    Then have the two users deliberately update the table at the same time and see what happens.

    Saturday, April 14, 2018 4:59 PM
  • Thanks for this suggestion.  I will add error handling accordingly.  Testing will be a challenge.

    Stay turned....

    Sunday, April 15, 2018 1:19 AM
  • My first question would be: why do you think you need to copy the records from master to local? Access is a multi-user DBMS and in most cases the linked table is all you need.

    -Tom. Microsoft Access MVP

    Sunday, April 15, 2018 3:50 AM
  • Tom and PSD1953:

    I am assuming users are unable to access the network drive all of the time. They may be updating or adding new information in the field without access to  the "master" database. So they apparently use Append and Update queries to update and add new records at the end of the day. Of course at some point after the "master" database is renewed, they must then copy or somehow get the data back on their individual computers for the next day. But I am just making assumptions based on his oroginal post. I have worked in an environment like this one as well. It's not optimal for sure.

    Sunday, April 15, 2018 2:45 PM
  • You are correct in understanding my environment.  Besides adding the On Error code, I found the MS Access options to do the job. I needed to open the database in Exclusive mode. The "True" in the set dbMaster command below does the trick.

    Dim dbMaster as DAO.Database
    set dbMaster = OpenDatabase("filename",True)

    Just to be safe, I also open the recordset in exclusive mode.  The dbDenyWrite + dbDenyRead takes care of it for the table.

    Dim rstMaster as DAO.Recordset
    set rstMaster = dbMaster.OpenRecordset("tablename",dbOpenDynaset, dbDenyWrite + dbDenyRead)

    Thanks for all you inputs.
    - Pat

    • Marked as answer by PSD1953 Sunday, April 15, 2018 5:59 PM
    Sunday, April 15, 2018 5:58 PM
  • I'd suggest that, rather than using DAO, you execute an INSERT INTO and UPDATE statement in a single Transaction which is rolled back in the event of an error in either SQL statement.  If you are not familiar with the use of Transactions, you'll find a simple example in TransactionDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file uses a simple model of a Families and FamilyMembers tables into which rows are inserted by means of a single transaction, rolling back the transaction in the event of an error in the execution of either SQL statement.

    Ken Sheridan, Stafford, England

    Sunday, April 15, 2018 6:06 PM