Exclusive access error

[Middlemarch]

I've made a mistake somewhere and getting error "You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later."


This is with all objects closed. (As well as trying to edit code)

Is there any command to restore 'exclusive access'? There's no other user in reality.

Or, is it possible to check in various procedures to see where the problem starts and hopefully what's causing it ?

Thanks.

[Micron]

I'd start by looking at Task Manager (ctrl+shift+esc) to see if there is a copy that is not visible but is still loaded into memory. You must look at both the Apps and Background Processes list to be sure. If you find nothing, check the file properties in File Explorer. After that I might also try opening Access first, and then open the db rather than opening the db directly if that is what you do. Do nothing except File/Save and reopen and see if the issue persists.

[Middlemarch]

Thanks for replying... I couldn't find a 2nd Access in Task manager.
After a bit of playing, seems i can make it error on will, so will work slowly through the code hoping to isolate the problem area.

[Middlemarch]

Not sure if I've made much progress but the error occurs on line


Set mp = Form_frmMain.subDisplay.Form.RecordsetClone

Run—time error '3734':
The database has been placed in a state by user 'Admin' on machine
'WIN10—PC' that prevents it from being opened or locked.

Mp has been Dimmed as DAO.Recordset.
Nothing has caused this state intentionally.

[CJ_London]

any reason for using the recordsetclone which is read only? have you tried just using the recordset?

and you need to clarify - you said

This is with all objects closed.

but your code is referring to an object that must be open

Set mp = Form_frmMain.subDisplay.Form.RecordsetClone

and why the 'Form_frmMain'? which is the method to refer to a forms vba code, usual construct is 'Forms.frmMain' and if the subform is in the same form as the code , just 'Me.'

And as an aside you may not be able to reference a clone on another form (a subform is still another form)

[June7]

CJ, why do you say recordsetclone is read-only? I can certainly edit data via recordsetclone.

Also, I have no problem with code in one subform opening recordsetclone of another subform and editing its data. Middlemarch's syntax worked for me.

Really don't have enough information to analyze issue.

[CJ_London]

CJ, why do you say recordsetclone is read-only? I can certainly edit data via recordsetclone.

see remarks in this link

https://learn.microsoft.com/en-us/of...recordsetclone

'This property is available only by using Visual Basic and is read-only in all views.'

agree, not enough info - so just speculating - particularly the use of 'form_....' which is a visual basic construct for calling public functions in other forms, not an 'object' that can be assigned to another.

Are you saying it works for you exactly as the OP has written (subject perhaps to a different form/subform) with form_...

Wasn't aware the OP wanted to edit the data

[June7]

I don't know if OP wants to edit data. Only responding to your point about read-only. I stand corrected. RecordsetClone property is read-only. OP is setting a recordset object variable to recordsetclone dataset and that recordset object is editable.

Yes, I tested with Form_ syntax.

RecordsetClone is commonly used to search form's dataset for a record without form reflecting that search then set focus to a record on form using Bookmarks only if match is located.

[CJ_London]

Yes, I tested with Form_ syntax.

new one on me

RecordsetClone is commonly used to search form's dataset for a record without form reflecting that search

I know - but you don't normally assign it to another object

OP still not clarified if the form is actually open or not - I presume in your test it was open?

[June7]

See explanation in https://accessexperts.com/blog/2011/...ecordsetclone/

It's not necessary to assign to a recordset object variable but works.
I would probably just use With Me.RecordsetClone.

Yes, form was open.

[Middlemarch]

Understood the question could have been better, but it's a bit hard to describe what's happening with an unhelpful error you can't trap.
E.g. It can occur just when typing some code in while stepping/debugging.
Is there no way to see how many users access thinks it has? If there is I could find and check why at the point it changes.

[June7]

The lock file can show how many connections are using backend.

[Micron]

If the db is not split, then the lock file to look at would be the db itself? We should first know if this db is split or not.
There are also these reasons

The Compact On Close database setting is selected.
The Track name AutoCorrect info and Perform name AutoCorrect database settings are both selected.
The form or report object you’re trying to open contains an embedded macro within a control or form event.

[davegri]

E.g. It can occur just when typing some code in while stepping/debugging.

Do you have a timer event running on an open form when this happens?

[Middlemarch]

I appreciate very much everyone's comments. After 3 or 4 days I'm no closer to figuring out what I've done wrong.
So I've made a cut down version of the db to demonstrate the error.
It's invoked by


Run listtest
Open frmMain
Click Import
Chick the "Other' button
Select the combo dropdown
Check some items
Click Action


It should stop in Private Sub btnImport_Click().
After which you often cannot type into the editor withing getting the error.
Somtimes too, it would stop three lines above Stop on Set EXTDB =...


Then any attempt to Save or do anything else causes the error. I can only move on from this by saying No to the save prompt then a compact and repair. Of course the same things repeat when next run.
Microsoft Access can't save design changes or save to a new database object because another user has the file open. To save your design changes or to save to a new object, you must have exclusive access to the file.


Apologies for the lack of comments in the code, and it's amatuerishness. it's purpose it to copy selectable fields from another db into the current record.
Everything needed is in the zip file.
Listtest.accdb
mp3ListBEmdb
OList_BE.mdb