Language: Deutsch English















Last Update: 2017 - 07 - 09





mySQL & Access

Table of Contents

  • What is mySQL
  • Linking tables via myODBC
  • You have an error in your SQL syntax
  • You can't add new records
  • Username and/or password are not beeing stored



  • What is mySQL

    mySQL ia a DBMS developed for Linux-/Unix-Systems, which is also available for Win32 evironments for some time now. Since June 2001 mySQL is Open Source software and licensed under the GPL (GNU General Public Licence.

    Within the LAMP (Linux - Apache - MySQL - PHP)-configuration, mySQL is part of the most often used system for database driven websites.

    The key feature of MySQL is the intentionally lean database-engine that is able to outperform most other DBMS. Lean does also imply that there are several features missing, which one would take for granted in many other DBMS. For example trigger, cursor, foreign keys and subselects are still missing in the current production releases, transactions are only supported with the most recent releases.

    How is that connected to Microsoft Access?

    MySQL can be used as a very cost- and performance-effective backend for Access-Applications. Of course MySQL can not measure up to Microsoft SQL Server regarding ease of administration and range of features, but it can very well do that regarding the cost of ownership.

    If you are using MySQL as backend for your website and are able to directly access that server via your LAN, Access makes a supreme tool for managing and analyzing the data gathered or served by your MySQL-Server to the web.

    I think these are two convincing reasons to take a closer look on Microsoft Access and MySQL working together. The articles on this page describe problems (and their solutions) I encountered while working with Microsoft Access as a front-end to MySQL-Databases.



    Back to the TOC

    Linking tables via myODBC

    MyODBC is the driver for accessing mySQL via ODBC. MyODBC is, like mySQL, available for download on the MySQL-Website. Compiled binaries are available as well as the source code of MyODBC.

    After you successfully installed MyODBC you can select the MySQL-Driver in the ODBC Data Source Administrator in the Control Panel to create and configure new ODBC Data Sources pointing to a database on a MySQL-Server. Using such a data source you can link tables from the MySQL-Database to your Microsoft Access-Applications.

    Unfortunately there are some strings attached to MyODBC which may cause some distress unless you have studied all the documentation- and readme-files regarding the use of MyODBC with Access quite thoroughly. I've have compile the most interesting options of MyODBC you should be aware of in the following list.

    • If you link a MySQL-Table to Access, all options you configured for MyODBC will be written to the Connect-Property of the linked table in Access. If you do change any of the options after initially linking the table to Access these changes will not affect the tables that are already linked at that time. You'll have to relink all the tables to Access for your changes to take effect!
    • You should always use the option Return matching rows when creating a Data Source pointing to a MySQL-Server to use it with Microsoft Access.
    • The data type BIGINT is a general problem in connection with Microsoft Access. Access does not support the full range of values of BIGINT (64bit, which means values up to 18446744073709551615). For this reason you should avoid using the BIGINT data type in databases that will be used with Microsoft Access whenever possible.

      The Change BIGINT Colums to INT-option can remedy problems with BIGINT only to limited extend. If you use this option it is possible for Access to deal with BIGINT-Columns as long as the values in these columns do not exceed the maximum value of the Access data type 'Long Integer' (32bit). Values greater than 32bit will be displayed incorrectly in Access and can not be written correctly by Access to MySQL.

      You can't use BIGINT-Columns as Primary Keys in your tables in any circumstances. Even with the Change BIGINT Colums to INToption Access will not be able to deal with BIGINT Primary Keys.
    • The MyODBC-Driver has a integrated feature to trace all SQL-Statements passed to the MySQL-Server via ODBC. You can enable this feature by using the Trace ODBC-Option in MyODBC. The information logged by the Trace ODBC-Option is not as extensive as that logged by the standard trace-tool of the Microsoft ODBC Data Source Administrator but the tracing is significantly faster. The gathered information should usually be quite sufficient for the need of an Access-Developer.
    • To use MyODBC with Access 2.0 you have to enable the option Simulate ODBC 1.0. If you still experiences problems with Access 2.0 you should consider using an old 16bit version of MyODBC.

    You'll usually find a readme-file describing all the options of MyODBC in the ZIP-Archive containing MyODBC. You'll find some additional information on the MyODBC-Options in the MySQL-Manual at the MySQL-Website.



    Back to the TOC

    You have an error in your SQL syntax

    Occasionally I see descriptions of problems with linking tables using certain versions of the MyODBC-Driver in some newsgroups. If you try to link a MySQL-Table to an Access-App you get this error: 'ODBC-Call failed -(TCX)(myODBC)You have an error in your SQL syntax near 'databasename'.FROM 'tablename' at line 1(#1064)".

    This seems to be a bug in some versions of the MyODCB-Driver (e.g. Version 2.50.33 is affected by this problem). Usually you can fix all this problems without any changes to your application by simply upgrading to a newer (or downgrading to an older) version of the MyODBC-Driver.



    Back to the TOC

    You can't add new records

    You have linked your MySQL-Tables to your Access database using MyODBC. The data from the MySQL-Tables is displayed correctly but you are not able to add any new records to some of your tables, or if you do, all the fields of the new record display "#Deleted" after you save the record.

    The reason for this usually lies with the primary key of that table or with the column you choose as unique column when linking that table to Access.

    Microsoft Access uses an ODBC keyset cursor to access any linked ODBC table. That cursor type requires an unique column to identify a single record in the linked table. If the linked table does not have a primary key and you didn't select an unique column when linking the table to Access, the linked table is read-only in Access and you can not add any record. If the primary key/unique column is of data type CHAR, BIGINT or a floating point type (FLOAT, REAL and DOUBLE) you will most likely encounter the problem with "#Deleted" in new records because the data in those types of columns is often stored different on the server than it is displayed in Access. The solution to this problem is to make sure every linked table has a primary key and the primary key is of a data type that Microsoft Access can use without any conversion issues (INT (int32) or VARCHAR).

    This issue is described in the article Q128809 in the Microsoft Knowledgebase as well.



    Back to the TOC

    Username and/or password are not beeing stored

    I could not experience this issue by myself but it has been reported in the German Access-Newsgroups.

    Access sometimes 'forgets' to store the username and/or the passwort for accessing the mySQL-Database. You should be able to solve this issue by relinking the mySQL-tables with a VBA procedure on the startup of your Access-Application.

    A sample for such a procedure is the following sub.

    Sub reconnectMySQL()
      Dim db As DAO.Database
    
      Set db = CurrentDb()
    
      db.TableDefs("yourTable").Connect = _
         "ODBC;DSN=yourDSN;SERVER=yourServer;" & _
         "PORT=3306;OPTION=12345;" & _
         "DATABASE=yourDB;USER=yourUserName;" & _
         "PASSWORD=yourPassword"
      db.TableDefs("yourTable").RefreshLink
      Set db = Nothing
    End Sub
    

    The OPTION-Value defines the MyODBC-Options. The options you type in the connectstring are the options that are used when accessing the table, even if you set up different options during the configuration of your DSN.

    To find out what the correct OPTION-Value for your application is, see the Readme-File from the MyODBC-ZIP-File.



    Back to the TOC

    Subscribe to my newsletter

    *

    I will never share your email with anyone. You can unsubscribe any time.



    © 1999 - 2017 by Philipp Stiefel