Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2011
    Location
    Canton, MO
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cannot connect MS Access 2013 to MariaDB

    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;U ID=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;U ID=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;U ID=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

  2. #2
    jwoods
    Guest
    You might find something in this article...

    http://www.itworld.com/article/28615...nnections.html

    You might try the new 1.05 version of the ODBC connector...

    https://mariadb.com/kb/en/mariadb/ma...release-notes/

    One of the fixes in 1.05 was for Access...

    "Fixed many issues preventing normal functioning of MS Access with the Connector. In particular - https://github.com/MariaDB/mariadb-connector-odbc/issues/1"
    Last edited by jwoods; 2015-10-31 at 00:52.

  3. #3
    New Lounger
    Join Date
    Apr 2011
    Location
    Canton, MO
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    New Lounger
    Join Date
    Apr 2011
    Location
    Canton, MO
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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=My Password;DATABASE=enterprisedata;
    Simplified string MariaDB:
    ODBC;DRIVER={MariaDB ODBC 1.0 Driver};SERVER=localhost;PORT=3307;UID=root;PWD=My Password;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

  5. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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?
    Rui
    -------
    R4

  6. #6
    jwoods
    Guest
    In Control Panel > Administrative Tools select Data Sources (ODBC).

    In the Tracing tab, set up a trace.

    This might give you more info on the Access Denied error.

    Also, the MariaDB ODBC connector is now at version 2.09.

    https://mariadb.com/kb/en/mariadb/ab...onnector-odbc/

    I'll also mention Universal Data Link (.UDL) files as a good way to test connections in isolation from the application.

    https://msdn.microsoft.com/en-us/lib...=vs.71%29.aspx
    Last edited by jwoods; 2015-12-04 at 21:43.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    561
    Thanks
    51
    Thanked 68 Times in 66 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •