CurrentDb puts me in locked mode. (1 Viewer)

[smbrr]

Hello,

The second function called by my main sub contains the following line:

Set rs = CurrentDb.OpenRecordset("Parametres")

If I have changed a piece of code before running it, it raises an error:

"The database has been placed in a state by user 'Admin' on machine 'numbers' that prevents it from being opened or locked."

If I haven't, the code works, but prevents me from doing any changes until I close and reopen it, making working on it a real pain.

Additional info:
- I'm using access 2010
- the database in on the network, but in my own folder and I'm the only one to have ever touched it.
- the database is split, in the sense that all the data is in tables by year, linked from their own .accdb file (they are so big I have one per year), and all the queries and forms are in this one.
- the first function called opens the year's database:

Set yearDB = DAO.OpenDatabase(path)

which never makes it crash in any way.
- since I've been having this issue, I have some very strange trouble later on with using Collection objects, such as having them as "Nothing" even though the Dim statement in the same function includes "As New Collection" (the collections are not globally defined), and also strange complete crashes of Access (ie, getting a data type conversion error, clicking debug, and the whole thing just crashes instantly and closes)

The code I've been working on is quite big and convulted, but solid. I've tried compacting, of course, copy-pasting the base, copy-pasting the form (seemed to work a few times but no longer), copy-pasting the code, not to avail.

What am I missing? Why do using currentDb necessitate exclusive access to the database, and how can I avoid it?

I'm 100% sure that currentDb doesn't do that in my other applications.

Thank you

 

[vbaInet]

First:

Set rs = yeardb.OpenRecordset("Parametres")

Second, what exactly are you opening, a table?

 

[smbrr]

rs is supposed to open the table "parametres" that is within the "front end" part. It's just a tiny table with some parameters I need to run my code.

yeardb is the database with the actual data, I open it remotely because what I'm coding is the import tool, and I figured it would be faster to open the database directly instead of updating a recordset through the linked table connection.

 

[smbrr]

After investiguation, I found a little more info, the .laccdb file created when I open up the database contains:

LFRMO0009498             Admin

After I run any line of code using currentDb, it becomes:

LFRMO0009498             Admin        LFRMO0009498             admin

It's not helping me so far, but I'm pretty sure that is not normal, and I'm wondering if the cause of this mess isn't the difference of capitalization between "Admin" and "admin".

Somehow, some part of Access messes it up and then duplicates it when it was meant to ignore it if already present?

 

[smbrr]

I found the solution, it only took me the entire day.

For a reason that I do not know, this whole issue happened because I was creating a pointer to another database with DAO.OpenDatabase, and later on using currentDB.

It seem that creating a pointer to currentDB, and then creating a pointer to another database does the trick. Now all my code works properly and I do not have exclusive mode issue while alone in it.

The order of creation is very important, creating a pointer to currentDB after having created my pointer to the remote database doesn't work, it will either lock the database, or raise an error if the database is already locked by yourself.

Maybe there is something I don't understand about the DAO.OpenDatabase instruction but I hope whoever has this issue comes across this thread and save themselves the hours of fiddling with completely valid code trying to figure out where it went wrong.

1. create pointer to currentDB
2. create pointer to remote accdb file X2015
3. create pointer to remote accdb file x2014
4. create as many pointers to as many accdb files as you like (i tried with 8 databases)
5. run code that uses recordsets, querydefs and other DAO objects, both read and write, but do NOT use "currentDB", use its pointer
6. close connections and delete pointers

Doing this works flawlessly for me, and I've had no other strange behavior afterwards with collections or anything.

Maybe it'll help someone.

 

[vbaInet]

Quite simple really. In my first post I highlighted that you should use the db reference to open the appropriate recordset because you were using CurrentDb which may not be pointing to the right db. We should be careful about the use of the word "pointer" as that doesn't apply in this case

