0

I have a Microsoft Excel spreadsheet that uses VBA to connect to a Microsoft Access database. The problem is that when multiple users are working with their local copy of the spreadsheet, often they are getting "Could not update; currently locked" error when trying to query the database.

I have tried setting the database to use optimistic locking when Excel creates a recordset but I'm still getting the error. Not sure what I'm doing wrong.

This is the code that creates the connection in each user's Excel file. accessCon is a global variable.

Dim accessFileLoc As String: accessFileLoc = ThisWorkbook.path & "\DB\GrabDB.accdb"
Set accessCon = CreateObject("ADODB.connection")
accessCon.Mode = 16 + 3 'adModeShareDenyNone + adModeShareReadWrite
accessCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accessFileLoc

And this is the code that uses the connection to create a recordset. accessRS is local variable to the function with the query.

Dim accessQuery as string
accessQuery = "SELECT [ID] FROM user_table"
Dim accessRS As Object
Set accessRS = CreateObject("ADODB.Recordset")

accessRS.CursorLocation = adUseClient 
accessRS.CursorType = adOpenStatic
accessRS.LockType = adLockOptimistic 

accessRS.Open accessQuery, accessCon

...do something with accessRS...

If accessRS.State = 1 Then accessRS.close
Set accessRS = Nothing

What am I doing wrong? Thanks G

Geoff L
  • 765
  • 5
  • 22
  • https://stackoverflow.com/questions/5646793/maximum-concurrent-connections-in-ms-access – ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ Mar 20 '23 at 15:41
  • Hi. Thanks for your comment, but I'm not sure how that is helpful? That is a discussion of user limits in Access, which I see are more than adequate of my needs of 5-10 users. What am I missing? Thank you. – Geoff L Mar 20 '23 at 16:11
  • Multiple connections may be opened without closing or by an error. It would be correct to make the connection with access in a Function and to call it when you need to connect and in any case to ensure that the connection will be closed even if something goes wrong in between. – ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ Mar 20 '23 at 16:28
  • 1
    If it's a SELECT you could use `adLockReadOnly`. – CDP1802 Mar 20 '23 at 16:51
  • @CDP1802 Thanks but the users need to be able to write data as well, I just put in the select as an example. The users need to SELECT, INSERT, UPDATE, and DELETE – Geoff L Mar 20 '23 at 16:58
  • @ΑΓΡΙΑΠΕΣΤΡΟΦΑ I try to close the recordset at the end of each function, even though the recordset is a local variable I still call accessRS.Close and Set accessRS = Nothing at the end of each function. Should I be doing the same with the connection variable? Currently its a global variable that only gets set once on program startup. – Geoff L Mar 20 '23 at 17:00
  • If your code is stable and since the connection is made once in the program, you could leave the connection alive. But since you have this problem and to draw conclusions maybe you need to open and close the connection as well as the recordset. – ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ Mar 20 '23 at 17:09
  • Does this help https://stackoverflow.com/questions/19139202/excel-data-connection-locks-access-db-prevents-second-connections-refresh? Why use Excel as GUI instead of Access? – June7 Mar 20 '23 at 18:27
  • I've made a test, adding `accessRS.Close: Set accessRS = Nothing` GrabDB.laccdb is removed automatically, and immediately. This might free the lock? – jacouh Mar 20 '23 at 20:13

0 Answers0