Access says Single user database is locked by other user.

[Red1]

Access says my files are locked or being used by another, but the database is set for exclusive use on single-user system.

I have demo database with one form that has just one button. The button event “on click” runs the following code:

Option Compare Database
Option Explicit

Private Sub Command1_Click()
Dim theDB As Database
Dim theDBName As String
'


theDBName = Access.CurrentProject.FullName
Set theDB = OpenDatabase(theDBName)
theDB.Close
'
End Sub

The program functions normally, but if I put the form into design mode and then reopen the form, I get the following error 3734: The database has been placed in a state by user “Admin” on machine “[machine name]” that prevents it from being opened or locked.

The File>Option>Default open mode is “Exclusive” and the Default record locking is “All records”. It is being used on a stand-alone machine. File>Options>Current Database>Compact on Close is not selected and Perform name AutoCorrect is not selected. There is a .laccdb file showing [machine name], “Admin”, [machine name], “admin” which is consistent with the names in System.mbd. The file is not password protected. I have administer rights. This happens on more than one computer running different operating system and reinstalled Access 365 on one machine. I am logged in to Windows with a user’s name that is not Admin.

I'm out of ideas. Many thanks for your assistance!

[isladogs]

I know you said this is a demo database but I can't see why you'd ever want to do this.

You have a database which you've opened exclusively (so no other copies can be opened) and you want to open the same database again and then close it! WHY?
If you try to do so, you will get error 3045: File already in use ...it should be obvious why!

If you open the database in shared mode, no error will occur but I still don't see the point. Perhaps I'm missing something?

As for your error 3734, the other user is yourself. Basically its similar to a write conflict error

BTW
1. If you open a database exclusively, no lock file is created. So your .laccdb file isn't from this session
2. You mentioned System.mdb - are you referring to an old MDB database with user level security?
3. Access will always report the user as ADMIN

[Red1]

Many thanks for your input, Isadogs. I really appreciate it! i've been writing extensive multi-user Access programs for years, but I'm self-taught and have gaping holes in my understanding.

When is open in shared mode, the same thing happens. I open the form, click the button several times, and everything run normally. Then I switch the form to design mode (and make no edits), then reopen the form, clicking the button returns the error message 3734. However, if I switch the form to design mode and then reopen it, then open the VBA editor, save and close the editor, the button works works normally.

Your answer stated that I should not be opening the database in exclusive mode, since the database is already open. Please explain the proper way to set the database variable theDB to database without the line: set theDB =OpenDatabase([name]) in exclusive mode.

[Micron]

If you set a public (global) variable upon form open, then switch to design view, the value of the public variable will persist. So perhaps when your code runs again, you're trying to do something you've already done and that's the cause. A lock on a table could do the same thing if attempted again. You could try trapping and ignoring the error but that can be dangerous practice in some cases - don't know if this is one of them. What I don't get is, if you can use this form several times without an issue, why are you putting it in design view and then re-opening? Did you try going from design to close then re-open to see what happens? Perhaps it is still under development and this issue will go away once the form is finalized?

[isladogs]

Many thanks for your input, Isadogs. I really appreciate it! i've been writing extensive multi-user Access programs for years, but I'm self-taught and have gaping holes in my understanding.

When is open in shared mode, the same thing happens. I open the form, click the button several times, and everything run normally. Then I switch the form to design mode (and make no edits), then reopen the form, clicking the button returns the error message 3734. However, if I switch the form to design mode and then reopen it, then open the VBA editor, save and close the editor, the button works works normally.

Your answer stated that I should not be opening the database in exclusive mode, since the database is already open. Please explain the proper way to set the database variable theDB to database without the line: set theDB =OpenDatabase([name]) in exclusive mode.

That's not quite what I said. Opening a database in exclusive mode means another copy cannot be opened, either from File Explorer or by code within Access itself.
That is the whole purpose of Exclusive mode.

There were several things that made no sense in your account
1. Why are you trying to do this at all?
2. You referred to a lock file but no lock file is created when opened in exclusive mode
3. You also referred to System.mdb. Why?

I can get the same error as you but not by changing to design mode.
Open in Shared mode (the normal default).
Comment out the line 'theDB.Close.
Now run your button code. It will work without error and the second copy will remain open.
The lock file will show 2 users on the same PC as you would expect.
Now click the button again. It should show error 3734

But at the end of all of this, I'll ask again. Why are you doing this?

[Red1]

Many thanks for your suggestion.

My database is under development and this problem really slows down the development. This demo code is the complete VBA code in the demo program. There are no other declared variables, functions, calls, modules, etc, and no tables. It has only one form containing one button yet the problem persists. Perhaps you have time to create a new database, add one form with one button, and run this code on the click event. Open the form, click the button, and then put the form in design mode. Do you get an error message?

[isladogs]

Hi again
That's exactly what I did before replying. Tested in A365 32-bit
The database I created is attached in case it helps
Perhaps it would have helped to upload yours!

BTW you still haven't answered any of my questions

[Red1]

Sorry I missed your reply Isadogs. Here are answers to your questions:

1. I am developing a hardware and software system that measures 16 forces for a research project. I am using Access to read serial data from four comm ports (reading serial data into Access is a bit tricky) and save the data in the database tables for further analysis. I prefer the ADO method for reading and writing data to the tables. I have been developing the database for over a year. The problem I described started happening in the last few weeks. There are other variations on the problem, but all error messages indicate Access believes the database is being used by another user. I've tried running in both shared and exclusive, and tried breaking the database into front and back ends. I don't understand the problem, so I included everything that I thought might possibly be relevant.

2. and 3. I didn't know if this was relevant to the issue so I included it.

The demo code was intended for debug purposes. I normally do not close the database, but I included the close here in case opening an already open database might be why the program is sending user conflict errors.

As you suggested, I changed to shared, commented out the close. I opened the form, clicked the button a few times, and then changed to design mode. I reopened the form, clicked the button, and yes, I also get error 3734. If I open the VB editor, save the code and close, the button starts working again. Is there a better work around? Is there a way to determine if the database is already opened so I can bypass the second OpenDatabase statement?

Again, many thanks for your help. I've learned a lot.

[Micron]

Isladogs isn't the only one not getting questions answered

One thing you might try is to copy the db and then decompile the copy. Then open it, open the VBE and compile the code project again. Close the db then open and test. I don't have high confidence that it will solve your issue, but since opening the vbe and saving code seems to be a temporary fix, the code is a bit suspect. Then there is always the possibility of corruption.

[Red1]

Hi Micron, My answer at 4:04 was my response to you. This is my first time using the forum. Sorry for my mistakes. This code was never compiled.

Isadogs repeated and identified the problem. I was trying to open the database twice. It seems obvious once he pointed it out, but I just couldn't see it. I was calling the OpenDatabase statement on the open form event. If I put the form in design mode and then reopened it, it ran open form event again and tried to run opendatabase again. This caused the error message. I think moving the OpenDatabase statement out of the open form even will solve the problem. I've wasted hours on this blunder. You and Isadogs have been a big help getting me back to work. Many thanks!

[Micron]

Yes I saw your reply but not an answer to

Did you try going from design to close then re-open to see what happens?

Not as important I guess is why you need to go from form to design back to form view but I didn't see where that was answered either.

I guess I was close, just not close enough

perhaps when your code runs again, you're trying to do something you've already done and that's the cause.

Kudos to Isladogs and glad you have a solution now.

[isladogs]

Apologies if you've already answered this but I'm still unclear what the purpose is.
None of your explanations about COM ports seem to be relevant to my understanding of the point of this thread
Wherever you run the code, the point is you are trying to open a second copy of the currently open database.
There is no need to check if it is already open. By definition, the current database is already open

[Red1]

I'm afraid I misunderstood your question. My explanation in my just previous post is the best that I can do. In any case, I think you have given me what I need to fix the problem. I will not able to do further test until tomorrow, but if it is fixed then, I will mark this thread solved and show you as the one who solved it. Please stand by until then.

[Red1]

The problem is solved by replacing

set theDB = OpenDatabase(theDBName)

with

Set theDB = CurrentDb

Many thanks for all of your help!

[isladogs]

LOL! After all that....