MySQL database tricks

I have a MySQL database that needs to be converted or exported to a Microsoft Access database. I can connect to the MySQL database with Access using ODBC, but when I try to save or export the MySQL data, Access wants me to export each table individually. Is there a way to create a self-contained Access database file from the MySQL database without going through the export steps for each table in the database?

Instead of using the File/Open command in Access to connect to your MySQL database using ODBC and then exporting the individual tables, you can create a new blank Access database and then import the data from the MySQL database. Start with the File/New command in Access and create an empty database. Then choose Get External Data from the File menu and select Import rather than Link tables from the Get External Data submenu. Select ODBC Databases in the Files of Type pull-down list in the Import dialog, and then select the same ODBC datasource definition for the database you want to import that you used previously with the File/Open command to connect to MySQL. After choosing the Import selection from the Get External Data submenu, the dialog you see when the ODBC connection is made will let you select the tables you want to import into Access. Click OK in this dialog, and Access will import the data from the MySQL database. Now you have a self-contained copy of the database on your PC that you can work with without affecting the MySQL database.

To continue reading, register here and become an Insider. You'll get free access to premium content from CIO, Computerworld, CSO, InfoWorld, and Network World. See more Insider content or sign in.

I have a MySQL database that needs to be converted or exported to a Microsoft Access database. I can connect to the MySQL database with Access using ODBC, but when I try to save or export the MySQL data, Access wants me to export each table individually. Is there a way to create a self-contained Access database file from the MySQL database without going through the export steps for each table in the database?

Instead of using the File/Open command in Access to connect to your MySQL database using ODBC and then exporting the individual tables, you can create a new blank Access database and then import the data from the MySQL database. Start with the File/New command in Access and create an empty database. Then choose Get External Data from the File menu and select Import rather than Link tables from the Get External Data submenu. Select ODBC Databases in the Files of Type pull-down list in the Import dialog, and then select the same ODBC datasource definition for the database you want to import that you used previously with the File/Open command to connect to MySQL. After choosing the Import selection from the Get External Data submenu, the dialog you see when the ODBC connection is made will let you select the tables you want to import into Access. Click OK in this dialog, and Access will import the data from the MySQL database. Now you have a self-contained copy of the database on your PC that you can work with without affecting the MySQL database.

Read more about data center in Network World's Data Center section.