Unexpected "no exclusive use" problem

  • Thread starter Thread starter Bill Manville
  • Start date Start date
B

Bill Manville

Access 2002 SP3 on Windows XP-Pro. All critical updates.

Access 2002-format single user database, split front+back ends.
One instance of Access running.
No relevant LDB files before or after run.

AutoExec macro opens a form named Menu (just a switchboard with buttons
on it).

After closing the Menu form I can't make design changes in the
database:
"You do not have exclusive access to the database at this time...."

Opening with shift-key held down enables me to make design changes but
if I then open and close the Menu form the problem recurs.

By commenting out code in Form_Open I found the problem occurred if and
only if the following was executed:
Dim DB As DAO.Database
Set DB=CurrentDB
Set DB=Nothing
(originally there was some useful code in there!)

I have tried all of the following, without success:
- Tools/Database Utilities/Compact and Repair
- Help/Detect and Repair
- /decompile switch
- Make a new database and copy everything into it (but copying the VBA
code separately)
- Tools / Options / Advanced /Default Open Mode=Shared (or =Exclusive)

(I do have Access 2000 and 2003 installed on the same machine)

Any suggestions?
Am at wits' end.

Bill Manville
(Excel MVP)
 
hi Bill,

Bill said:
By commenting out code in Form_Open I found the problem occurred if and
only if the following was executed:
Dim DB As DAO.Database
Set DB=CurrentDB
Set DB=Nothing
Click to expand...
Try commenting out the Set DB=Nothing, it is not necessary for CurrentDb.

Also you can gain speed if using Michael Kaplans solution (place it in
any module), because CurrentDb creates every time called a new instance:

Private m_db As DAO.Database
Public Property Get CurrentDbC() As DAO.Database
If (m_db Is Nothing) Then
Set m_db = CurrentDb
End If
Set CurrentDbC = m_db
End Property

In every place where you need CurrentDb you can use it directly:

Dim rs As DAO.Recordset

Set rs = CurrentDbC.OpenRecordset()

mfG
--> stefan <--
 
Thanks. It did fail with or without Set DB=Nothing; I was just trying
to forestall a suggestion to set it to Nothing!

I appreciate you passing on MickKa's tip.
I implemented it.
But opening and closing my menu form still causes the problem.

Form_Open now reads:

Private Sub Form_Open(Cancel As Integer)
Dim T As DAO.TableDef
For Each T In CurrentDbC().TableDefs
Next
End Sub

After opening and closing the form I can no longer make design changes
until closing and re-opening the database.
Comment out the For,Next lines and no problem.

More ideas welcomed!

Bill Manville
MVP - Microsoft Excel, Oxford, England
 
Third instalment:

Copied the database to another machine - no sign of the problem.

Went back to the original machine, reloaded Access, re-opened the
database - no sign of the problem!

Closed Access and re-opened - the problem re-appeared.
Closed Access and re-opened - the problem persisted.

Duh?


Bill Manville
MVP - Microsoft Excel, Oxford, England
 
hi Bill,

Bill said:
Third instalment:

Copied the database to another machine - no sign of the problem.

Went back to the original machine, reloaded Access, re-opened the
database - no sign of the problem!

Closed Access and re-opened - the problem re-appeared.
Closed Access and re-opened - the problem persisted.

Duh?
Click to expand...
Sounds like code corruption.

Try the compare and repair function.
Turn off the Objectname-Autocorrection.
Try to import all your objects into a new .mdb.

Any nasty scanner (Norton, Symantec) installed?


mfG
--> stefan <--
 
Already tried compact & repair

Already imported all objects into a new MDB
(3 or 4 times,including once when I stripped out the VBA code while
importing and replaced it in the destination)

I have Norton Antivirus installed; tried turning it off for a while
yesterday; no effect.

I have just turned off name autocorrect as you suggested - no immediate
effect.

