CODEWITHSUNDEEP
ms-accessvbams-access-2013ms-access-2016
loltospoon
loltospoon

Reputation: 239

MS Access: DB is crashing when trying to delete a spreadsheet

I have a button that exports a file, but first it checks to see if that file exists. If it does, then it deletes the file and writes a new one in its place. The problem is that clicking this button immediately crashes the database. Any ideas why? This problem only recently showed up - it was working fine before on both Acess 2013 and Access 2016. The environment is a business setting (company laptops, not personal ones) where a majority of anything we do is managed by an I.T. admin.

The code:

Private Sub Command370_Click()

    Dim myQueryName As String
    Dim myExportFileName As String

    myQueryName = "qry_SAP_FGCheck"
    myExportFileName = "J:\2017\SAP\SAPExports\DailyFGCheck_Export.xlsx"
    If Len(myExportFileName) > 0 Then
        On Error GoTo Err_Msg
        Kill myExportFileName
    End If
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, myQueryName, myExportFileName
    Application.FollowHyperlink myExportFileName

Err_Msg: If (Err.Number = 70) Then MsgBox "Error: (" & Err.Number & ")" & Err.description & ". You must close the spreadsheet in order to export.", vbOKOnly Else Resume Next


End Sub

Upvotes: 1

Views: 59

Answers (1)

Parfait
Parfait

Reputation: 107687

Possibly there is no such directory on the machine called in button click event. You use a drive letter J:\ suggested a user-defined mapped drive path to a network directory. Users may have mapped to different drive letters. Try the full UNC which can be found at cmd line net use:

myExportFileName = "\\SomeNetwork\Path\2017\SAP\SAPExports\DailyFGCheck_Export.xlsx"

In fact, right now the file is always deleted since you are checking the length of VBA string (which is always nonzero) as you assign it just before and not actual file system object.

Consider conditioning if such a directory file path exists before deletion or export:

If Len(Dir(myExportFileName, vbDirectory)) > 0 Then
    On Error GoTo Err_Msg
    Kill myExportFileName

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
                              myQueryName, myExportFileName
    Application.FollowHyperlink myExportFileName
End If

Even consider saving to the current path of database so it does not matter what directories are available to the user and even avoids overwriting other users' exports. 

myExportFileName = Application.CurrentProject.Path & "\DailyFGCheck_Export.xlsx"

If Len(Dir(myExportFileName, vbDirectory)) > 0 Then
    On Error GoTo Err_Msg
    Kill myExportFileName

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
                              myQueryName, myExportFileName
    Application.FollowHyperlink myExportFileName
End If

And ideally each user is using his/her own frontend on local CPUs (i.e, C:\ drives).

Upvotes: 2

Related Questions