The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
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.
Any insight or advice you may have would be greatly appreciated. Thanks.
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
Any insight or advice you may have would be greatly appreciated. Thanks.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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.
Seems like just trying a Dir() command with a re-try loop would be good. What I'm assuming is happening is you have a network connection that has timed out and has been disconnected. When you go to use it again, it takes a second or two to get it going.
I'd write a procedure, CheckNetworkConnectio()
That:
1. Issues a Dir() command
2. If no error, returns true and exits.
3. If an error, checks the retry limit. If the retry count has reached the limit, returns false and exits.
4. Increments the retry count, Issues a DoEvents(), waits a second, then goes back to 1.
I'd say no more than two or three retires would be needed.
Jim.
I'd write a procedure, CheckNetworkConnectio()
That:
1. Issues a Dir() command
2. If no error, returns true and exits.
3. If an error, checks the retry limit. If the retry count has reached the limit, returns false and exits.
4. Increments the retry count, Issues a DoEvents(), waits a second, then goes back to 1.
I'd say no more than two or three retires would be needed.
Jim.
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.
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).
Good points, but I don't we have to have a reliable connection before we do anything, regardless of what it is?
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
Sure - and implementing BOTH suggestions would be the best way to handle this (at least in my opinion).
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
FileCopy "source", "destination"
Then, use the file in the "destination" location instead of trying to pull across the network. So for example in this section:
Dim sPath As String, Src1 As String, Irc1 As String
sPath = "G:\XE_ECMs\__MOST_REPORT\
Src1 = sPath & "MB25.XLSX"
Irc1 = "G:\XE_ECMs\__MOST_REPORT\
Assuming Src1 would give you the full path, you could do this:
FileCopy Src1, "C:\SomeFolder\MBs5.XLSX"
Then "reset" the Src1 variable to point to the local copy:
Src1 = "C:\SomeFolder\MBs5.XLSX"