Excel data link to Access table - VBA refresh not showing the 'table not found' error message

In Excel 2003 VBA ... I am refreshing a link to an MSAccess 2000 table using:      

  Private Sub workbook_open()
  On Error GoTo Workbook_Open_ERROR
   
      Application.ActiveWorkbook.RefreshAll
      Exit Sub

  Workbook_Open_ERROR:
      MsgBox "Workbook Open: " & Err.Number & " " & Err.Description
  End Sub


Problem:  If the table is not found I want an error to display but none is given.
                  ------------------------------------------------------------------------------------------

If I refresh manually (via right click, Refresh) I do get the error message i'm expecting...
Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'TEMP_CSHS Summ by Location by Month v1c'. Make sure it exists and that its name is spelled correctly.

How do I show the error message from the VBA refresh?
tjchuntAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Neil FlemingConsultant and developerCommented:
weird. your code works fine for me, at least in Excel 2007.

Probably a dumb question,but you DO have the subroutine in the module  "ThisWorkbook", rather than in Module 1, or wherever?
0
tjchuntAuthor Commented:
Hi and thanks for the comment.

The code does work for me.  My problem is when the underlying table/query is not found (for whatever reason) I need Excel to show the user an error message but it doesn't.  

If I forcefully delete the linked table and run the code... no error.  but when I manually refresh the data link via right click Refresh I get the error message I am expecting.  

How do I get Excel to give me an error from the code when the link is broken?
0
Neil FlemingConsultant and developerCommented:
..that's what I mean. In Excel 2007, the error message DOES display from the code on workbook open.

Have you tried putting a breakpoint in the code to ensure it is actually running when you open the workbook?
0
tjchuntAuthor Commented:
Yes it's running.  Perhaps there's  warning suppression i'm missig.  I'm getting all other warnings / erorrs though which is odd.
0
Neil FlemingConsultant and developerCommented:
OK, I'm wondering if it's a sequencing thing -- that the workbook doesn't recognize the link exists at the point where it is being opened.

Try moving the refreshall command to the Workbook.SheetActivate event for sheet 1? Does that help?
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error GoTo errortrap
    
     If Sh.Index = 1 Then
      Application.ActiveWorkbook.RefreshAll
     End If
      Exit Sub

errortrap:
      MsgBox "Refresh problem: " & Err.Number & " " & Err.Description

End Sub

Open in new window

0

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
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.