Access with 1 MySQL Table asking for credentials

Question

I have a access database using a local sql server backend for all tables except 1 web based MySQL table. The MySQL table has 50 rows or so, 3 fields, not big at all. I have a odbc connection setup and the table is linked with the password saved. This table is updated 30 times per day at most... Sometimes the connection breaks and the MySQL connection popup will appear... clicking test will result in a success, and clicking ok will allow the code to proceed. It is doing a 1 line update (SET LastUpdatedDate = #" & now() & "# WHERE ItemID = 'xyz').

I want to capture an error, or get it to continue without the connection if it is unavailable... but it appears no error is generated. I would rather not update the table when this happens, then have to physically select ok to get it running again. This problem exists from multiple locations, on multiple PCs around the US. I assume it is the server the MySQL db is hosted on that is having problems - I just want to know how to ignore them and move on with the other code... again, no error generated (So On Error ... won't work). Any Ideas? Using Access 2016.

UPDATE: My current setup is to ping the server... and if the ping gets a response, I assume it is up... then I run 'CurrentDb.Execute "UPDATE XYZ SET ABC = 'DEF' WHERE GHI = 'JKL'". That simple. If I try to query the table XYZ and it isn't available, I get the same connection popup. How should I go about refreshing the table? Delete the link and recreate?

NEW UPDATE Finally got around to try out the DSN-less pass through query proposed by Andre below. When I get to the 'execute' step I get an error saying I cannot execute a select query... but it is an update query. Here is the SQL string... .SQL = "UPDATE [Status] SET ItemDate = NOW() WHERE PlantID = '" & PlantID & "' AND ItemID = '" & ItemID & "'"

Rapture

Honestly i would alter the code to refresh the link on each execution. this would force it to refresh and then you wouldnt see the error. – Furthermore

post your current code. There are several ways to fix this but you need to show us what you have so far. – Envenom

Did you try the Pass-Through query? – Chromatograph

Answer 1

I suggest that instead of running an Access query on the linked table, you use a DSN-less Pass-Through query that you create on the fly.

This should either always work, or raise a trappable error.

Const ConnectString = "ODBC;DRIVER={MySQL ODBC 5.1 Driver};SERVER=your.server.com;PORT=3306;DATABASE=mydatabase;UID=myuserid;PWD=mypassword"

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.CreateQueryDef("")
With qdf
    ' Setting .Connect turns it into a Pass-Through query
    .Connect = ConnectString
    ' Need to set this for non-SELECT queries
    .ReturnsRecords = False
    ' Note: you need to use MySql syntax here, not Access SQL, especially the correct date format
    .SQL = "UPDATE XYZ SET ABC = 'DEF' WHERE GHI = 'JKL'"
    ' or since MySql has a NOW() function too, just this:
    .SQL = "UPDATE foo SET LastUpdatedDate = NOW() WHERE ItemID = 'xyz'"
    .Execute
End With

You can also try a saved Pass-Through query, it might work as well. Then you would only need to supply the current .Sql, not the connect string.

Chromatograph

Sorry for the delay - finally getting around to try this out. When I get to the 'execute' step I get an error saying I cannot execute a select query... but it is an update query. .SQL = "UPDATE [Status] SET ItemDate = NOW() WHERE PlantID = '" & PlantID & "' AND ItemID = '" & ItemID & "'" – Rapture

You need to add a line, see edit. learn.microsoft.com/en-us/office/client-developer/access/… – Chromatograph

Awesome! That is the line I needed. I have added an error handler to this operation now and should be able to trap any errors that pop up. I really appreciate it! – Rapture

Answer 2

Maybe you will get an helpful error if you execute your SQL command with the option dbFailOnError ? Like CurrentDB.Execute("Your SQL", dbFailOnError)

Pomfret

I have not tried that... I will add that and see what happens... unpredictable for when an outage will be, so it may be a while before I can tell if it worked. – Rapture

Nope - didn't help any - added it to two different pcs and both have since locked up on the connection string. Any other ideas? – Rapture

Did you look in the Connector/ODBC settings of your MySql connection? There is an option 'Enable automatic reconnect' - it's shown here dev.mysql.com/doc/connector-odbc/en/… – Pomfret

Tried it overnight (also selected 'Don't prompt when reconnecting' and neither (nor the combination) worked. Thanks! – Rapture

@Rapture what do you mean it didn't work? Do you still see the connection popup after enabling automatic_reconect? – Envenom

Correct - still had the connection popup after enabling automatic_reconnect. – Rapture

Answer 3

Before trying the UPDATE, do a simple SELECT. If it does not return a reasonable result, assume that the connection is down.

Britisher