CurrentDB and database exclusive lock

[realspido1]

Hi all,
I have a problem which I can't figure out what is it caused by and how to solve it as I never expericed it before even though I've been using same approach many times before.
I have front end database with form that creates back end db, inserts new tables in BE and then creates linked tables in FE linked to BE. It used to work but stopped couple of days ago.
Ok, so I have that form with button calling the code - code is located in class objects rather than modules.
Everything goes just fine until I call a public function located in a module:

Public Function FindDelQryDef(strQueryName As String, blnDelete As Boolean) As Boolean
'Returns True if table or query exist in currentDb
    Dim qryd As DAO.QueryDef
    Dim blnResult As Boolean
    blnResult = False
    For Each qryd In CurrentDb.QueryDefs
        If qryd.Name = strQueryName Then
            blnResult = True
            If blnDelete = True Then CurrentDb.QueryDefs.Delete (strQueryName)
            Exit For
        End If
    Next qryd
    Set qryd = Nothing
    FindDelQryDef = blnResult
End Function

When it executes 'CurrentDb.Quer yDefs.Delete (strQueryName)' the database locks itself and when I try to save changes made during debugging of my code, it says: "Microsoft Office Access can't save design changes or save to a new database object because another user had the file open. To save changes or to save to a new object, you must have exclusive access to the file."

It must be associated with command Application.Cur rentDb, because when I earlier tried to exectue:
strThisDb = CurrentDb.Name , I was getting same error message when tried to save changes afterwards.
Even if I do it through

Set objThisDb = CurrentDb()
strThisDb = objThisDb.Name
objThisDb.Close
set objThisDb = Nothing

it doesn't change anything and I'm still unable to retrieve an exclusive access after code is executed.
Can anyone help please? It's killing me!

[Stewart Ross]

Hi. If you are sure there are no other users active when you are trying to run the DB updates it suggests to me that the database is remaining locked as a result of an interrupted process leaving the .LDB file in an inconsistent state. This happens if a user has to 'kill' the database using Task Manager, say, or if their PC or the network crashes before the lock file is released, leaving it with an apparent lock that does not match any current active process, and a lock-file which you cannot delete because it is still classed as in use by another process.

If the lock file is present when you do not have the database open yourself then either someone else is using the database, or you have had an instance of an interrupted Access occurrence not releasing the database.

Check the lock file (the .LDB file) which is associated with your DB - you can open it read-only using Word, for example, to see who is active, or apparently active, at the time.

-Stewart

PS it is unusual to build a front-end DB that users share that creates a back-end DB as a result of some administrative process. This defeats the purpose of having a front-end DB, as you cannot create or modify certain objects when the DB is in shared mode. Is there a reason why this cannot be done using a separate admin front-end which is not shared by other users?

[realspido1]

Hi Stewart,
the reason for FE creating BE is that the database I'm working on links multiple data sources and compiles them into one - what I call "parent" database. That parent database is an existing database. To start the process the admin performs few tasks: points to the parent database/table (which creates a link in FE database, and creates BE to store historical compiled updates) and then sets up all links to feeding data sources, which can be Excel spreadsheets, other database tables etc. (admin sets up the mapping using designated form to establish relationships between fields in parent table and data sources). This process is done only once by the admin, after which users use created mappings to import the data, compile it, and produce reports. Hope that gives you bit more understanding of the whole process..

But anyway, I've checked the .ldb file after the database gets locked up and it looks like this:
PE11113629 Admin PE11113629 admin

where PE11113629 is a number of my computer
before I run the code that locks it, it looks like this:
PE11113629 Admin

FYI, I'm the only user opening the database - it sits on my portable hard drive. The code doesn't attempt to kill any of the processes - in fact what I tried before, I placed the code:
strDbName = CurrentDb.Name
right at the top of the Sub and it did the same thing: I opened the database, and went straight to executing that line and soon as VBA chewed it up it locked the db.
At some stage I was able to fix the problem by replacing that line with:
Set objDB = CurrentDb
strDbName = objDB.Name
objDB.close
Set objDB = Nothing
and it worked for couple of days, but one day when I executed same code, it locked the db again. What's even more strange is that other lines of code using CurrentDB started doing the same thing - like the one I sent in my previous post. Strangely enough, I am still able to open tables in design mode and modify their content - it seems to affect only forms and VBA modules.
I thought this was happening because the code was executed from "on button click" event within the form, and the database takes over the database to create another instance of MS Access to be able to create object CurrentDb - or something like that.
I found a code that does the trick:

    Dim db As Database
    Dim ws As PrivDBEngine
    Set ws = New PrivDBEngine
    Set db = ws(0).CreateDatabase(strNewPathFile, dbLangGeneral)
    ws(0).Close
    Set ws = Nothing
    Set db = Nothing

but unfortunately it doesn't work with QueryDefs, which I'm using further down in my code (.CreateQueryDe fs, QueryDefs.Delet e).
Now why PrivDBEngine doesn't lock the database and CurrentDb does?
Any help will be much appreciated.

[realspido1]

Anyone help, please? this is killing me! I tried everything and it still doesn't work! I am desperate!!

[Stewart Ross]

Hi. It's hard to advise you on this one as it is not at all obvious what is going on. Is there an LDB file present when you exit from the database? If there is, can you delete it? If you can, it is clearly not locked by any process at the time.

If the LDB is not present, then the lock file is being closed normally when you exit, and the two user-instances of your own access to the system you see in your lock file may well be arising because of something you are doing in code.

I have myself come across this on one previous occasion, when using VBA to connect to tables within the database using an ADO connection object. This created a separate instance of myself in the lock file and prevented me from modifying forms etc until I rewrote the code concerned without the separate connection.

-Stewart

[NeoPa]

I'm not positive, but this seems as if it may be related to a fairly common problem using CurrentDB(). Most people consider this is a reference to an object, when it is really a function that returns an object. Best practice is to call the function once, but to save the returned object for use in your code. Repeated calls to the function are known to cause issues. I strongly recommend looking through the Help topic on this as it makes some of the issues clearer.

[realspido1]

NeoPa: it's got something to do with CurentDB() - that's fact. The database is locked when it's called first time, so it's not that I'm trying to open same object multiple times. I also tried:
Set objDB = CurrentDb
but it does the same thing.

Stewart: the database is located on my hard drive locally, I'm positive noone else is using it at the same time. When I close the db, the ldb file is not present. It appears as if access was opening second instance of same database in a background to create (?) object CurrentDb and put that instance in exclusive mode, but how do I close that instance?

[NeoPa]

Sorry. I wish I could help more. Normally such a problem disappears when object variables are used and CurrentDB is only called once. It's hard to say what else may be causing an issue trying to work via a web page.