VBA: Can't save because I don't have exclusive access...

[dsatino]

Ok, this is an easy one that I've solved in the past but I just can't for the life of me remember how.

I've been asked to modify a front end and am working on a copy stored on my desk top so I know there's no one else using it.

Here's the issue: any time I run any code with any initialized object variable, I can't write any more code without shutting it down and starting it back up because it won't let me save any work because '[another user has the db open]'.

I'm setting all the object variables to Nothing, but that doesn't help so I know there's nothing lingering open.

[NeoPa]

I really don't know about this one. Let's try to find something :

1. First look at the Locals Pane (View \ Locals Window from the IDE). Is there anything there that indicates anything is still maintained? Particularly a reference to the database?
2. Next look at the stop project button that's available in the Standard toolbar. Does it allow you to save after clicking on that?

Not much I know, but see if either gives you anything.

[dsatino]

Nope, neither of those helped. Here's an update though.

I've traced this back to the .ldb file which has two occurrences of my machine as having the file open. Essentially, what's happening is that one occurrence is opened when I first open the file and the other occurrence is opened when I run the code.

I decided to start from the beginning of the code and add pieces on until I acheived this second lock in the .ldb.

Fortunately, that didn't take long at all. Here is the code that causes this:

Public Function CreateWrkJet()
Set wrkjet = CreateWorkspace("Jet1", "Admin", "", dbUseJet)
DBEngine.Workspaces.Append wrkjet
End Function

For some quick background, this is code I use in basically every interface I build so it's time tested. All of the variables are declared globally in a seperate module.

Creating the new workspace doesn't cause the lock, but appending to the collection does.

What's odd is that I use this same snippet of code in dozens of other projects and have not had the same issue. I even opened those projects up and couldn't reproduce it. So I have the same exact code running in different projects with different actions being taken with respect to the .ldb file.

To further my problem is that any project I start (just test db's), I have this issue. I've taken all of the properties from the projects that it works fine with and set my new db to imitate those, but it's had no effect.

Now, I was able to get this to work by adding a line of code:

Public Function CreateWrkJet()
Set wrkjet = DBEngine.Workspaces(0)
Set wrkjet = CreateWorkspace("Jet1", "Admin", "", dbUseJet)
DBEngine.Workspaces.Append wrkjet
End Function

It seems that if I initialize the workspace variable in the default workspace first I don't have the issue. So it seems there is something I'm missing with respect to the DBEngine and how it handles locks, database properties, and sharing. Maybe more. Maybe less, I don't know.

[dsatino]

It just occurred to be that I was calling a function with no result. Why would I do that I wondered. It then occurred to me that the only reason I would write a function that doesn't return a result was to call it from the AutoExec macro.

This explained why this code worked on all my other projects and not this one.

I guess that by specifically referencing the Jet engine on open causes the user and the code to seen as one in the same (for lack of a better way of explaining it).

[dsatino]

So I put this in the AutoExec macro and I have no more issues:

Public Function WS0Ref()
With DBEngine.Workspaces(0)
End With
End Function
 

[NeoPa]

It seems the original code may have been developed to give your code access to the database. I don't use similar code myself, but I'm guessing DBEngine.Workspaces(0) is already set, before you do anything to it even, to a working copy of the current database.

You may want to try accessing it without ever setting it first. As I say, I don't really know my way around this area, but I would expect that to work for you from what you've posted.

By the way, my first point in post #2 was an attempt to find just such a thing as this. An open database or workspace would be expected to lock the database from you. You found a better way of discovering it though. Well done.

[dsatino]

The code is all my own. Most of my interfaces hit various DB's and DB types so I use a lot of workspaces and hence a have a lot of canned code that I just use over and over.

This particular one (Jet1) is the one I use to refer to the current db. (There are various reasons I don't use the currentdb function).

In any case, DBEngine.Workspaces(0) is the default workspace of any Access DB and it can be accessed without setting it.

Apparently what happens, or my best interpretation of what happens, is that the JET DBEngine workspaces collection creates a user lock when it's first opened/referenced/loaded as if it's a user. So when you open the DB you create a .ldb. Now if you create a new workspace, a lock is created as well. So essentially your user and your code, although on the same machine are seen as seperate users.

Apparently, by referencing the the workspace collection during the load of the database, there is no distinction between the user and the code.

I have no idea if that makes sense, but it seems to be the case

[NeoPa]

It sounds like we're saying quite similar things, just not in the same way. I'm pleased you have a solution anyway :-)