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(
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
Set getCurrentDbC = gDAODB
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:
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?