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;DATABASE=" & strLocation
               tbl.Connect = "Excel 8.0;HDR=NO;IMEX=2;DATABASE=" & strLocation
            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
Mark DalleyInformation AnalystAsked:
Who is Participating?
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.

Jeffrey CoachmanMIS LiasonCommented:
Not sure, but it sounds like a timing issue.
Especially when using "Interoperability" coding, (like you see to have here), many things take time before all systems will be notified of any changes.

The problem with using code to "Wait", is that if you set it for 1 second, then inevitably  the code may need 3 seconds for another run.
So then you are stuck putting huge pauses in your code to try to cover the longest possible "wait" needed.

But if that works for you then roll with it...

But to be sure, ...lets see what other Experts may post.


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
What you might find is happening is that both lines of code are being executed at similar times, in VBS to run a command you can add ,TRUE to the end which tells VBS to wait for that command to finish before continuing. It doesn't look like there is any such thing within the code you are implementing.

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.

Mark DalleyInformation AnalystAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
My feeling is that the most important factor is that the code complets successfully.
So your goal should be to get code that works first, then work on the speed second.

Remember, ...you can do all the checking and waiting you like, and still encounter yet another snag.
And sometimes the very act of trying to workarond these snags, may cause even more issues.

Please know that it is the way in which this system is designed, that is the main culpret here.
Going from Outlook, to Excel, then to Access flawlessly, will never be a smooth, or quick process.
(I know that this is out of your control, ...just mentioning it)

Something else to consider:

You can update a database via email directly from Access (Access 2007 and newer)

Mark DalleyInformation AnalystAuthor Commented:
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
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.