Network Connection Issue?

[LeAnn]

Hi,

I have an Access 2003 and I use VBA to process multiple files on the network
- perhaps as many as 20 in a session (sometimes importing, sometimes
converting text files into Excel files, sometimes exporting). Each file
contains anywhere from 256 to 4000+ records each. The current version is a
stand alone .mdb on a network drive. It is only used from one PC station. My
question is, is there a way to check for network connectivity during the
processing? There have been many times that the database stops processing
files and thus the "Please Wait.." form that displays stays displayed - for
hours and hours until they get tired of waiting and finally call me! Access
itself has not stopped responding that I am aware of. I disabled the On
Error handling to see if an error would display but it doesn't. I'm only
guessing it is a network connection problem because the area this PC is in
has had other network reliability issues.

This database is in revision now and will be split into a Front End/Back End
design. Any suggestions on ways to handle this? I have not been able to
reproduce the problem.

Thanks
LeAnn

 

[Brian]

Try something like this - a simple test to see if the target file exists.
This assumes you are already running an Access app that needs to access a
file name AccessDB.mdb at the path below via mapped drive R. The same could
apply to Excel or other files opened via VBA.

Dim FileName as String
FileName = R:\Path\Folder\AccessDB.mdb
if len(dir(filename)) = 0 then
MsgBox "No file found". 'or log an error, etc.
Exit sub
end if

 

[LeAnn]

Hi Brian,

I think I already have something like that but perhaps it needs a little
modification. Are you saying I should check for the database file? Here's a
snippet of what I'm doing.

strFile = Dir(UDSCDIR)

If strFile = "" Then
MsgBox "There are no files in the directory.", vbOKOnly + vbExclamation,
"No files"
Exit Sub
End If

'Open each file and copy range into Excel template
Do Until strFile = ""

'Open excel
Set objXL = CreateObject("Excel.Application")
objXL.Workbooks.OpenText FileName:=UDSCDIR & strFile, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1,
2), _
TrailingMinusNumbers:=True

'Do a whole lot of things with each file

Loop

 

[Brian]

Yes. That was the gist of my first step in isolating the problem.
Intermittent network problems can be very difficult to diagnose.

If the file names are pre-defined (i.e. IF you are looking for specific
files rather than just processing whatever files are in the folder), try
doing a Dir specifically for each file immediately before you open it.

In conjuction with this, try adding code to give you and/or your users an
indication of what the program is doing at any given time as well as the last
action succssfully completed. I would try one of two things (or both):

1. Set the status bar text to show the specific action and/or file being
processed. This may give the user a more specific idea of what is happening
when the program hangs.

Dim txtStatus as Variant
Dim procCurrent as String
Dim FileName as String
Dim PathName as String

'time to open an Excel file; repeat for each file
procCurrent = "Processing Excel file"
FileName = "MyExcelFile.xls"
PathName = "F:\ExcelFiles"
txtStatus = SysCmd(acSysCmdSetStatus, procCurrent & ": " & PathName & "\" &
FileName)

When done, be sure to do this to clear the status bar so it does not appear
to be stuck on the last action:

txtStatus = SysCmd(acSysCmdClearStatus)

2. Add a table to record events as they happen. Append a record immediately
after processing each iteration of a loop or at key code points. Include the
date, time, and notation identifying what happened last. Then, if the program
gets hung and the user has to close it down, you can look at the last event
in the log to see the last successful event and infer at what point it hung.

 

[LeAnn]

Hi Brian,

Both great ideas. I will ponder them over. If I create my own log table, I
will need to purge it regularly to keep the size from growing exponentially -
easy enough.

Thanks for the ideas!

 

[Brian]

Hopefully, you won't need the log table permanently - just until you diagnose
& repair the specific issue that plagues the system.

Having said that, I usually build in a 30-day auto-purge so that when some
main form is opened, it silently runs a query that deletes all the log
entries over 30 days old.