If you need to import a table of data from an Access database for use in Drupal, you don't need a module to do that. You can use tools provided by Microsoft and Oracle (i.e., MySQL) for connecting the databases. This looks to me like a better way to go than using a Drupal module because it uses a direct data connection between Access and MySQL with nothing intervening that could introduce a potential for error. But I'm open to being corrected on that if others explain why a certain module is a better way to do this.
The data I'm talking about here is not CMS data (like user IDs, field types, etc.), but is data related to the subject of a page to be displayed. So this is not about migrating WordPress or Joomla to Drupal. It's about migrating content data into MySQL tables for display by Drupal on webpages. This could be used, for example if you have population data in Access and want to put up a table of that data on a Drupal site.
The procedure described here:
- Was done in Windows 7 64 bit with MS Access 2007, XAMPP v. 5.6.3, and MySQL v. 5.6.21.
- Is based on the official MySQL documentation, the MySQL Connector/ODBC Developer Guide.
- Is based on the ODBC ("Open Database Connectivity") technology and so is probably applicable to transferring data in both directions among a wide variety of data platforms, including Excel, SQL Server, etc.
Select the destination database. When you transfer the table from Access to MySQL, you can either put it in an existing MySQL database or create a new database in MySQL to receive it.
Install the connection driver The main thing you need is a driver capable of connecting the two databases, called "Connector/ODBC" and available from Oracle after signing up for a free account. The download page offers four varieties, either an automatic installer (which is easiest to use) or a zip archive and either 32- or 64-bit. Installation instructions are provided, including specific instructions for Windows.
Use the 32-bit version for Access. Because my machine is 64-bit, I downloaded the 64-bit version. But when I tried to use it, I got a cryptic message about an "architecture mismatch," which I eventually figured out meant that the databases couldn't communicate because Access is a 32-bit program and the driver was 64-bit. So I then downloaded the 32-bit and thought I'd keep both versions on the computer. The installation made no complaint about that, but it gave both versions the same name, so when I went back to start the transfer again, only one ODBC driver was visible and I had no way to tell which one it was. Rather than take chances, I uninstalled both versions and then reinstalled just the 32-bit version.
"Connector/ODBC" actually installs two drivers, an ANSI and a Unicode version. The Unicode version can work with more characters at the cost of some speed, as explained in the instructions. (The instructions say you can install either one or both, but the installation procedure installs both without asking.)
Define the connection. Once you have "Connector/ODBC," you use it to define a connection to the MySQL database. This is done in Windows 7 using the "ODBC Data Source Administrator" (Start > All Programs > Administrative Tools > Data Sources (ODBC)). Follow the provided instructions. However, there are two important things those instructions don't explain. First, the "Data Source Name" you are asked to give will define your destination MySQL database as a new data source. Therefore, use a name that is specific to that database. Second, you need to select that database from the "Database" drop-down list. In order to have the database show up in the list, enter a MySQL user name and password that has access to that database. After doing that and selecting the destination database from the list, click "Test." It should respond that the connection succeeded. Click "OK" in this box and the destination database will appear in the list of data sources in the "ODBC Data Source Administrator".
Export the table. You are now ready to export the table. The instructions are for a previous version of Access. In Access 2007, right-click on the table name and select "Export > ODBC Database". Keep or change the name of the table and click "OK". The "Select Data Source" box comes up. Click on the "Machine Data Source" tab, select the data source name of the destination MySQL database, and click "OK". Access should respond that it successfully exported the table and you should see the table when you go back to MySQL.
(On my first time around, there was a requirement to run Access "as administrator". But when I went through the procedure later, that was not necessary, so I don't know what that's about. But if you do need to, running Access as administrator can be a challenge because you can't do that by opening an Access file or via a shortcut to Access. You have to do it by right-clicking the Access exe file and selecting "Run as Administrator". I had a hard time finding the exe file since its location is not given in the shortcuts to it. On my computer, I finally found it at "C:\Program Files (x86)\Microsoft\Office\Office12\MSACCESS.EXE".)
Check the data integrity. Finally, because I'd never done this before, I wanted to test the integrity of the transfer by sending the table back to Access and checking that the result was the same as the original. Because I already had an ODBC connection defined for the MySQL database, I did this by using Access to import the table back (which procedure is a mirror image of the export process). After doing that, I first checked that the reflected-back table had the same number of records as the original. Then I set up two queries. Both linked the original and the reflected-back table on the primary key. One query set the criteria that all fields in one table were either null or equal to the corresponding field in the other table, while the other query did the same thing with the tables exchanged. The reason for doing this was to not miss an error in which a non-null value was reflected back as null or a null value was reflected back with a non-null value. Both queries returned the same number of records as are in the table, meaning that the transfer was executed without error. The table I transferred has over a thousand records and 22 fields, including Booleans, integers, text, date/time, and memo fields of up to 1,850 characters, basically every data type except floats, so I feel like it has pretty well validated the integrity of this procedure for transferring data between Access and MySQL.
Export more tables. Repeating the procedure for additional tables requires only one or two steps: If the table is going into a database that has already been set up in the "ODBC Data Source Administrator", then just export the table in Access. If the table is going into a different database, then first define the destination database in "ODBC Data Source Administrator", and then export the table in Access. Pretty simple once the driver installation is out of the way.
(This is my first contribution to Drupal documentation, so please let me know if there's anything about it that could be better.)