• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 832
  • Last Modified:

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?
0
tjchunt
Asked:
tjchunt
  • 3
  • 2
1 Solution
 
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
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

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now