2

On Excel I have a table that uses MySQL Connector/ODBC to get external data from a database.

If the connection fails when I refresh it the MySQL Connector/ODBC Data Source Configuration window appears.

MySQL Connector/ODBC Data Source Configuration

Instead of that I want to present the user with a custom error message.

Checking if the refresh was successful after refreshing is too late to prevent the window so I ping the server before refreshing and cancel the refresh if the ping fails.

My question is if there's a more reliable/standard way to not show the window when there's no connection. I don't want to write the password on my code for security reasons (the connection is made with a DSN) and I don't know if the ping trick will always work or if it might rarely give unexpected errors or results.

Also I think it's technically or theoretically possible that the connection status changes between the ping check and the refresh even though that wouldn't be the end of the world (if that was even possible between the fast running code).

Code example for anyone asking:

Sub Table()
  Sheets(1).ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=connection_test;", Destination:=Range("A1")).QueryTable.CommandText = Array("SELECT table_test_0.`column _test`  FROM db_test.table_test table_test_0")
End Sub

Sub Refresh()
  Sheets(1).ListObjects(1).QueryTable.Refresh
End Sub
Community
  • 1
  • 1
user7393973
  • 2,270
  • 1
  • 20
  • 58
  • @PortlandRunner I forgot to mention that. It will kind of do the same as `Success` in the AfterRefresh event. It catches but doesn't prevent the window. – user7393973 Mar 22 '18 at 20:15
  • 2
    One other thought. Try turning off alerts, `Application.DisplayAlerts = False` and then I believe it will throw a connection error so you can use the `On Error` to catch it and display a message or something similar to notify user that the table didn't refresh. – Automate This Mar 22 '18 at 21:03
  • @PortlandRunner Not the perfect solution I wanted but probably the best there is or that I will get. Well thought. – user7393973 Mar 22 '18 at 22:45

0 Answers0