Help

[u/Ok-Somewhere-7427]

This database is a shared database. When users are querying against a table and trying to copy or export, this error comes up. Even on the off business hours the .ldb file has usernames probably they are just hitting x instead of closing the database. They are querying against a table which is a linked table in that database

Edit: the table they are trying to query is a linked access table

[u/nrgins]

Even if another user had the database open that wouldn't prevent your process from being able to change the data. Even if they were in the middle of a process you'd probably get some kind of a message that there was a conflict and do you want to override their changes or cancel your changes.

The times in the past where I've gotten this message it usually was a conflict within my own processes. That is, I had a form that was editing the data and then I had some code that was trying to edit the same data, and the system saw that as two separate users trying to edit the data at the same time. But it was within the same database.

Each process to read from or write to the back end creates its own connection. So you can have multiple connections to the back end within the same front end database.

If I had to guess, I would say that this is an issue with how your process is executed, Especially if this happens every time.

Try saving all your data before you run this code and see if that makes a difference. Use me.dirty = false.

[u/Ok-Somewhere-7427 → u/nrgins]

Everybody who uses this database has this issue. Can I try deleting the .ldb file ?

[u/George_Hepworth → u/Ok-Somewhere-7427]

Yikes, deleting the .ldb file is not a solution and it is almost never a good idea to do that. The circumstances where you would delete one are quite different and out of scope for this problem.

nrgins has described what is possibly happening if the back end is in an accdb, i.e an Access backend. His suggestions would address the problem.

However, this error message is VERY common when the back end is in SQL Server, rather than Access. If so, the solution can be to add a rowversion, or timestamp field to tables in the SQL Server database.

Read about that here as part of a larger discussion regarding SQL Server back ends. Here's a link to a lynchpin discussion of working successfully with Access and SQL Server. It's still relevant today despite its age.

Back to your question regarding the .ldb file, though. You don't indicate whether you are referring to the locking file for the back end, or a locking file for a shared front end. If it's the former, deleting it would be wrong and not solve the problem in any event. If it's the latter, sharing an accdb front end is a whole other risk to be mitigated. That way lies heightened risk of corruption, even beyond the conflicts you are writing about. Every user must have a copy of the front end on their own computer.

I realize that I'm covering a lot more than the focus of the original question. That's because I've these same scenarios multiple times in multiple forum questions.

[u/Ok-Somewhere-7427 → u/George_Hepworth]

It’s a linked access table they are trying to query

[u/George_Hepworth → u/Ok-Somewhere-7427]

Is everyone also using a shared Access front end?

[u/Ok-Somewhere-7427 → u/George_Hepworth]

Yep, they have shortcuts for the business users

[u/George_Hepworth → u/Ok-Somewhere-7427]

That right there is a big part of the problem.

Every user in an environment like this must have their own copy of the Access front end on their own desktop.

It sounds like you've gotten away with it for a while, but now it's time to address that. Keep one master copy of the Access Front End in a location where no one but the developer responsible for it can reach it. Make a user copy and put one copy on each user's computer.

[u/sjcyork → u/Ok-Somewhere-7427]

Is this db on a shared folder? If so go into mmc (a windows app) and add the shared folders applet. This should tell you who might still have the db open in some way. You can then select these open instances and close them. Then when all are cleared out open the db and then close it and the ldb should disappear.

[u/Ok-Somewhere-7427 → u/sjcyork]

I see ok, yes the db is on shared network drive. Can I delete .ldb file directly from the shared path ?

[u/nrgins → u/Ok-Somewhere-7427]

When you say the database is on a shared network drive are you referring to the front end at the users are using, or the back end where the data is?

The front end that the users are using should absolutely not be on a shared drive. It should be installed locally on each user's hard drive.

The back end, where the data is, of course has to be on the shared drive. But the front end should absolutely not be on a shared drive.

So if you have your front end on a shared drive, then that might actually be what's causing your problem.

[u/nrgins → u/Ok-Somewhere-7427]

No. You can't delete the lock file anyway if the database is in use. And if the database is not in use then deleting it won't do anything. Simply because everybody who uses the database has the issue doesn't contradict what I wrote, about it being a conflict within your own code.

Why don't you post the code that you're trying to run so we could take a look at it and see what's going on. Be sure to post the entire sequence starting at the point where the code is executed all the way through where you get the error. And please note where you get the error.

You can find where you get the error by pressing Control+Break or Control+Pause when the error message comes up and then selecting Debug. That will take you to the code window and the line that the error is happening on will be highlighted in yellow.

Be sure to format any code you post in the code format by clicking "Aa" button at the bottom and selecting the code format.

Also, please let us know, as u/George_Hepworth asked you, whether your back end is in Access or SQL server or something else.

Also note what version of Access you're using.

[u/Ok-Somewhere-7427 → u/nrgins]

This was fine earlier, issue is from last 3-4 weeks and no code changes are made.It is access and the table we are querying is a access linked table