Spurious errors when defining and using Access links to Excel spreadsheets
Hello experts
I need to explain some background before asking the actual question - please bear with me.
I recently found myself charged with programming an import from a large number of Excel spreadsheets containing timesheet data to an Access database. The timesheets were submitted every week via Outlook.
My approach was to save the email attachments that looked like spreadsheets, create an Access link to the saved file using DAO, and execute a standard query to pull in the data. Fair enough.
After extirpating the usual screed of bugs, I was left with a residual set of spreadsheets which (1) looked perfectly OK, but which either (2) threw an error when I tried to define the link to them, or (3) threw an error when I attempted to read their data ("invalid format" or similar). The weird thing was that every time I single-stepped through the process with the debugger, everything worked OK! Sometimes it just worked when you re-ran the process without the debugger - same spreadsheet!
The relevant bit of code was (edited):
dim tbl As DAO.TableDef
...
set db = CurrentDB()
...
Select Case strExt ' strExt is the extension of the file being linked to
...
Case ".xls", ".xlsx", ".xlsm" ' Connecting to an Excel spreadsheet
If blnHasHeader Then
tbl.Connect = "Excel 8.0;HDR=YES;IMEX=2;DATABAS
Else
tbl.Connect = "Excel 8.0;HDR=NO;IMEX=2;DATABASE
End If
' if table is a sheet, suffix with "$", if a named range, use name as is
tbl.SourceTableName = strTableName ' strTableName passed as parameter
...
End Select
db.TableDefs.Append tbl ' make link persistent ' stmt X*
db.TableDefs.Refresh ' update object model ' stmt Y*
I finally discovered that if I wait a second before stmt X* and another second between stmt X* and stmt Y*, and another second after stmt Y*, the problem does not occur. (I did this via a Pause routine using Timer and a loop with DoEvents).
OK, now for the questions: (a) Why is this necessary? (b) Is there anything in an object model which I can interrogate to find out when the process is complete? (c) Do I really need to wait in all the 3 places I mentioned, or are some of them superfluous?
I look forward to your enlightenment, and thanks in advance.
Hopeful Kiwi
I need to explain some background before asking the actual question - please bear with me.
I recently found myself charged with programming an import from a large number of Excel spreadsheets containing timesheet data to an Access database. The timesheets were submitted every week via Outlook.
My approach was to save the email attachments that looked like spreadsheets, create an Access link to the saved file using DAO, and execute a standard query to pull in the data. Fair enough.
After extirpating the usual screed of bugs, I was left with a residual set of spreadsheets which (1) looked perfectly OK, but which either (2) threw an error when I tried to define the link to them, or (3) threw an error when I attempted to read their data ("invalid format" or similar). The weird thing was that every time I single-stepped through the process with the debugger, everything worked OK! Sometimes it just worked when you re-ran the process without the debugger - same spreadsheet!
The relevant bit of code was (edited):
dim tbl As DAO.TableDef
...
set db = CurrentDB()
...
Select Case strExt ' strExt is the extension of the file being linked to
...
Case ".xls", ".xlsx", ".xlsm" ' Connecting to an Excel spreadsheet
If blnHasHeader Then
tbl.Connect = "Excel 8.0;HDR=YES;IMEX=2;DATABAS
Else
tbl.Connect = "Excel 8.0;HDR=NO;IMEX=2;DATABASE
End If
' if table is a sheet, suffix with "$", if a named range, use name as is
tbl.SourceTableName = strTableName ' strTableName passed as parameter
...
End Select
db.TableDefs.Append tbl ' make link persistent ' stmt X*
db.TableDefs.Refresh ' update object model ' stmt Y*
I finally discovered that if I wait a second before stmt X* and another second between stmt X* and stmt Y*, and another second after stmt Y*, the problem does not occur. (I did this via a Pause routine using Timer and a loop with DoEvents).
OK, now for the questions: (a) Why is this necessary? (b) Is there anything in an object model which I can interrogate to find out when the process is complete? (c) Do I really need to wait in all the 3 places I mentioned, or are some of them superfluous?
I look forward to your enlightenment, and thanks in advance.
Hopeful Kiwi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello Wayne / Jeff
Thanks for your replies.
First, 6-8 seconds is quite a lot when you want to process several hundred spreadsheets.
Second, adding ,TRUE to a statement to make it synchronous only applies to certain statements in VBScript - and very useful it is too.
Also, Jeff is right - sometimes the link fails even with the wait included, though not very often.
When I watch the critical statemets execute in the VBA debugger, there is often a bit of a pause before the statement returns - especially the db.TableDefs.Append (my stmt X*). I was assuming that it wasn't returning before it was complete. Of course Windows may want to 'optimise' things by returning early, but if it does so it ought to provide something in the object model (ideally an event, but a flag would suffice) to tell me when the process is *really* finished. My reason for posting was in the hope of hearing about something like this.
Failing that, another workaround does occur to me... Instead of waiting unconditionally, then ploughing ahead regardless, I could test the link in some way when creating it. If a certain kind of error occurs, wait for a second, then Resume (i.e. retry). If this fails after (say) 5 rounds, signal error.
Does anyone out there have any better ideas?
Hopeful Kiwi
Thanks for your replies.
First, 6-8 seconds is quite a lot when you want to process several hundred spreadsheets.
Second, adding ,TRUE to a statement to make it synchronous only applies to certain statements in VBScript - and very useful it is too.
Also, Jeff is right - sometimes the link fails even with the wait included, though not very often.
When I watch the critical statemets execute in the VBA debugger, there is often a bit of a pause before the statement returns - especially the db.TableDefs.Append (my stmt X*). I was assuming that it wasn't returning before it was complete. Of course Windows may want to 'optimise' things by returning early, but if it does so it ought to provide something in the object model (ideally an event, but a flag would suffice) to tell me when the process is *really* finished. My reason for posting was in the hope of hearing about something like this.
Failing that, another workaround does occur to me... Instead of waiting unconditionally, then ploughing ahead regardless, I could test the link in some way when creating it. If a certain kind of error occurs, wait for a second, then Resume (i.e. retry). If this fails after (say) 5 rounds, signal error.
Does anyone out there have any better ideas?
Hopeful Kiwi
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Am rating this as a B; however, this is due to the intractibility of the problem and no reflection on the answerer. If anyone wants to make a case to promote to an A, I am happy to consider it.
Hopeful Kiwi
Hopeful Kiwi
So it seems to me you've found the solution and after all what's 6-8 seconds within automation?
I would add the waits into the places you have found they're needed and put this one down to the lap of the IT Gods.
Wayne