Solved

Locks on MS Access DB not being released

Posted on 2016-10-17
9
46 Views
Last Modified: 2016-10-20
I’ve been developing in Access for over a decade, actually well over.  Since ADO was introduced I’ve always used ADO for file handling.
Sometime in the last year I switched over the DAO on any new applications and for any new file handling in existing applications.
Initially my DAO opens looked like this:

Dim rsClient As DAO.Recordset
Set rsClient = CurrentDb().OpenRecordset("tblClients", dbOpenDynaset)

I recently notice an example on EE that was explained as being more efficient.  So now this is the way I'm doing DAO.

I set up a public variable in a common code only module (not linked to a form)
'
Public gDAODB As DAO.Database  ' used to only DAO connect 1 time

And a public routine in the same module

Public Property Get getCurrentDbC() As DAO.Database
    If (gDAODB Is Nothing) Then
        'Set m_db = CurrentDb
        Set gDAODB = CurrentDb
    End If
    Set getCurrentDbC = gDAODB
End Property

Each module that uses DAO also has a this definition at the top, prior to any code routines

Dim db As DAO.Database

Within the module my DAO opens look like this.

Set db = getCurrentDbC
Dim rsOut As DAO.Recordset
Set rsOut = db.OpenRecordset("tblCOPDW_Local", dbOpenDynaset, dbSeeChanges)

There is almost always more than one DAO open in any given section of code.

The new way works well but I’ve been having an issue with locks being left on the DB when a job cancels.  Maybe the issue is only when users are ‘admin’ users.

This application is used concurrently by up to 50 users.  This morning one of the users tried to run a job but got an error message:

Lock Error DAO
This never occurred when I was strictly using ADO or my original DAO method but it has occurred several times using the new DAO methodology.

Any ideas on what would be causing this with the new DAO coding scheme and more importantly how can I fix it?  Would going back to my original DOA coding eliminate this issue?
0
Comment
Question by:mlcktmguy
  • 4
  • 4
9 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41847667
did you close your database after use it?

like adding:
db.Close
Set db = Nothing

Open in new window

0
 
LVL 57
ID: 41848043
<<The new way works well but I’ve been having an issue with locks being left on the DB when a job cancels.  Maybe the issue is only when users are ‘admin’ users.>>

  Either one of two things:

1. Someone made a change that required exclusive mode (i.e. changed code).

2. Someone exited the database abnormally, leaving the database flagged as corrupt.  If this occurred, a C&R would be required to get it working again.

Jim.
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 41848413
Thanks for the response:
<<1. Someone made a change that required exclusive mode (i.e. changed code).>>

Would the difference between my original DAO open code and my revised DAO open code, which are both shown in my original post, cause this issue?

I am the only one working on this application so I'm sure that is the only thing that changed in the code.

<<2. Someone exited the database abnormally, leaving the database flagged as corrupt.  If this occurred, a C&R would be required to get it working again.>>

You are corr3ect, this situation only occurs when someone has left the DB abnormally.  However, the database is not flagged as corrupt.  No C&R is necessary to resume operation.  What I don't understand is why this message never occurred using ADO or with my original DAO open technique.
0
 
LVL 57
ID: 41848510
<<Maybe the issue is only when users are ‘admin’ users.>>

 What do "admin" users do that is different than a normal user?

Jim.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:mlcktmguy
ID: 41849388
Nothing different.  I'll even get the message on my single user development machine when I'm exiting after something cancels.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 41849925
<<Would the difference between my original DAO open code and my revised DAO open code, which are both shown in my original post, cause this issue?>>

The only difference between this:

Dim rsClient As DAO.Recordset
Set rsClient = CurrentDb().OpenRecordset("tblClients", dbOpenDynaset)

and this:

Public Property Get getCurrentDbC() As DAO.Database

 Is that in the first, your always getting a new object (a CurrentDB() call always creates a new object, which is up to date) to the current DB.  The second, since it is a cached object, will not be up to date and will not reflect updates to the DB collections.  You can read more about that here:

