MS Access 2010 / Windows 7: Run-time error: 3045 could not use c:\[Test2003].mdb; file already in use:
Hi,
I have a front end running Excel vba ( .xls format ) interacting with Access ( .mdb format and also serves as front end )
The environment is : Windows 7, Excel 2010 / Access 2010.
I am receiving intermittent error messages when calling the Access database file to perfrom 3 to 4 routines within the Access environment, from Excel . The MS Access file will be required to be open and close for each such routine in the process
e.g. Run-time error: 3045 could not use c:\Test2003.mdb; file already in use, or
Run-time error: 3027 Cannot update: Database or object is read-only.
The concerned file c:\Test2003.mdb is dedicated to a single user and so I believe the errors are likely to do with instances / sessions of the same DB ( the routines will call for the opening and closing of the DB from the Excel vba to perform routines to refresh links etc ).
Each time the error occurs, I will close the Excel and Access files,
a) wait out or in more extreme cases
b) after a re-boot to open the Excel file.
It will work but I get the intermittent error messages referring to the above run-time error i.e. file in use ( and the DB will shows it is in read-only mode ).
How do I overcome these run-time errors?
Thank you in advance for your kind help.
I have a front end running Excel vba ( .xls format ) interacting with Access ( .mdb format and also serves as front end )
The environment is : Windows 7, Excel 2010 / Access 2010.
I am receiving intermittent error messages when calling the Access database file to perfrom 3 to 4 routines within the Access environment, from Excel . The MS Access file will be required to be open and close for each such routine in the process
e.g. Run-time error: 3045 could not use c:\Test2003.mdb; file already in use, or
Run-time error: 3027 Cannot update: Database or object is read-only.
The concerned file c:\Test2003.mdb is dedicated to a single user and so I believe the errors are likely to do with instances / sessions of the same DB ( the routines will call for the opening and closing of the DB from the Excel vba to perform routines to refresh links etc ).
Each time the error occurs, I will close the Excel and Access files,
a) wait out or in more extreme cases
b) after a re-boot to open the Excel file.
It will work but I get the intermittent error messages referring to the above run-time error i.e. file in use ( and the DB will shows it is in read-only mode ).
How do I overcome these run-time errors?
Thank you in advance for your kind help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
I have covered points 1, 2 and 4 as much as possible. There appears to be an improvement but still the occasional runtime error, indicating the mdb is in a "read-only" state although it has been closed and there is no .ldb in the same folder also.
The file in question, c:\Test2003.mdb has been subjected to frequent opening / closing depending on the requirements. I have inserted Excel .wait method in between sessions to allow time for the DB to revert from the "read-only' state to a normal state. This appears to help but not all the time.
To remove this bug, I am moving to point 3 i.e. deactivate the AVG Microsoft Office Plugin which virus scans Word, Excel, and PowerPoint documents as you open them. The suggested code for use is :
regsvr32.exe /u "C:\Program Files\Grisoft\AVG7/avgoff2
The machine has an AVG ver 10 installed. I cannot locate avgoff2k.dll or its equivalent.
I have 2 questions that would appreciate your help with.
a) does MS Acces DB get scanned by AVG 10, when opened?
b) if so, how do I deactivate the AVG Microsoft Office plugin for AVG 10?
Thank you for your kind attention.
I have covered points 1, 2 and 4 as much as possible. There appears to be an improvement but still the occasional runtime error, indicating the mdb is in a "read-only" state although it has been closed and there is no .ldb in the same folder also.
The file in question, c:\Test2003.mdb has been subjected to frequent opening / closing depending on the requirements. I have inserted Excel .wait method in between sessions to allow time for the DB to revert from the "read-only' state to a normal state. This appears to help but not all the time.
To remove this bug, I am moving to point 3 i.e. deactivate the AVG Microsoft Office Plugin which virus scans Word, Excel, and PowerPoint documents as you open them. The suggested code for use is :
regsvr32.exe /u "C:\Program Files\Grisoft\AVG7/avgoff2
The machine has an AVG ver 10 installed. I cannot locate avgoff2k.dll or its equivalent.
I have 2 questions that would appreciate your help with.
a) does MS Acces DB get scanned by AVG 10, when opened?
b) if so, how do I deactivate the AVG Microsoft Office plugin for AVG 10?
Thank you for your kind attention.
<<
a) does MS Acces DB get scanned by AVG 10, when opened?
b) if so, how do I deactivate the AVG Microsoft Office plugin for AVG 10?
>>
Afraid I can't answer either as I've never worked with AVG. I would suggest for test purposes, simply unload the current AV and run the app.
Jim.
a) does MS Acces DB get scanned by AVG 10, when opened?
b) if so, how do I deactivate the AVG Microsoft Office plugin for AVG 10?
>>
Afraid I can't answer either as I've never worked with AVG. I would suggest for test purposes, simply unload the current AV and run the app.
Jim.
ASKER
Am trying on another machine where I can deactivate the AVG, but ran into another bug. So need to fix that before able to proceed.
However, I noticed that if I wait long enough in the original machine that I used for testing, I will be able to proceed to open the DB in normal ( read-write ) state.
Is there a vba code from excel that could check the state of the DB? I could try a code with a loop ( every 10 seconds ) that opens the DB if the DB is not in read-only mode( without actually opening the DB ).
Otherwise I will have to open the DB and check if it is in read-only mode, to close it and pause before repeating until it is in normal mode which is rather cumbersome.
Would appreciate your help to the above.
Many thanks.
However, I noticed that if I wait long enough in the original machine that I used for testing, I will be able to proceed to open the DB in normal ( read-write ) state.
Is there a vba code from excel that could check the state of the DB? I could try a code with a loop ( every 10 seconds ) that opens the DB if the DB is not in read-only mode( without actually opening the DB ).
Otherwise I will have to open the DB and check if it is in read-only mode, to close it and pause before repeating until it is in normal mode which is rather cumbersome.
Would appreciate your help to the above.
Many thanks.
You know, you may be running into an OPLOCKS issue; is the DB sitting on a server?
If so, can you move it to a local PC to test?
OPLOCKS is opportunsitic locking. On a server OS, it allows for client side caching of files.
User "A" opens the file and is given exclusive access. User "B" comes along and wants access, OS goes to A and asks to break the exclusive OPLOCK and bring it back to shared.
Sounds like that is what is going on.
Configuring opportunistic locking in Windows
http://support.microsoft.com/kb/296264
Jim.
If so, can you move it to a local PC to test?
OPLOCKS is opportunsitic locking. On a server OS, it allows for client side caching of files.
User "A" opens the file and is given exclusive access. User "B" comes along and wants access, OS goes to A and asks to break the exclusive OPLOCK and bring it back to shared.
Sounds like that is what is going on.
Configuring opportunistic locking in Windows
http://support.microsoft.com/kb/296264
Jim.
ASKER
Hi Jim
Thank you for the link. The concerned file c:\Test2003.mdb is dedicated to a single user, so as to avoid this issue. This file is used to update the Share Point list via a link.
However, the input is via Excel and hence the Excel serves as front end and will need to open / close the Test2003.mdb for updating the Test2003.mdb . No other user will be using this file, Test2003.mdb to minmise the read-only issue.
So the Test2003.mdb is localised to a single user/machine - and appreciate your help with a Excel vba code that checks ( loop very 10 seconds ) the state of the DB - and only opens it when it is NOT "read-only" mode. Is this possible?
I am making the assumptions that the 10 seconds ( or perhaps 20 seconds ) will allow the local machine to clear system memory, cache etc to put the DB into normal state. At this stage, the local machine is not interacting with any other external server / devices. Just the stand-alone machine with the Excel and Test2003.mdb.
RB
Thank you for the link. The concerned file c:\Test2003.mdb is dedicated to a single user, so as to avoid this issue. This file is used to update the Share Point list via a link.
However, the input is via Excel and hence the Excel serves as front end and will need to open / close the Test2003.mdb for updating the Test2003.mdb . No other user will be using this file, Test2003.mdb to minmise the read-only issue.
So the Test2003.mdb is localised to a single user/machine - and appreciate your help with a Excel vba code that checks ( loop very 10 seconds ) the state of the DB - and only opens it when it is NOT "read-only" mode. Is this possible?
I am making the assumptions that the 10 seconds ( or perhaps 20 seconds ) will allow the local machine to clear system memory, cache etc to put the DB into normal state. At this stage, the local machine is not interacting with any other external server / devices. Just the stand-alone machine with the Excel and Test2003.mdb.
RB
<<So the Test2003.mdb is localised to a single user/machine - and appreciate your help with a Excel vba code that checks ( loop very 10 seconds ) the state of the DB - and only opens it when it is NOT "read-only" mode. Is this possible?>>
Yes it's possible. Code a Error trap when you attempt to modify the document (On Error Goto) and if you hit it, use the wait routine below to pause x number of seconds, then try again.
I would put a loop count on that however (say 5 tries) and then return an error in the program if after x tries, you still can't modify it.
Let me know if you need more...not sure what your level of coding experience is.
Jim.
Public Function Wait(intSeconds As Integer) As Integer
Dim datCurDateTime As Date
10 datCurDateTime = Now
20 Do Until DateDiff("s", datCurDateTime, Now) > intSeconds
30 DoEvents
40 Loop
End Function
Yes it's possible. Code a Error trap when you attempt to modify the document (On Error Goto) and if you hit it, use the wait routine below to pause x number of seconds, then try again.
I would put a loop count on that however (say 5 tries) and then return an error in the program if after x tries, you still can't modify it.
Let me know if you need more...not sure what your level of coding experience is.
Jim.
Public Function Wait(intSeconds As Integer) As Integer
Dim datCurDateTime As Date
10 datCurDateTime = Now
20 Do Until DateDiff("s", datCurDateTime, Now) > intSeconds
30 DoEvents
40 Loop
End Function
ASKER
Hi,
Using similar idea, I used the following codes.
For try = 1 To 8
Application.Wait (Now + TimeValue("0:00:05"))
Set db_app = New Access.Application
db_app.OpenCurrentDatabase
If db_app.CurrentDb.Updatable
' db_app.Quit 'commented this line - seems to work without this
'Set db_app = Nothing 'commented this line - seems to work without this
Next
It allows me to test the state of the DB and if it is read-only will perform 8 times with 5 seconds interval. Able to achieve 95% success.
Did I miss anything out?
Using similar idea, I used the following codes.
For try = 1 To 8
Application.Wait (Now + TimeValue("0:00:05"))
Set db_app = New Access.Application
db_app.OpenCurrentDatabase
If db_app.CurrentDb.Updatable
' db_app.Quit 'commented this line - seems to work without this
'Set db_app = Nothing 'commented this line - seems to work without this
Next
It allows me to test the state of the DB and if it is read-only will perform 8 times with 5 seconds interval. Able to achieve 95% success.
Did I miss anything out?
I would have coded a bit differently:
Dim bolDBIsUpdateable as Boolean
bolDBIsUpdateable = False
Set db_app = New Access.Application
For try = 1 To 8
Application.Wait (Now + TimeValue("0:00:05"))
db_app.OpenCurrentDatabase
If db_app.CurrentDb.Updatable
bolDBIsUpdateable = True
db_app.Quit
Exit For
Else
db_app.Quit
End If
Next
Set db_app = Nothing
This leaves the db closed assuming your going to re-open somewhere else and this is just a check. I'd also msg the user at the start of the loop "Waiting for DB to become updateable" or some such.
Jim.
Dim bolDBIsUpdateable as Boolean
bolDBIsUpdateable = False
Set db_app = New Access.Application
For try = 1 To 8
Application.Wait (Now + TimeValue("0:00:05"))
db_app.OpenCurrentDatabase
If db_app.CurrentDb.Updatable
bolDBIsUpdateable = True
db_app.Quit
Exit For
Else
db_app.Quit
End If
Next
Set db_app = Nothing
This leaves the db closed assuming your going to re-open somewhere else and this is just a check. I'd also msg the user at the start of the loop "Waiting for DB to become updateable" or some such.
Jim.
ASKER
Noted with thanks for the guidance. It is a fairly challenging issue but I believe the workaround could work satisfactorily.
ASKER
I then tested the application and it appears to be working good. There is definitely an improvement and I noticed that the DB is opening / closing ( without the read-only message ). Will continue further testing and revert. Thank you very much.