Mr N

Most users understand Microsoft products and a lot of users are really proficient with all the tools in a suite of Microsoft products, such as Microsoft SQL Server or Microsoft Access. Fortunately, not everyone uses Microsoft products and there are usually occasions where a Microsoft trained person needs to do a similar task using a database product by a different vendor.

Lots of places are starting to use Open Source or free software as an alternative, and so there will be a greater need to learn the way a different vendor works. So to that end I will describe the processes that one might use to do an import from a MySQL SQL dump file to a MySQL database and access via Microsoft Access. The following is an out-line of what to get from where with notes rather than a step-by-step set of instructions.

We will start by installing our own copy of MySQL. The database application you will need is called “Windows ZIP/Setup.EXE (x86)” and can be downloaded from the MysQL website. This is a standard windows installer and shouldn’t present any problem to some one who is used to installing windows applications. The default administration user for MySQL is root, and so for the “get up and running” aspect, is the only user we will use.

Once the database server software is installed we will have to import our data. Typically some one will email you this or provide it to you as a compressed SQL dump of the database. A dump file is all the SQL commands needed to create a database (it is generally used as a way of backing up a database). If the customer is using MySQL then they may use gzip or rar as a compression format which are not native to windows but are to a unix environment. There is a handy open source program called 7-Zip which can help.

7-Zip will easily uncompressing archives, files and folders. It has very nice integration with Windows to aid this process, one of which is appearing in the context menu for a file, folder or archive.

Use 7-Zip to uncompress your MySQL dump file. Next we need to get the dump file into the database. For this we will use the MySQL command line tool - don’t be scared it’s easy!

Here there are 2 types of import file: those that create the database for you and those that don’t. If this import file does create a database, then you simply need to do the import. In the examples below we will assume that you have uncompressed your dump file into the top level directory “C:\".

C:\> mysql -uroot < uncompressed_file.sql

If the dump file does not create a database we have to do it. Below we will create a new database called: my_new_db and import into it.

C:\> mysqladmin -uroot create my_new_db
C:\> mysql -uroot my_new_db < uncompressed_file.sql

We can check everything went in all right by doing a few small tests. Log in to the database my_new_db, list all the tables and count how many records are in the table table_name_1.

C:\> mysql -uroot my_new_db
mysql> show tables;
mysql> SELECT COUNT(1) FROM table_name_1;
mysql> exit;
C:\>

If you are used to using Microsoft products then you may find it difficult to use a command line; you will almost certainly want to use something you are familiar with already - like Microsoft Access!

MySQL has an Open Database Connectivity connector (ODBC) which is the databases’s glue to join with other applications in a standards based way. The glue can be downloaded from the MySQL’s ODBC Connector web page. After installation, you can create a Open Database Connectivity data source from the control panel (Control Panel->Administrative Tools->Data Source (ODBC)) which will allow other applications to use this method to access MySQL.

Microsoft Access lets you read from an ODBC datasource, so you can now use the front-end tools that your users are used to without them needing to know what kind of database is at the back-end.