Spurious errors when defining and using Access links to Excel spreadsheets

Posted on 2013-09-18
Medium Priority
Last Modified: 2013-09-27
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
Question by:kiwi_731
  • 2
  • 2
LVL 74

Accepted Solution

Jeffrey Coachman earned 1500 total points
ID: 39502959
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.


Expert Comment

ID: 39514303
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.


Author Comment

ID: 39514505
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
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 1500 total points
ID: 39515008
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)


Author Closing Comment

ID: 39527112
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

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question