Cannot connect MS Access 2013 to MariaDB

Topic

New Reply

MS Access 2013 (MS Office 365 Pro-Plus)
Windows 7 Enterprise SP1

MariaDB 10.0.19 loaded to a VM on a Linux server
MariaDB 1.0.5 ODBC connector

MySQL 5.5.21 loaded on Windows 2008 server
MySQL 5.2 Unicode ODBC Connector

I have one primary application using MS Access as a Front End to a MySQL backend.
I actually have several with single user, but one primary application with multiple users.
The server hosting MySQL is an old box scheduled to be decommissioned soon. It is a physical box located on-site.
The new server has MariaDB installed, and I need to migrate my FE apps to it.

I have been unable to successfully connect to MariaDB using the connection string below.
Also listed below is the connection string that I have successfully used for MySQL since 2011 for MySQL.

One side note, I have discovered during my research that (for MariaDB versions prior to 10.1.x) the optimizer_switch
default value for ‘engine_condition_pushdown’ is OFF, and should be set to ON. This has more to do with the WHERE
clause processing for SQL statements. I can’t even get a connection!

This is the full connection string I have used (password mocked here) for four years to MySQL as a DSN-less connector for pass-thru queries:
ODBC;DRIVER={MySQL ODBC 5.2w Driver};SERVER=qcyce1;DATABASE=master_repository;UID=root;PWD=mypassword;PORT=3306;
CONNECTION TIMEOUT=15;COLUMN_SIZE_S32=1;DFLT_BIGINT_BIND_STR=1;LANGUAGE=us_english;CHARSET=utf8mb4;AUTO_IS_NULL=1;FOUND_ROWS=1;

This is the MariaDB version I have tried unsuccessfully:
ODBC;DRIVER={MariaDB ODBC 1.0 Driver};SERVER=qcyops;DATABASE=master_repository;UID=root;PWD=mypassword;PORT=3306;
CONNECTION TIMEOUT=15;COLUMN_SIZE_S32=1;DFLT_BIGINT_BIND_STR=1;LANGUAGE=us_english;CHARSET=utf8mb4;AUTO_IS_NULL=1;FOUND_ROWS=1;

I have also tried this bare-bones string just to see if I can make it work (still unsuccessful):
ODBC;DRIVER={MariaDB ODBC 1.0 Driver};SERVER=qcyops;DATABASE=master_repository;UID=root;PWD=mypassword;PORT=3306;

I can successfully connect MySQL workbench, Heidi SQL, and NetBeans to MariaDB and run SQL etc.
I have a similar problem in MS Excel, but it simply crashes.
When I actually do create a DSN I get the error message (URL address mocked):
ODBC call failed.
Access denied for user ‘root@###.##.##.###’ (using password: NO) #1045

I’m assuming it is something simple that I have overlooked.
Hoping someone has a possible solution to my problem?
Thanks! Scott

Reply | Quote

Replies

Thanks!
I am indeed using the 1.0.5 connector, yet still having the problem.
Also, I was mistaken about our server. It is a Windows Server 2012, not Linux.

Reply | Quote

I am re-posting this with both corrected and additional information:
————————————————————————-
MS Access 2013 (MS Office 365 Pro-Plus)
MySQL 5.5.46 (32-bit) Community Server GPL loaded on my local machine
MariaDB 10.1.8 (32-bit) loaded on my local machine
Windows 7 Enterprise SP1
Dell Optiplex 790 (64-bit) Intel i7-2600

MariaDB 10.0.19 64-bit loaded to a VM on a Windows 2012 server
MariaDB 1.0.5 ODBC connector

MySQL 5.5.21 loaded on Windows 2008 server
MySQL 5.2 Unicode ODBC Connector

I have one primary application using MS Access as a Front End to a MySQL backend.
I actually have several FE apps with just a single user, but one primary application with multiple users.
The server hosting MySQL is an old box and has just recently been decommissioned. It was a physical box located on-site.
The new server has MariaDB installed, and I need to migrate my FE apps to it.

I have been unable to successfully connect to it using the connection string below.
Also listed below is the connection string that I have successfully used since 2011 for MySQL.

