Locking Access Record

[mikeymay]

I am opening an Access table record via VBA code in Excel and I want to 'lock' that record until it is updated by the Excel VBA code.

I have tested using the following

   Set rsQuery = CreateObject("ADODB.Recordset")
   Set rsQuery2 = CreateObject("ADODB.Recordset")
   
   strQuery = "SELECT SO_No FROM AllData WHERE SO_No= '" & strSO & "'"
   strQuery2 = "SELECT SO_No FROM AllData WHERE SO_No= '" & strSO & "'"
         
   rsQuery.Open strQuery, cnConnection, adOpenKeyset, adLockOptimistic  
   rsQuery2.Open strQuery, cnConnection, adOpenKeyset, adLockOptimistic

And both record objects execute when opened with no erro message.

Whilst testing I did do something where I got a message saying the record can't be opened as it is in use or something similar but I don't remember how I got this.


TIA

 

[Micron]

Not sure what the question is. Perhaps you need adLockPessimistic?

Types of Locks - ActiveX Data Objects (ADO)

Types of Locks

docs.microsoft.com

 

[mikeymay]

Pessimistic works!

I'm sure I'd tried this and it didn't work leading me to post on here but happy days (ish) now.

Now I need to be able to lock a table when it is being updated with new data.

The table can be viewed via excel to get records to populate repots, but also records updated with specific field data.

Also there is a process to add new records from another application, basically dumps lots of new records into the table and amends any existing records with new data from the upload. Whilst this is happening I need to lock the table incase an existing record needs to be updating and it will stop anyone from performing a change to exiting record.

The upload process only takes a few seconds so a user wanting to make a change to an existing record will only see a slight delay, dealt with by a loop trying to open the table for read/write.


TIA

 

[Micron]

AFAIK, locking option settings are client based, which means the chosen setting applies to all db's opened by Access. Also, I believe that the setting is ignored for action queries or code that executes action sql statements. You might have to develop a work around if what you're doing raises any issues. Some developers use the no lock setting to avoid such problems. It means that a user might be looking at data that is not current and you'd have to weigh that probability against the need to do anything about it. However, it's important to note that those locking options only come into play when a record is being edited, so if the user is only looking there should be no conflict. If a record was being edited during the updates the user who releases the record last might/should get the prompt that you might have seen before about accepting or rejecting the changes that were made. The message might be a bit confusing but the user should be discarding their changes in that case and then reviewing the new data. Of course, none of this matters for the records that are being appended.

Perhaps the best solution is to perform these uploads after hours when no one is around if you can work all day with a single update at the beginning. For that you'd need a pc that is left running and use Task Scheduler to open a db and run the updates. This could be a different db used only for updating or you can use a command line switch in a shortcut that TS opens. Your db code can look for the command setting and know that this is a regular user, or it is the updating pc so go ahead - do the updates then close. What you don't want to do there is have to deal with a message box prompt because no one will be around if you open the db via TS.

Not sure if your last post means you consider this thread solved or not. If so, please mark it as solved to save people the trouble of reading through it while looking for issues that need solving.
Thanks.

 

[mikeymay]

Thanks Micron

The data imports won't be able to happen outside of normal work hours more for the reason that the client doesn't want to allocate any additional budget to the project.

I think the best was is to create a text file in the folder holding the db and if someone else is wanting to retrieve a record for editing, it checks for the text file and loops until it is deleted at the end of the data import process.

 

[Micron]

That won't prevent anyone from locking a record before your update begins?

If you find that network speed is an issue, you could set a flag in a local table field - then you wouldn't have to be repeatedly hitting the server to look for a file.

 

[mikeymay]

When the process to update a record in in play, it will first check if there is a text file saved which basically flags the db as locked. This will loop for 20 seconds which is how long the other update process takes.

So yes, it doesn't 'lock' the db, table or a record but it does stop the other Excel VBA driven process for taking place until the text file is deleted.

A work around, but should do the job.