We help IT Professionals succeed at work.
Get Started

Access VBA: How do I verify a network folder connection before importing files from it.

Jay Williams
Jay Williams asked
on
127 Views
Last Modified: 2016-02-11
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 Database

Public 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_REPORT
End Sub

Public 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 Sub
Public 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 Sub

Public 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 Sub

Public 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 Sub


Public 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 Sub

Public 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 Sub
    
Public 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 Sub

Public 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

Open in new window


Any insight or advice you may have would be greatly appreciated.  Thanks.
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 3 Answers and 8 Comments.
See Answers
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