:-(

Bill Manville
MVP - Microsoft Excel, Oxford, England
 
hi Bill,

Bill said:
Already tried compact & repair

Already imported all objects into a new MDB
(3 or 4 times,including once when I stripped out the VBA code while
importing and replaced it in the destination)

I have Norton Antivirus installed; tried turning it off for a while
yesterday; no effect.

I have just turned off name autocorrect as you suggested - no immediate
effect.

:-(
Click to expand...
Maybe re-installing MDAC2.8/Jet SP8.

Your frontend is stored on a local folder?

mfG
--> stefan <--
 
Stefan said:
Maybe re-installing MDAC2.8/Jet SP8.
Click to expand...
Please remind me how best to do that.
Your frontend is stored on a local folder?
Click to expand...
Yes. All files are local

Are there any issues with Access 2003 and 2002 on the same machine?
I installed 2003 ages ago and have only run it once, a few weeks ago,
when someone reported a problem in 2003. I don't THINK I opened an
ancestor of this database in 2003, but may be wrong.

Bill Manville
MVP - Microsoft Excel, Oxford, England
 
Stefan said:
Just install it.
Click to expand...
I'm reluctant to install it since it does not include Windows XP in the
list of applicable systems and the version number quoted (4.0.7328) is
earlier than my MSJET40.DLL (4.0.8618.0)

Bill Manville
MVP - Microsoft Excel, Oxford, England
 
Thanks for the more appropriate link.

However, it says
<<
Important If you are running Windows XP SP2, you have a later version
of Jet 4.0 than the version that is included with Jet 4.0 SP8.I am running Windows XP SP2 so again I am reluctant to download an
earlier version.

Bill Manville
MVP - Microsoft Excel, Oxford, England
 
Thanks for the more appropriate link.

However, it says
<<
Important If you are running Windows XP SP2, you have a later version
of Jet 4.0 than the version that is included with Jet 4.0 SP8.
I am running Windows XP SP2 so again I am reluctant to download an
earlier version.

Bill Manville
MVP - Microsoft Excel, Oxford, England
Click to expand...

I've run across a couple of scenarios:
1. "If the database file and the workgroup information file have the same name
and are located in the same folder, two record-locking information files (.ldb)
are created when an Access object is opened."
See: http://support.microsoft.com/?kbid=839782

2. You have Adobe Acrobat or Avery Personal Assistant installed. These two
programs may install buttons on the Access toolbar that somehow cause this
problem. It's my understanding that with Acrobat you can go into it's settings
and deselect it's ability to work with Access (which is a bummer if you need it
to). I believe that with Avery you might have to uninstall it.

Hopw this helps,
RD
 
Hi RD

Thanks for the useful suggestions.

The database name is "Structure Developer.mdb". It doesn't use
user-level security and there is no mdw file with that name so I don't
think that's the issue. Anyway, I have renamed it a few times when
rebuilding it and the problem persists.

I do have Acrobat Reader 5.0 installed, but I don't see any sign of it
in Access or any Access-related options in Acrobat Reader. I don't
have the Avery Personal Assistant.

Other suggestions welcomed.

Bill Manville
MVP - Microsoft Excel, Oxford, England
 
Hi RD

Thanks for the useful suggestions.

The database name is "Structure Developer.mdb". It doesn't use
user-level security and there is no mdw file with that name so I don't
think that's the issue. Anyway, I have renamed it a few times when
rebuilding it and the problem persists.

I do have Acrobat Reader 5.0 installed, but I don't see any sign of it
in Access or any Access-related options in Acrobat Reader. I don't
have the Avery Personal Assistant.

Other suggestions welcomed.

Bill Manville
MVP - Microsoft Excel, Oxford, England
Click to expand...

Yeah, Reader wouldn't do it.

Is there a hidden form with a timer running? Ok, I'm just shooting in the dark.
Sorry I can't be of more help.
 
No. Nothing that sophisticated.
I'll be working on it again on Monday and will report back if I ever
find a solution

Bill Manville
MVP - Microsoft Excel, Oxford, England
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Exclusive use- ? coding issue? 3
Exclusive use notification 13
Exclusive Access Problem 1
Access how to open existing database from another database using Access - VBA 0
Access Exclusive Use problem in MS Access 1
Exclusive use database 1
Convert TO 2007; exclusive problem 1
Automating CompactRepair--"db in use" error 3

Back
Top