One side note, I have discovered during my research that (for MariaDB versions prior to 10.1.x) the optimizer_switch
default value for ‘engine_condition_pushdown’ is OFF, and should be set to ON. This has more to do with the WHERE
clause processing for SQL statements. I can’t even get a connection!

This is the full connection string I have used (password mocked here) for four years to MySQL as a DSN-less connector for pass-thru queries:
ODBC;DRIVER={MySQL ODBC 5.2w Driver};SERVER=qcyce1;DATABASE=enterprisedata;UID=root;PWD=mypassword;PORT=3306;
CONNECTION TIMEOUT=15;COLUMN_SIZE_S32=1;DFLT_BIGINT_BIND_STR=1;LANGUAGE=us_english;CHARSET=utf8mb4;AUTO_IS_NULL=1;FOUND_ROWS=1;

This is the MariaDB version I have tried unsuccessfully:
ODBC;DRIVER={MariaDB ODBC 1.0 Driver};SERVER=qcyops;DATABASE=enterprisedata;UID=root;PWD=mypassword;PORT=3306;
CONNECTION TIMEOUT=15;COLUMN_SIZE_S32=1;DFLT_BIGINT_BIND_STR=1;LANGUAGE=us_english;CHARSET=utf8mb4;AUTO_IS_NULL=1;FOUND_ROWS=1;

I have also tried this bare-bones string just to see if I can make it work (still unsuccessful):
ODBC;DRIVER={MariaDB ODBC 1.0 Driver};SERVER=qcyops;DATABASE=enterprisedata;UID=root;PWD=mypassword;PORT=3306;

Just a side note: Even though the driver name says {MariaDB ODBC 1.0 Driver}, I do have the 1.0.5 driver installed. This remains the correct driver name,
per MariaDB website, and looking in my windows registry. The 1.0.5 driver supposedly provided bug fixes specifically for MS Access connection problems.

Since the server has been shut down, I have installed both MySQL and MariaDB onto my local machine.
Again, same problem. I can connect all-day long to MySQL, but not MariaDB.
Simplified string MySQL:
ODBC;DRIVER={MySQL ODBC 5.2w Driver};SERVER=localhost;PORT=3306;UID=root;PWD=MyPassword;DATABASE=enterprisedata;
Simplified string MariaDB:
ODBC;DRIVER={MariaDB ODBC 1.0 Driver};SERVER=localhost;PORT=3307;UID=root;PWD=MyPassword;DATABASE=enterprisedata;

I have also switched up the designated ports for MySQL and MariaDB, with the same results.
I can connect to local MySQL but not local MariaDB.

I can successfully connect to MariaDB (both local and server) using other interfaces: MySQL Workbench, Heidi SQL, and NetBeans.
I successfully run SQL against it in each of them.

I have a similar problem in MS Excel, but it simply crashes.

When I actually do create a DSN I get the error message (URL address mocked):
ODBC call failed.
Access denied for user ‘root@###.##.##.###’ (using password: NO) #1045

I’m assuming it is something simple that I have overlooked.
Hoping someone has a possible solution to my problem?
Thanks!
Scott

Reply | Quote

I have no experience with MariaDB and I haven’t coded in Access for a couple years, now, but here goes anyway.

When I actually do create a DSN I get the error message (URL address mocked):
ODBC call failed.
Access denied for user ‘root@###.##.##.###’ (using password: NO) #1045

I’m not sure how relevant this may be. Are you using the same login details for the DSN as for the other connections?
The question arises from the fact that the error message shows a permissions issue. User root (for which no password has been specified) has no permissions to connect from the (obscured) host.

I doubt that this is the real issue, though, unless you are using different users for the different tests you made, connecting from other apps to MariaDB. Is that the case?

Reply | Quote

I’ve never tried connecting Access to MariaDB. However from my reading of the messages, you have a straightforward authority problem.

Access denied for user ‘root’

Pretty much says it all don’t you think? So the first question is, is there a user named ‘root’ on the MariaDB server? And is MariaDB configured to allow connections from this user?

Adjust for the presence of Active Directory as appropriate.

Reply | Quote