Locks in MySQL

[dfeller]

I have a MySQL database hosted on a webserver. We are using the MySql odbc driver to link to the tables and this, so far is working well. (The link is over the internet.)

The problem that has come up is that I am trying to issue an update query from Microsoft Access to update the records in one of the tables.

The Microsoft Access database just hangs when running the update query. I assume it's because a record is in use on the web server. I tested this by updating different records via individual UPDATE statements and it will update the majority of them but hang on some.

If we could, I would like to just skip past the ones that are locked for updating and move on to the next record. This process runs every day so if it's not caught one day it will be caught the next.

However I can't see any way in Access to tell it to skip a locked record.

Any advice?

Dawn

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[kjv1611]

You might could look at the conditional IIF statement, though I'm not sure what you would tell Access to look for. The record locking is definitely happening on the source of record (MySql in this instance).

If it were done via a recordset rather than a straight query, then you might could have a little more control over it, but recordset usage tends to be MUCH slower than straight queries, so that not give you what you want either.

That is an interesting dilemma. If I get a chance, I'll think about it some more today, and post back if I have any ideas.