1. Open the db and set a reference to it (whether you use DBEngine.OpenDatabase or DAO.OpenDatabase it won't make any difference)
2. Use the same db instance (from 1) to open as many recordsets as you wish
3. The recordset that you open should not be based on tables but rather should based on queries
4. Tables should not be in the Front End unless it's specific to the user. But then again, the said table can be moved to the backend with a UserID field to indicate which records belong to which user.
5. And most importantly, if it errors, close the db instance and clean up. Same goes for when you're done with all the recordsets.

 

[vbaInet]

I should add that with with Step 1, I prefer creating a new Access instance instead of using the current one. Kill that application instance and you kill the db and recordset instances opened. Of course you would want to sanely close your recordset and db instances but this is worth mentioning.

 

[smbrr]

But in my case I use objects that are in the current db (front end), and objects that are in other dbs (back ends). The issue was that I needed different databases to be open at the same time, not just recordsets from the same database.

My code was right the whole time, it should have worked, because I had 'currentDB' to access my front end in which the code ran, and the queries (and parameter table) were, and I had 'yearDB' to access my back end in which I imported the data by updating the recordset with .AddNew.

In the end it worked by virtually switching two lines of code so one would be executed before the other, when it shouldn't matter at all.

As for the parameter table I use, it's linked to the program I write, and the parameters in there are not going to be changed by any of the few users, it's just a tool for me to speed up the process when I'll have to change stuff later on.

You could say that I'm overcomplicating because I could have accessed my 'yeardb' through the connection that it has in 'currentdb' (being its back end), but like I said, I thought it would speed up the importing by connecting directly with DAO, which might not be the case at all.

PS: for your point #3: if I'm importing data through a DAO.Recordset.AddNew function, why should I not import in the table but rather in a query? Seems like a useless complication.

 

[vbaInet]

Ok, now that you've clarified where the Paramters table is located then your case is different. The generalist view when one says Front End is that tables don't exist in that part of the db. What I explained above is a general view where all tables reside in the BE.

Before I delve into further details and give some more pointers, can you answer a few questions:

1. Is the Parameters table a linked or local table?
2. If the Parameters table is just for your use, why place it in the FE which is distributed to all users?
3. When you say AddNew, are you adding records to the Parameters table or other tables?
4. In what context are you using AddNew, i.e. are you looping through another recordset, performing some checks and then adding missing/updated records?

 

[smbrr]

1. Parameters is a local table
2. I place my parameter table in the FE because the BE is actually 8 BE's and it feels right to put it there. There are also other parameters tables such as "sales area" that will be used in JOINS with the BE tables to pull out some stats, and those need to be local to each instance of the FE because users are gonna change the sales area to pull out different stats, and they need to be able to run them together.
3. I use addnew to import data in one of the 8 tables in one of my 8 back ends.
4. It's used in a loop that read different Excel files, play with some data in other tables of the other back ends, filter with some of my parameters from the parameters table (there will eventually be more parameter tables) and so on.

I'll admit that I'm pretty much self-taught on MS Access and while I understand the concept of FE and BE, I don't follow a particular way of doing it, I just build my thing how I have it in mind.

But, regardless, that issue with CurrentDB seems to be a bug and not a design flaw.

 

[smbrr]

And, typing about it, I realize that indeed my parameters table would sit well in its own database, as it'd allow me to change parameters without needing to redistribute the FE to users, but it's supposed to never change unless I do design change aka adding/modifying modules and forms, so I'll have to redistribute it anyways.

 

[jeffgerritsen]

I ran into the same problem today in Access 2013 64 bit version. What cleared the problem for me was to go into Options-> client settings-> advanced and set the default open mode to "exclusive".

Opened the database again and same problem but no .laccdb file. Still could not modify any forms. Closed the database, opened it back up, went back into Options and changed default open mode to "shared". Selected Okay and closed the database.

Next time I opened the database everything was working correctly!

Go figure! Must have cleared some exclusive locking bit in the bowels of access!

Spent a lot of time doing this: :banghead:

Hope this helps others.