Access VBA: How do I verify a network folder connection before importing files from it.
I have an Access program that (among other things) imports Excel files from various network locations. It seems like the code outruns the network connection because it sometimes throws an error saying the object cannot be found, but then runs right over the error when I restart the procedure manually.
What can I do before the import that will give the network time to establish the connection, if that is indeed the problem? Do you think there may be another issue? If so, how would I best work around it? For now, the problem is just an inconvenience for me because I know how to manually continue the run, but at some point I do need to be able to turn the program over to people who may not know what to do when this happens.
Here is all of my import code, if will help, but I think my question is more generic than code specific.
Option Compare DatabasePublic Sub ImportData() DoCmd.SetWarnings False Call MB25 Call MB52Inventory Call PKMC Call WHtrans DoCmd.SetWarnings False DoCmd.RunSQL "DELETE * FROM MaSAT" Call AX01_MASA_REPORT Call AX02_MASA_REPORT Call HX01_MASA_REPORT Call JL01_MASA_REPORTEnd SubPublic Sub MB25() Dim sPath As String, Src1 As String, Irc1 As String sPath = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\" Src1 = sPath & "MB25.XLSX" Irc1 = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\MB25.XLSX" Dim xlObj As Object Set xlObj = CreateObject("excel.application") xlObj.Workbooks.Open Irc1 xlObj.DisplayAlerts = False xlObj.ActiveWorkbook.SaveAs Src1, FileFormat:=51 xlObj.Quit Set xlObj = Nothing DoCmd.SetWarnings False DoCmd.DeleteObject acTable, "MB25T" DoCmd.TransferSpreadsheet acImport, 10, "MB25T", Src1, True, "" DoCmd.DeleteObject acTable, "Sheet1$_ImportErrors" DoCmd.SetWarnings True End SubPublic Sub MB52Inventory() Dim sPath As String, Src1 As String sPath = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\" Src1 = sPath & "MB52Inventory.XLSX" Irc1 = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\MB52Inventory.XLSX" Dim xlObj As Object Set xlObj = CreateObject("excel.application") xlObj.Workbooks.Open Irc1 xlObj.DisplayAlerts = False xlObj.ActiveWorkbook.SaveAs Src1, FileFormat:=51 xlObj.Quit Set xlObj = Nothing DoCmd.SetWarnings False DoCmd.DeleteObject acTable, "MB52InventoryT" DoCmd.TransferSpreadsheet acImport, 10, "MB52InventoryT", Src1, True, "" DoCmd.DeleteObject acTable, "Sheet1$_ImportErrors" DoCmd.SetWarnings True End SubPublic Sub PKMC() Dim sPath As String, Src1 As String sPath = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\" Src1 = sPath & "PKMC.XLSX" Irc1 = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\PKMC.XLSX" Dim xlObj As Object Set xlObj = CreateObject("excel.application") xlObj.Workbooks.Open Irc1 xlObj.DisplayAlerts = False xlObj.ActiveWorkbook.SaveAs Src1, FileFormat:=51 xlObj.Quit Set xlObj = Nothing DoCmd.SetWarnings False DoCmd.DeleteObject acTable, "PKMCT" DoCmd.TransferSpreadsheet acImport, 10, "PKMCT", Src1, True, "" 'DoCmd.DeleteObject acTable, "Sheet1$_ImportErrors" DoCmd.SetWarnings True End SubPublic Sub WHtrans() Dim sPath As String, Src1 As String sPath = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\" Src1 = sPath & "WHtrans.xlsx" Irc1 = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\WHtrans.xlsx""" Dim xlObj As Object Set xlObj = CreateObject("excel.application") xlObj.Workbooks.Open Irc1 xlObj.DisplayAlerts = False xlObj.ActiveWorkbook.SaveAs Src1, FileFormat:=51 xlObj.Quit Set xlObj = Nothing DoCmd.SetWarnings False DoCmd.DeleteObject acTable, "WHtransT" DoCmd.TransferSpreadsheet acImport, 10, "WHtransT", Src1, True, "" 'DoCmd.DeleteObject acTable, "Sheet1$_ImportErrors" DoCmd.SetWarnings True End SubPublic Sub AX01_MASA_REPORT() Dim sPath As String, Src1 As String, Src2 As String sPath = "\\Frxfileserv1\FTPDOWN\WW_PUBLIC\Download Folder\New Product Programs-MASA\Local\AX01" & "\" Src1 = sPath & "AX01_MASA_REPORT.XLS" Src2 = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\AX01_MASA_REPORT.XLS" Dim xlObj As Object Set xlObj = CreateObject("excel.application") xlObj.Workbooks.Open Src1 xlObj.Visible = True xlObj.DisplayAlerts = False xlObj.ActiveWorkbook.SaveAs Src2, FileFormat:=51 xlObj.Quit Set xlObj = Nothing DoCmd.SetWarnings False DoCmd.TransferSpreadsheet acImport, 10, "MaSAT", Src2, True, "" DoCmd.DeleteObject acTable, "AX01_MASA_REPORT$_ImportErrors" DoCmd.SetWarnings True End SubPublic Sub AX02_MASA_REPORT() Dim sPath As String, Src1 As String, Src2 As String sPath = "\\Frxfileserv1\FTPDOWN\WW_PUBLIC\Download Folder\New Product Programs-MASA\Local\AX02" & "\" Src1 = sPath & "AX02_MASA_REPORT.XLS" Src2 = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\AX02_MASA_REPORT.XLS" Dim xlObj As Object Set xlObj = CreateObject("excel.application") xlObj.Workbooks.Open Src1 xlObj.Visible = True xlObj.DisplayAlerts = False xlObj.ActiveWorkbook.SaveAs Src2, FileFormat:=51 xlObj.Quit Set xlObj = Nothing DoCmd.SetWarnings False DoCmd.TransferSpreadsheet acImport, 10, "MaSAT", Src2, True, "" DoCmd.DeleteObject acTable, "AX02_MASA_REPORT$_ImportErrors" DoCmd.SetWarnings True End SubPublic Sub HX01_MASA_REPORT() Dim sPath As String, Src1 As String, Src2 As String sPath = "\\Frxfileserv1\FTPDOWN\WW_PUBLIC\Download Folder\New Product Programs-MASA\Local\HX01" & "\" Src1 = sPath & "HX01_MASA_REPORT.XLS" Src2 = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\HX01_MASA_REPORT.XLS" Dim xlObj As Object Set xlObj = CreateObject("excel.application") xlObj.Workbooks.Open Src1 xlObj.Visible = True xlObj.DisplayAlerts = False xlObj.ActiveWorkbook.SaveAs Src2, FileFormat:=51 xlObj.Quit Set xlObj = Nothing DoCmd.SetWarnings False DoCmd.TransferSpreadsheet acImport, 10, "MaSAT", Src2, True, "" DoCmd.DeleteObject acTable, "HX01_MASA_REPORT$_ImportErrors" DoCmd.SetWarnings True End SubPublic Sub JL01_MASA_REPORT() Dim sPath As String, Src1 As String, Src2 As String sPath = "\\Frxfileserv1\FTPDOWN\WW_PUBLIC\Download Folder\New Product Programs-MASA\Local\JL01" & "\" Src1 = sPath & "JL01_MASA_REPORT.XLS" Src2 = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\JL01_MASA_REPORT.XLS" Dim xlObj As Object Set xlObj = CreateObject("excel.application") xlObj.Workbooks.Open Src1 xlObj.Visible = True xlObj.DisplayAlerts = False xlObj.ActiveWorkbook.SaveAs Src2, FileFormat:=51 xlObj.Quit Set xlObj = Nothing DoCmd.SetWarnings False DoCmd.TransferSpreadsheet acImport, 10, "MaSAT", Src2, True, "" DoCmd.DeleteObject acTable, "JL01_MASA_REPORT$_ImportErrors" DoCmd.SetWarnings True End Sub
Thanks. I didn't know about the FileCopy method, so as you can see, having to open each one individually and save it in a friendlier format anyway, I just open them where they sit and save them to the new location. I'll give it a whirl. Thanks again Scott.
Looks like there's more than one way to skin a cat. On the face of it, I like this idea because I could check each directory in one procedure and not have to edit four procedures. On the downside, I have to learn how to do what you suggest; it's new ground for me.
Scott McDaniel (EE MVE )
One other downside: You're still importing across a network connection, and part of your import process involves opening the file in Excel, saving it in a new format, etc etc - those processes take time, and they can take a lot longer across a network, which means you have more opportunities for troubles. Copying a file across a network is generally fairly quick (depending on several factors, of course), and then working with a local copy can mean significant performance gains (and also no chance of dropping a connection in the middle of an operation).
Sure - and implementing BOTH suggestions would be the best way to handle this (at least in my opinion).
Jay Williams
ASKER
I'm going to take both sets of advice. First thing I'm going to issue Dir() commands to nudge the network folders, and whether they error or not, move on to the local imports. That should give the connections time to refresh before starting the network procedures.