MS Access's user interface to interact with data from a MySQL database?

[cenr-renauds]

Hi,
On Windows 7, I must connect a MS Access client (.accdb), where all the forms, VBA scripts, macro, and reports are, to a MySQL Database instead of a MS Access database (.mdb). Here what I have tried so far.
I created a connection with MySQL Connector /ODBC 5.3 Unicode driver (https://dev.mysql.com/downloads/connector/odbc/5.3.html) to the MySQL database with success.
After in MS Access 2013, I created a new database (.mdb) by linking to my local machine ODBC Connector. I can see the data in each table I have linked.
However, I didn’t find where to change the connection’s setup of the MS Access client (.accdb) so it uses the data from the new .mdb file linked to the MySQL database. Where do we do that?
Is there another, maybe easier way to use MS Access's user interface to interact with data from a MySQL database?


Best Regards,
Sébastien

[pbaldy]

I'm confused (not unusual). You are trying to link an accdb to an mdb which is in turn linked to MySQL? What's the purpose of the mdb? If the forms and such are in the accdb, it should contain the tables linked to MySQL.

[cenr-renauds]

Hi,
On Windows 7, I must connect a MS Access client (.accdb), where all the forms, VBA scripts, macro, and reports are, to a MySQL Database instead of a MS Access database (.mdb). Here what I have tried so far.
I created a connection with MySQL Connector /ODBC 5.3 Unicode driver (https://dev.mysql.com/downloads/connector/odbc/5.3.html) to the MySQL database with success.
After in MS Access 2013, I created a new database (.mdb) by linking to my local machine ODBC Connector. I can see the data in each table I have linked.
However, I didn’t find where to change the connection’s setup of the MS Access client (.accdb) so it uses the data from the new .mdb file linked to the MySQL database. Where do we do that?
Is there another, maybe easier way to use MS Access's user interface to interact with data from a MySQL database?
Best Regards,
Sébastien

Actually, I forgot to mention that I had already tried to link the .accdb to MySQL by linking to my local machine ODBC Connector. In that case, the .accdb's forms kept trying to connect to the previous .mdb database. Where can I change this configuration? Thanks!

[pbaldy]

The firm's source is set in its Record Source property. Try changing that to the directly linked table.

[June7]

Cross post https://stackoverflow.com/questions/...mysql-database

[cenr-renauds]

The MySQL database is the converted .mdb database. All the tables and their fields are the same.
In the forms and the report, the record sources are as previously. However, the accdb still try to open and access the .mdb database for some reason. Can it be a cached data in memory? Thanks again!

[cenr-renauds]

Different platforms, different people... Is there a problem in posting our question on different forums? If it's the case, I'm sorry, I didn't know. Thanks!

[pbaldy]

You need to find where it's referring to the mdb. Could be a linked table, somewhere in code, maybe a VBA reference. How do you know it's trying to open the mdb? If you delete/move the mdb, what kind of error do you get? That might lead to where the issue is.

Here are thoughts on cross posting:

cross posters

[cenr-renauds]

Hi, I have redone all the procedure to connect the Access UI .accdb to the MySQL database via connector ODBC:
https://dev.mysql.com/doc/connector-...ed-tables.html
It didn't work before I restarted Access. After, it worked... Maybe there is a cached activated on my Access's system? Thanks for your help!
Also thank you for the info about cross posting.

[pbaldy]

I have heard of Access using cached connections before. Glad you got it working.

[cenr-renauds]

Yes! And how do we mark this post as resolved on this forum?

[pbaldy]

With the Thread Tools dropdown at the top of the thread.

[cenr-renauds]

Ok! Thanks again!

[pbaldy]

No problemo!