Assume that you are an Access developer, and you want to port an Access backend to a local MySQL server for development and testing. You will maintain your own solutions and enjoy using CommunityServer
Assume that you are an Access developer, and you want to port an Access backend to a local MySQL server for development and testing. You will maintain your own solutions and enjoy using Community Server
Step 1-install MySQL
Download this Server, MySQL GUI tool, and MySQL ODBC driver from http://dev.mysql.com/downloads. You can select 5.0, 5.1, or 6.0 servers. Then install them.
Precautions During installation:
Your firewall should be set to allow connections through Port 3306.
Run MySQL as a service (you can select the command line mode ).
MySQL installation default username/login = root and server = localhost.
MySQL provides the storage engine/table type options-MySAM and InnoDb. The latter looks more like Access, which has transactions and foreign keys, so we use this method in this article.
Has user and security options. Do not forget to record all passwords.
Install Bullzip Access to MySQL and Dreamcoder for later use.
Step 2 -- move the table from Access to MySQL
Use Bullzip to migrate the background data table to MySQL, or open your database and export your data through ODBC.
Note:
MySQL does not recognize the functions that Access uses by default for fields (such as the Now () and Date () functions in the Date/Time fields): they may be deleted or the table may be rejected. We recommend that you delete them before transferring/exporting them.
The autonumber field of Access is inconsistent with the auto_increment field of MySQL. Bullzip will be converted during the transfer, but ODBC will not.
The Access data type is converted to a similar MySQL data type. However, MySQL has a wider range and you may want to change them later.
The "ole object" of Access is changed to "blob" in MySQL ". Because of an OLE server problem, we changed blobs to longtext.
Step 3: Change the MySQL table (field)
Open Dreamcoder or MySQL manager (a GUI tool ). If you are familiar with SQL Server, consider these as Enterprise or SQL server management studio express interfaces.
Connect to the database for transfer/export.
Open each table in order and check:
The Autonumber field is auto_increment.
Each table has a primary key (one or more fields) (in Dreamcoder, create-new constraint ). Otherwise, you must specify this parameter during connection. Otherwise, the joined table cannot be updated.
The field is correct by default. We check the consistency with the default value of front-end data login.
Whether to allow Null values.
Whether the data type is as expected.
There is a timestamp field. the default value is current_time (usually the last field in the table ).
Step 4: create your MySQL backup system
Open MySQL manager and back up your MySQL database. This saves the schema and data to a. SQL file. If you want to create a database on another computer, install a MySQL server on that machine and copy the. SQL file to the new server.
Step 5: link from the front end to MySQL
Open your Access front-end. If you have already linked to an Access backend, you cannot use the linked table manager to change to an ODBC data source. Therefore, you can delete the link to the Access background and link it to the MySQL background through ODBC. Similarly, once you connect to the ODBC data source, the linked table manager only provides options for the ODBC data source.
When creating an ODBC data source, in ODBC configuration -- Advanced -- Flags1, check Return Matching Rows and Allow Big Results.
Performance
So far:
Using Access 97 on the MySQL background is much faster than using Access 2007 on the same background.
Initially, this hybrid and list record source uses the record source query builder. For some reason, using user-created queries to replace them can significantly improve the speed.
In the current configuration, Access 2007 is faster than using the MySQL background.
