MySql and Access

[mihabaki]

Is there any other way to import or link data from MySql database (MS Access as front-end) then via MyODBC driver???

thx,
m.

 

[bboffin]

That's the only normal method. Why would you want to use anything different? If you don't want to install the ODBC driver on user PCs then try creating an Access DB on a common server that only contains the MySql linked tables and link the user applications to that DB. That way the ODBC drivers need only be installed on the server.

If that isn't satisfactoty the only other solutions that come to mind are to write an ActiveX control to do it for you or to write a 'Web Service' that you can access using explicit HttpRequest objects called from Access


Bob Boffin

 

[mihabaki]

OK. I'm asking because I'm having problems with MyODBC (3.51.12) - the connection keeps breaking up (after 1 minute or so)... Is there any other FREE ODBC driver then MyODBC ???

 

[bboffin]

If you're using Access 2000 or later you could code everything using ADO and connect to the MySql OleDB Provider instead but this might be a bit long winded if you're not used to it.

The MySql ODBC driver is normally quite reliable but it could be that Access uses it in a non standard way. For example by default Access queries work by retrieving the entire recordset and filtering it locally which is quite a heavy load and this may be the cause. In my experience even accessing SQL Server from Access via ODBC can give rise to a significant number of ODBC errors and cause database locks that have to be resolved manually.

Try using passthrough queries to see if that helps and turn on ODBC tracing to get some idea of what going on.



Bob Boffin

 

[mihabaki]

Thank you bboffin.

The problem was on the server side - "wait_timeout" was to short. I made it longer (1 hour) and now it works without any problems.

m.

 

[bboffin]

Glad you managed to resolve it.

Bob Boffin