In order to use JCMS Access versions after 4.6.0, the data (JCMS_db.mdb) must be converted to a MySQL database. The user interface (JCMS.mdb) will still use Microsoft Access. It is then possible to also install and use the JCMS Web user interface connected to the same MySQL database.
It is recommended that you upgrade to JCMS Access v3.0.2 or greater, prior to converting your database. You must also have access to a MySQL database. If you do not, you may download and install it from the MySQL website and follow the installation instructions. Please note that if you are running MySQL on a windows server, you must configure the server with strict mode disabled in order to use it with JCMS.
Preparing your Access Database for Conversion to MySQL:
Prior to running the Conversion Tool, you need to prepare your Access database by setting the correct permissions. To do this-
A. Open your interface database (JCMS.mdb) using the Admin or mtsadmin user account. Take the following steps:
- Go to the “Tools” menu and select “Security” and then “User and Group Permissions.” (Vista users, you will select from the “Database Tools” menu, “Users and Permissions”, then select “User and Group Permissions.”)
- Highlight <New Tables/Queries> from the “Object Name” list on the right hand side and make sure user “Admin” is highlighted on the left hand side.
- Check the “Administer” check box and
- Click “OK.”
B. Open your source database (JCMS_db.mdb) using the Admin or mtsadmin user account.
- Access permissions per step one above.
- Select all tables from the “Object Name” list on the right side, select “Admin” user on the left hand side.
- Mark the “Read Data” check box.
- Click “OK.”
C. Special instruction for JCMS Users using JCMS v4.2.x, v4.3.x, and future versions
- Open JCMS by pressing ‘Start Workstation’ from the interface.
- Open ‘Administrator’ button.
- Open ‘JCMS Setup Variables’ button.
- Change the variable of JCMS_DBMS to “MySQL” (case sensitive).
- Setting this variable can be done before or after conversion from msAccess to MySQL.
Note: There is an issue after converting to MySQL, and this patch fixes it (JCMS v4.2.x conversion users only).
Installing the Conversion Tool:
- download Conversion Tool for 64bit and 32bit Windows Operating Systems (works with msAccess2010 and 2007)
The next step is to download the conversion tool installation package. You may either save the file to your local machine and then run it, or select “Run” directly from the link. This will install the conversion tool and the MySQL ODBC driver. JCMS is compatible with the MySQL ODBC driver version 3.51.22.
When you run the installation package, you will be prompted with three screens like the one below:
Click the “Next” button on each of these screens. The tool will then install on your computer, and when it is finished, you will see an “Installation Complete” screen. Click “Close.”
The next step in the installation is the setup of the MySQL driver. The Setup Wizard for MySQL Connector/ODBC 3.51 is supported for use on 32-bit operating systems.
You will see the following window:
* Note: If you already have this driver installed, you will be notified and may cancel out of this step.
Click “Next” and on the next screen, leave the default setup type to “Typical” and click “Next.” On the following screen, click “Install.” The driver is then installed, and the final screen will indicate that the install is complete. You are now ready to run the conversion tool.
Running the Conversion Tool
After installing it, you may start the conversion tool at any time by clicking Start -> Program Files -> JCMS -> JCMS Database Conversion Tool. You will see the following screen:
This is where you enter the location of your JCMS interface database and log in information for your MySQL database. You also have the option of disabling your database after it is converted. This action renames the database by appending “.disabled” to the file name. Your data remain intact, and the database may be restored by removing the “.disabled” from the file name. After you have entered this information, click “Convert.” You will then see a screen indicating each step performed and the overall progress of the conversion, for example:
If for some reason your database conversion fails, a message will be provided, indicating the cause of the failure. If this happens, you may still use your JCMS Access database, as this process DOES NOT affect your existing database.
Troubleshooting problems with database conversion
Message “Your database relationships file is out of date. You must obtain the latest version from the JCMS web site before continuing.”
- You must upgrade your version of JCMS. You may only convert a JCMS database version 3.0.2 or greater.
Message “Error creating table <table name>”
- This means that the table could not be created in MySQL and could be due to special characters in the table name. Check for and remove any special characters (such as question marks or spaces) and then attempt the conversion again.
Message “Could not re-create table links. This step must be performed manually.” This message indicates that the MySQL database was created and is ready for use, but you must manually recreate the links from your JCMS.mdb by performing the following steps
- Bring up JCMS
- Hit F11
- Right click on the “ Tables” icon in the “ Objects” dialog
- Select “Link Tables ..”
- Select “ODBC databases” in the “Files of type” drop down list
- In the “Select Data Source” window that appears, click to select “myJCMS.dsn” from the list and click “OK”
- If prompted, enter log in information for your MySQL database
- Select all tables except those starting with “mysql.” Make sure the “Save password” check box is checked, and click “OK.”
If you see the following message:
You may receive this message box if you inadvertently linked to a MySQL system table. These are tables beginning with “mysql.” You may click OK and disregard this message, table linking will proceed.
You receive a MySQL log in screen when you open JCMS – This happens when you don’t check the “Save Password” box when manually linking tables. You can remove and re-link the tables, per the steps above, this time making sure that “Save Password” is checked.
You receive an error indicating that you have “no read permission on Dbinfo.” – This error can occur if the permissions in the source database (jcms_db.mdb) are not set correctly. Please see instructions “Preparing your Access database” above.