We help IT Professionals succeed at work.
Get Started

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

2,285 Views
Last Modified: 2016-02-10
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

Comment
Watch Question
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This problem has been solved!
Unlock 1 Answer and 16 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE