Results 1 to 5 of 5
  1. #1
    Yann63 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    13

    How to make a persistent ODBC connection MySQL to Access

    Hello

    I have connected a MySQL database on a webserver to my Access 2013 database.
    using following parameters

    [ODBC]
    DRIVER=MySQL ODBC 5.2 Unicode Driver
    Server=


    Port=
    User=
    Password=
    Database=


    Connection can be made, but is lost after a few seconds.
    In the Access application a screen is used to consult/adapt the MySQL database data in Access. Following occurs : open the screen - no problem connection is made. But after a few seconds system mentions "ODBC - call failed. Lost connection to MySQL server during query (#2013)".

    How can I solve this problem and make the ODBC connection persistent?

    Thank you in advance.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    when you get this error, have you tried to go to the table and open it? do you get the same error message?

    Is there a communication issue between the server the Access database resides on and the server the MYSQL database resides on?

    Typically when there is even a brief interruption MS Access is not great about re-establishing that connection, meaning you'd have to close the Access db and re-open it.

    Have you tried linking the tables directly into your MS Access database rather than try to do it through a connection string? (I'm assuming you're currently running some sort of VBA code using a connection string rather than having the table linked but that's a guess).

    Do you get this error message even if you do nothing, for instance open your form then just sit and wait, will this error message pop up?

    are the ms access database and the mysql database on the same server?

  3. #3
    Yann63 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    13
    Quote Originally Posted by rpeare View Post
    when you get this error, have you tried to go to the table and open it? do you get the same error message?

    Is there a communication issue between the server the Access database resides on and the server the MYSQL database resides on?

    Typically when there is even a brief interruption MS Access is not great about re-establishing that connection, meaning you'd have to close the Access db and re-open it.

    Have you tried linking the tables directly into your MS Access database rather than try to do it through a connection string? (I'm assuming you're currently running some sort of VBA code using a connection string rather than having the table linked but that's a guess).

    Do you get this error message even if you do nothing, for instance open your form then just sit and wait, will this error message pop up?

    are the ms access database and the mysql database on the same server?
    Hello,
    Thanks for your reply.
    What do you mean by "Is there a communication issue between the server the Access database resides on and the server the MYSQL database resides on?". Can you clarify please.
    The MySQL tables are linked directly into MS Access, so connection is not made via VBA connection string.

    Indeed, I get the error also by doing nothing, just wait and error message pops up.
    The Access database is on a desktop, the MySQL database on a remote webserver.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Here's the issue (I've had it before), there is something going on between your webserver and your access database (they are on two different machines), and even short interruptions in communication will stop your Access database from working without closing the database and re-opening it. MS access is not good about re-aquiring linked tables if there's an interruption in communication. This isn't an Access problem it is an issue between your web server and your computer hosting the MS Access database. If you have someone that's able to monitor the traffic between the two computers and verify there's a problem you may be able to track down which machine it is and fix it from there.

    If the problem happens every few minutes (as it was with me) finding this problem is really the only solution.

    If the problem happens, say, every few hours, you can build an event into your database to re-establish the links so you don't have to exit and come back into the database.

  5. #5
    Yann63 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    13

    Smile

    Quote Originally Posted by rpeare View Post
    Here's the issue (I've had it before), there is something going on between your webserver and your access database (they are on two different machines), and even short interruptions in communication will stop your Access database from working without closing the database and re-opening it. MS access is not good about re-aquiring linked tables if there's an interruption in communication. This isn't an Access problem it is an issue between your web server and your computer hosting the MS Access database. If you have someone that's able to monitor the traffic between the two computers and verify there's a problem you may be able to track down which machine it is and fix it from there.

    If the problem happens every few minutes (as it was with me) finding this problem is really the only solution.

    If the problem happens, say, every few hours, you can build an event into your database to re-establish the links so you don't have to exit and come back into the database.
    Thanks for the info. I will continue my search...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Odbc connection mysql 5.1
    By PATRICK in forum Programming
    Replies: 0
    Last Post: 03-07-2013, 05:53 PM
  2. Replies: 2
    Last Post: 09-19-2012, 12:01 AM
  3. Replies: 3
    Last Post: 01-06-2012, 04:17 PM
  4. Replies: 5
    Last Post: 10-25-2011, 08:01 AM
  5. Access data page connection to MySQL
    By dkperez in forum Access
    Replies: 1
    Last Post: 09-29-2009, 06:02 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums