Connecting to MYSQL server with VBA (cannot use ODBC)

[jinx_uk_98]

Hi there,

I want to create a database that connects to a MYSQL database to run some reports. Normally I would do this trough Get External data and use a ODBC connection.

The problem is this database is going to be saved to a shared folder where anyone can open and run the access reports at anytime. The users won't necessary have ODBC drivers installed.

Is there anyway I can code the connection settings and refresh tables instruction in VBA? or maybe there is a better solution entirely.

Basically my user need to run up to date reports from a MYSQL database, ideally using access as the report with have a field that converts some text to a barcode


Many thanks
Kev

 

[SydneyGeek]

You can do this using ADO, a generic database protocol.
You will need to use a DSN-less connection (so that ODBC driver is not required).
See if this page helps: MySQL connection strings - ConnectionStrings.com

Denis

 

[HiTechCoach]

Hi there,

I want to create a database that connects to a MYSQL database to run some reports. Normally I would do this trough Get External data and use a ODBC connection.

The problem is this database is going to be saved to a shared folder where anyone can open and run the access reports at anytime. The users won't necessary have ODBC drivers installed.

Is there anyway I can code the connection settings and refresh tables instruction in VBA? or maybe there is a better solution entirely.

Basically my user need to run up to date reports from a MYSQL database, ideally using access as the report with have a field that converts some text to a barcode


Many thanks
Kev

Kev,

Unfortunately what you want to do is probably not possible for several reason.

1) You should not share a database. Every user will need their own copy.

2) Every workstation that will open the database will need the ODBC drivers installed.


The mySQL ODBC drivers are what allow any software, including Access to talk to an ODBC compliant database.

Access will require the ODBC drivers to be installed. You can use a DNS-less connection so you do not have to set up an an ODBC connection in the Control Panel.

You may need to create a .NET web App.

@Dennis, you liked to the Connector/Net which is a fully-managed ADO.NET driver for MySQL. Unfortunately, Access can not use the .NET libraries. Access can use the standard ODBC Drivers.