Spurious errors when defining and using Access links to Excel spreadsheets
Posted on 2013-09-18
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;DATABASE=" & strLocation
tbl.Connect = "Excel 8.0;HDR=NO;IMEX=2;DATABASE=" & strLocation
' if table is a sheet, suffix with "$", if a named range, use name as is
tbl.SourceTableName = strTableName ' strTableName passed as parameter
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.