Last Update: 2024 - 05 - 21 |
What is mySQLmySQL 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 myODBCMyODBC 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.
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 syntaxOccasionally 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 recordsYou 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 storedI 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
I will never share your email with anyone. You can unsubscribe any time. © 1999 - 2024 by Philipp Stiefel - Privacy Policiy |