https://www.experts-exchange.com/articles/2072/CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html

 So as long as your not adding tables, temp query def's etc, there is no difference between the two methods and the answer to your question is no.


On this:

<<1. Someone made a change that required exclusive mode (i.e. changed code).>>

 it's not just code, but that is part of it.   Access was changed a while back that any code save required exclusive access to the DB.   That is not only code you change, but any object being changed that has code attached.

 Do you open any objects in design view at runtime?  This will always require a save.

 Are you modifying objects at runtime?  If so and not doing acSaveNo, then you may be causing a save, which requires exclusive access if code is attached.

 In short, I think your looking in the wrong direction.

Jim.
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 41852244
Jim Dettman: Thank you very much for the clarification and great article you wrote.

Based on what you posted I just now realized I am never 'closing' the DB properly.  I open and close each recordset but after setting the property using:

Public Property Get getCurrentDbC() As DAO.Database
     If (gDAODB Is Nothing) Then
         'Set m_db = CurrentDb
         Set gDAODB = CurrentDb
     End If
     Set getCurrentDbC = gDAODB
 End Property

I don't have a any code to set it back to nothing upon exit from the app. if it was set.  It is remotely possible that the application can start and exit without setting gDAODB.

Also, I want to clarify what exactly is not ‘refreshed’ using my new method.  Would records added concurrently by other users and/or the current user show in the recordset pulled?  Or, is this open similar to a snapshot version of the DB?

In my current coding scenario what would I use to set gDAODB t nothing, if it had been set.  It is as simple as
Public sub cleargDAODB()
If (gDAODB Is Nothing) Then
else
              set gDAODB = nothing
      endif
end sub


I am strongly considering adopting you methodology:

Using your suggested routines my original method would become

Dim rsClient As DAO.Recordset
Set rsClient = CurDb ().OpenRecordset("tblClients", dbOpenDynaset)

And If I specicially wanted a refreshed data set I would use

Dim rsClient As DAO.Recordset
Set rsClient = CurDb (True).OpenRecordset("tblClients", dbOpenDynaset)

When my code application canceled or was exited cleanly I would want to run

Public Sub CurDBClear()
If objCurDB= nothing then
else
    Set objCurDB = Nothing
endif
End Sub

Is that correct?
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 41852920
<<I don't have a any code to set it back to nothing upon exit from the app.>>

 This is not a show stopper, but in the past, this might cause Access to minimize at close.  The main reason to close objects and set variables to nothing is to make sure they get cleaned up.

 Access's/VBA's garbage collection is not as good as it should be, so if you don't do this, your leaving it to chance (always better to be explicit in programming rather than letting things implicitly happen).

 In this case, it's only one object variable.   But if you had something in a routine that got called over and over and there was a bug, you could get a memory leak.


<<Also, I want to clarify what exactly is not ‘refreshed’ using my new method.>>

 The DB collections or objects within the DB.  Something like a temp querydef or the tabledefs.

 You'll notice that each of the collections has a refresh method.  i.e.

 CurrentDB().TableDefs.Refresh

 If your dealing with a cached object variable, then it's not updated.   But if you did:

 CurrentDB().TableDefs.Count

 each time, there would never be a problem because the CurrentDB() call is *always* handing you a new database object.


<<I am strongly considering adopting you methodology:>>

 Your already there for the most part.    You don't need the additional code at the top of each module to set an object variable.   Just keep calling getCurrentDbC().

 The other piece you don't have is the Refresh argument on getCurrentDbC() when you do need a object variable with everything refreshed.   But in overall concept, your already there.

 That's why I believe your problem lies else where.  There's nothing wrong per say with your setup as far as locking is concerned.

Jim.
0
 
LVL 1

Author Closing Comment

by:mlcktmguy
ID: 41853099
Thank You very much
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This collection of functions covers all the normal rounding methods of just about any numeric value.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now