Run Time error: This network connection has files open or requests pending

I have a database that, when closed looks to see if it was backed up more than 7 days ago.  

If it sees it has a backup within the past seven days, it successfully closes out the network drive it uses to access the data tables and exits Microsoft Access -- no errors.

If it does not have a recent back up, it runs a Module process called "BackupMade" that creates a copy of the backend tables.  This works fine and comes back to the "Form_Close" Sub.

After that, if the back up was done (BkupMade = True), it also makes backup copies of all folders and files in a specified directory.  Again, this works fine.

It then runs a quick process to re-enable the "X" button for the Access application, which I disable during use so they are forced to use my "Exit Database" button to close out of the app.RTE.png
My problem starts after this point when I try to close the network drive that the database creates and uses to reach the backend tables.  When it gets to the statement:  objNetwork.RemoveNetworkDrive "Q:", I get the error in the title (see attachment).

I'm ok with coding, but not formally trained.  And, I did not write the attached code, instead got it from a forum like this where someone helped me with most of it.  I understand most of what it does, and have even been able to make my own edits to it over time.  But, I cannot figure out where I have missed closing out a dataset or some request to the data tables that is forcing the database to require the connection to the tables remain open.

Can someone help me by pointing out what is forcing this to not close?

I've attached the actual code in the shutdown Sub below:

Private Sub Form_Close()

Dim Db As Database
Dim old_RST As Recordset
Set Db = CurrentDb
Set old_RST = Db.OpenRecordset("tbl_Bkup", dbOpenDynaset)

Dim objNetwork
Dim BackupDir As String
Dim docPath As String
Dim docFolder As String
Dim CurrentState As Long
Dim fc1

