Follow

How to connect to a MySQL data source ODBC

ODBC MySQL connection

Install and set up the provider

To use the odbc connection to MySQL you need to have a 64 bit ODBC provider. You can use MariaDB. Download and install it.

Open ODBC Data Sources Administrator (64-Bit) go to System DSN and press Add. Find the driver in the list and press Finish.

Give it a name and press next.

10.PNG

Add the server in the Server Name field, add 3306 in Port, add root in User name and its password in the Password field. When this is set up, press Test DSN. Now you can find all the databases the server contains by looking through the drop down menu.

11.PNG

Press next until it is done.

Add a MySQL ODBC data source.

To add it, right click on Data Sources, go to Data Sources, Application Specific ODBC and choose Add MySQL 5.1 Client data source.

12.PNG

In the menu choose the System DSN we just created.

13.PNG

Set the Escape Character to None and press OK.

14.PNG

Synchronize with the data source and see the fields. In this you will only get the tables related to the database you chose in the DSN setup. If you have more databases and want to connect to them as well, you need to create additional DSN providers for all of them.

Common issues with MySQL data

Date fields smaller than 1753-01-01

In MySQL the smallest allowed date is 0001-01-01 in MSSQL that is not the case for datetime fields.

If that is the case you will get the following error message.

8.PNG

To fix this you can use the override data type feature: Using the Override Data Type feature.

In this case you would use the General Rule where DateTime fields are converted to DateTime2 fields. Remember that it differentiates between upper case and lower case.

9.PNG

Apply the override and synchronize with the data source. You should get a notification about which fields were affected by the change.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.