Opening, Closing, quickly Re-Opening Same Access Database via Excel VBA Access Macro Call results in Access to be Read-Only

Question

I have an Excel VBA Subroutine that opens an Access Macro and it runs a Subroutine in Access.  Then the same Excel VBA subroutine closes the Access database after Access has done what it needed to do.  The Excel Subroutine then continues running and soon thereafter re-opens that same Access Database again seconds later and sometimes the Access Database opens Read-Only...this is very bad.  Why does an Access Database open Read-Only if you open, close and soon thereafter re-open it again?  I am the only user as this is on my home PC so no one else is locking me out.  And I have seen other times where if I just open, close, and re-open an Access Database it still opens Read-Only the second time on occasion.  What can I do to fix this issue and why does it happen?  I've even built in Delay() functions to give Access time to "rest" a minute or two and it still isn't a flawless process.  It's very annoying.  Is there potentially a way to Call an Access Macro from Excel VBA without physically opening (OpenCurrentDatabase) the Access Database as a workaround to avoid opening, closing, re-opening?  Am I doing something wrong?  Thank you for any thoughts or ideas.

Answer 1

Although to supplement what I recently replied...my code still crashes sometimes because the Database sometimes opens Read-Only even if the Locking file doesn't exist.  So my recent solution doesn't really completely fix the problem that I am having.  In summation there are instances of the Database opening Read-Only even if there is no Locking File present.  Hmmm...annoying...  Is there a way to say...once I open the Database...or better even before I open it... "If in a Read Only state then Delay until Not Read Only?"

Answer 2

This Excel and Access is all on my home PC.  So there is no network involved.  And it's only me using it so no user is locking it - when I close the Database then no one else is in it and no other files are locking it.  The delay is just Windows/Access being difficult and not releasing the locking file in a timely manner.  I was slightly confused on your code earlier... You used 100...is that 100 seconds? Or 100 loops? And if loops then I assumed that could take milliseconds or however fast the processor can run it 100 times.  Because I wasn't sure "how long in time" the 100 was...I did something like this... 

[code]

Dim z as Long

For z = 1 To 1000000

        DoEvents

        If Dir(strDBLockPath) <> vbNullString Then

            Call Delay(0, 0, 1)

        ElseIf Dir(strDBLockPath) = vbNullString Then

            Exit For

        End If

    Next z

[/code]

Just in case 1000000 wasn't in seconds and was however fast the processor could run the loops I figured it could build in enough time for the locking file to disappear...  But then again and more importantly the Delay function should slow things down to 1 second anyway.  I know it isn't perfect but I would be content with this workaround if you think it will do what I am trying to do...delay Access just enough to rest and remove the locking file before reopening Access.  If you think my code is correct...or erroneous...please let me know.  Also, what does DoEvents do?  I've never used it before in any of my code.  Thank you for your time.

Answer 3

Deleting and creating the LDB file might take a longish time, depending on the infrastructure that is needed to do that.  Things like your network speed, network and server workload and even the depth of the path to the file.  Assuming nothing extraordinary going on, the LDB file should disappear within a few seconds, especially if the files are on your local hard disk and your system is not overloaded with other tasks.

OTOH, it another user is using the same database, the LDB file will remain open until all other users have closed out of the database.  This is one reason why each user needs to have their own local copy of the front end.  (The fact that the back end database's LDB file may never be deleted should have nothing to do with your problem.)

I can't imagine a how compacting can help.

It think there is probably a way to open Access from Execl so it won't close, but I never actually did it.  I'm  fairly sure it would be a tricky thing to do though.

Answer 4

Thank you for the reply.  You might be on to something with the .laccdb file.  Is there a way to speed up how long it locks the file?  I don't want my code to quit running if it's locked...I just want it to Delay to Call Access until Access is Unlocked without me having to build in some arbitrary number of minutes.  This Database has many internal tables and many tables linked to it which I am sure is part of the problem.  Although I have discovered that sometimes it locks a long time and sometimes it doesn't lock at all (well for a very short time) on this same Database.  And even if I manually "refresh" the folder where the Database resides after immediately closing the Database, the lock file goes away even though the Database is still locked...in the same token sometimes the .laccdb file is still there even though the Access Database is Unlocked and I am able to open it normally non-Read Only (just from what I have seen the past few minutes while playing with it).  I don't want to do this but would it help "Compacting" the Database after I open and run the Access Macro the first time to speed up the locking process?  This locking issue is inconsistent and hard for me to troubleshoot for how long it will lock.  Another idea, is it possible to open the Database in Excel VBA, have Access run its Macro and NOT close the Database, go back to Excel VBA automatically to continue the Excel VBA, then hit the Database again while still open?  I think I tried it once before and when I left the "With Statement" which opened the Database and ran the Access Macro and I believe Excel VBA closed Access anyway.  Therefore, I am not sure how to do that programmatically.  Any further thoughts or ideas is appreciated.  Thank you very much.

Answer 5

I suspect that Windows/Access has not had enough time to finish deleting the LDB file.  THe only thing I can come up with that might deal with this situation would be to add a loop to your Excel code just before the code that (re)opens Access.  It might be something like:

For k = 1 To 100

   DoEvents

   If Dir("path to LDB file") = "" Then Exit For

Next k

If k > 100 Then Msgbox("LDB has not been deleted")