old_RST.MoveFirst
BackupDir = "Q:\Backups"
docPath = "Q:\Documents"
docFolder = ParseWord(docPath, -1, "\")

If (DateValue(old_RST![BKUP_DATE]) <= (DateValue(Now()) - 7)) Then

        old_RST.Close
        Set old_RST = Nothing
        Db.Close

    'Opens form to Edit Categories
    DoCmd.OpenForm "frm_Msg"
    Forms![frm_Msg].SetFocus
    
    BackupMade (BackupDir)
    
    If (BkupMade = True) Then
        
        If (Dir(BackupDir, vbDirectory) = "") Then
        
            msg = MsgBox("The directory " & BackupDir & " does not exist. Please correct this using the 'Options Menu'. Backup is aborted.", vbOKOnly, "Error!")
            Exit Sub
        
        End If
    
        fc1 = CopyFolder(docPath, BackupDir & "\" & docFolder & " " & Format(Now(), "yy-mm-dd hh-mm"), True)

        'Re-enables the "X" button to close out of Microsoft Access
        CurrentState = SetCloseBox(True)
        
        Set objNetwork = CreateObject("WScript.Network")
        Set filesys = CreateObject("Scripting.FileSystemObject")
        objNetwork.RemoveNetworkDrive "Q:"   '************ This is the line where the error appears ***********
        Exit Sub
    End If
End If

    old_RST.Close
    Set old_RST = Nothing
    Db.Close

    'Re-enables the "X" button to close out of Microsoft Access
    CurrentState = SetCloseBox(True)
    
    Set objNetwork = CreateObject("WScript.Network")
    Set filesys = CreateObject("Scripting.FileSystemObject")

    objNetwork.RemoveNetworkDrive "Q:"

End Sub

Open in new window

jmccloskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
It's probably because your application/script "holds" a folder on the drive Q:.

Try to make your script change drive and/or directory to a local folder before exiting.

/gustav
0
jmccloskAuthor Commented:
Thanks for the response /gustav!

If I understand you correctly, the database could be holding on to one of the folders it copied to the backup location.  Would that be a correct assessment of your comment?

If this is the case, can you help me with syntax on how to change the "hold" of a folder from that to something local?  I don't know how to do that.

Thank you!
0
Gustav BrockCIOCommented:
It could be this line to modify for another copy using local folders:

   fc1 = CopyFolder(docPath, BackupDir & "\" & docFolder & " " & Format(Now(), "yy-mm-dd hh-mm"), True)

But you don't even create drive Q: here so why remove it?
Why can't you just use the full path: \\server\sharedfolder\backupfolder ?

/gustav
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jmccloskAuthor Commented:
Yes, I create Drive Q: when the application opens.  That is a Sub that runs first when the database is opened.  So, to be a good neighbor, I remove all my work when the end user closes the app.

I tried just using the SharePoint server path instead of a mapped network drive.  For whatever reason, my database did not recognize it and threw me all sorts of horrible errors.  

I work in a very large company, and I am not in the IT Dept.  I have no visibility to the SharePoint servers or how they are configured.  I'd love to just have their services turned on so I could offer this as a web site that runs completely on SharePoint!  But, that is not an option at this time.

I tried to put a statement in after the one you reference to say:  fc1.Close, but it did not like that at all.

Any other ideas?
0
Gustav BrockCIOCommented:
OK, but can't you run a script that removes the drive the same way it was created?

And try run a copy on a local drive before exiting.

/gustav
0
jmccloskAuthor Commented:
This is how I map the drive when I open the app.  Here is the code in the startup:

Dim objNetwork
Set objNetwork = CreateObject("WScript.Network")
Set filesys = CreateObject("Scripting.FileSystemObject")

Dim CurrentState As Long
  CurrentState = SetCloseBox(False)

If (Dir("Q:*", vbDirectory) = "") Then

    objNetwork.MapNetworkDrive "Q:", "\\teamsna6.jnj.com\CPC\DistTech\Shared Documents\New Ticket List\Database"  'mapping to Sharepoint server
End If

Open in new window


So, you're saying create a dummy file locally and copy it just to fake out Access?
0
jmccloskAuthor Commented:
Update:  So, I tried to enter another CopyFile command after the original one that copied a dummy file from one location to another on the local drive after the first one.  But, I still got the same error.  

The second FileCopy did not force a release of the first one.

Any other ideas?
0
Gustav BrockCIOCommented:
Perhaps it just hasn't finished the copying. Try inserting a messagebox and wait for a while:

    'Re-enables the "X" button to close out of Microsoft Access
    CurrentState = SetCloseBox(True)
    MsgBox "Wait for drive Q: to relax ..."    
    Set objNetwork = CreateObject("WScript.Network")
    Set filesys = CreateObject("Scripting.FileSystemObject")
    objNetwork.RemoveNetworkDrive "Q:"

/gustav
0
jmccloskAuthor Commented:
Hi /gustav

So, I tried adding in the message box and waited a full two minutes.  I still got the same error.  Is there no way to check, using VBA, to see if there are any file or folder locks on a share and/or release them?  It seems like we are trying to troubleshoot a problem that is a bit of a ghost.  I mean, I am pretty sure we know why the error is happening, but it does not seem like we are addressing the problem (release the file/folder lock), but instead just trying to alleviate, or work around the symptoms.  Your thoughts?

Thanks!
0
Gustav BrockCIOCommented:
Try to call:

    Debug.Print CurDir

before you try to close drive Q:.
If it reports using a folder on Q:, call this:

    ChDrive "C:"
    Debug.Print CurDir

It should now return "C:" as drive.
Then try to close drive Q:.

/gustav
0
jmccloskAuthor Commented:
The Debug.Print statements don't give anything.  I am wondering if this is because the application is in the process of closing?  In any case, I tried the ChDrive command, but it had no effect.  I still get the same error.
0
Gustav BrockCIOCommented:
Closing?
Then try to call the commands at an earlier stage. Or move the whole operation from the OnClose event of the form to then OnUnload event.

/gustav
0
jmccloskAuthor Commented:
Ok, so I changed the event to OnUnload as you said and when I run it the first Debug.Print comes up with the sharepoint path (which is the mapped network drive "Q:").  The second Debug.Print comes up as "C:\".  So, it looks like the ChDrive "C:" command is working.

However, the bad news is it still errors out on the line:

objNetwork.RemoveNetworkDrvie "Q:"

Is there a way to disconnect from the back end tables I have stored on the Q:\ drive without "unlinking" them?
0
Gustav BrockCIOCommented:
So the process tries to remove its own feets ...

The only way I can think of, is to move the process out of the current Access session - for example by calling a script that opens a second instance of Access and performs the backup.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmccloskAuthor Commented:
So, my apologies for leaving this for a couple days.  It is going to take me some time to develop what you suggest.  Based on the conversation we had, it seems the most likely method to move forward.  However I cannot say, at this time, that I have created a process based on this idea that does work.
0
Gustav BrockCIOCommented:
OK. Difficult stuff - it will probably take some attempts before you succeed.

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.