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

i have button exports file, first checks see if file exists. if does, deletes file , writes new 1 in place. problem clicking button crashes database. ideas why? problem showed - working fine before on both acess 2013 , access 2016. environment business setting (company laptops, not personal ones) majority of managed i.t. admin.

the code:

private sub command370_click()      dim myqueryname string     dim myexportfilename string      myqueryname = "qry_sap_fgcheck"     myexportfilename = "j:\2017\sap\sapexports\dailyfgcheck_export.xlsx"     if len(myexportfilename) > 0         on error goto err_msg         kill myexportfilename     end if     docmd.transferspreadsheet acexport, acspreadsheettypeexcel12xml, myqueryname, myexportfilename     application.followhyperlink myexportfilename  err_msg: if (err.number = 70) msgbox "error: (" & err.number & ")" & err.description & ". must close spreadsheet in order export.", vbokonly else resume next   end sub 

possibly there no such directory on machine called in button click event. use drive letter j:\ suggested user-defined mapped drive path network directory. users may have mapped different drive letters. try full unc can found @ cmd line net use:

myexportfilename = "\\somenetwork\path\2017\sap\sapexports\dailyfgcheck_export.xlsx" 

in fact, right file deleted since checking length of vba string (which nonzero) assign before , not actual file system object.

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

if len(dir(myexportfilename, vbdirectory)) > 0     on error goto err_msg     kill myexportfilename      docmd.transferspreadsheet acexport, acspreadsheettypeexcel12xml, _                               myqueryname, myexportfilename     application.followhyperlink myexportfilename end if 

even consider saving current path of database not matter directories available user , avoids overwriting other users' exports.

myexportfilename = application.currentproject.path & "\dailyfgcheck_export.xlsx"  if len(dir(myexportfilename, vbdirectory)) > 0     on error goto err_msg     kill myexportfilename      docmd.transferspreadsheet acexport, acspreadsheettypeexcel12xml, _                               myqueryname, myexportfilename     application.followhyperlink myexportfilename end if 

and ideally each user using his/her own frontend on local cpus (i.e, c:\ drives).