Data Management

Capture MySQL data from the Web with Access

Transferring data from one database system to another can be a major hassle. Follow these steps to easily get data from MySQL into Access.


MySQL has emerged as a popular database within the Internet community for a number of reasons, including:
  • ·        It's fast.
  • ·        It's free.
  • ·        It runs on Linux and a number of other platforms.
  • ·        It integrates well with Apache, a popular Web server.
  • ·        It integrates well with PHP.

MySQL has an ODBC interface available. If you control your Web server or MySQL server machine and want to pull data into desktop applications, using the ODBC interface works well and makes sense. But if you don’t control the server, it is possible that the port needed to access MySQL through the network may be disabled, and other avenues must be explored to move your data back and forth.

The problem
Such was the situation for one of my clients. In the interest of security, the client opted to create a Web presence via a hosting company, avoiding any need to open up its own systems to the wider Internet. Many of these hosting companies offer very reasonable rates and provide MySQL, FrontPage extensions, CGI, and all the other tools needed to make a full featured Web site. They also take care of keeping the hardware running and the software updated. This particular host even provides tools to help you move data in and out of your MySQL database via a Web interface—but it only works with the basic MySQL dump format, which can’t be directly read into Microsoft Access or other desktop applications.

The client didn't want to set up MySQL on the company's own servers to facilitate the conversion, and initially it was sending all the files through me, because I run MySQL locally on my own servers. I would manually retrieve the data via the hosting company’s dump. The process involved me loading the data into my database, transferring it via ODBC into Access, and sending the client the resulting database. This worked, and I certainly didn’t mind doing the service for the client, but we needed something better so it wasn’t quite so dependent on me.

I decided to revisit the idea of the screen scraper. The hosting company displays the data dump on a Web page, and I played a little with automated scripts to go through the login process and capture the data. The login was intentionally set up to make this type of thing difficult to do via automation for security reasons. So instead, I decided to use the Microsoft Web Browser ActiveX control directly within an Access database. This allows users to go through the normal login/dump process, press a button to capture the MySQL data at the point when it is available, and then transfer the data into a compatible table in the Access database. Once the data is in Access, users can then use ODBC or their usual methodology to move the data into the ERP system or other systems within their organization.

Microsoft Access
The Access database is fairly simple, consisting of a table for the end data (this can be expanded to multiple tables if needed), a table to list applicable tables you may want to append data to, and a form to do the Web capture. For this article, I’ll use a table called Sales_Data, which collects data from a shopping cart application on a mock Web site. Again, this table should have the same name and structure as the MySQL table at the Web host, as far as the number and types of fields. I actually created the table first in Access (Figure A) and then used ODBC to move it into MySQL on my local server.

Figure A


The opening of the Access application also opens the Web Control Panel form, as is the case when my client goes to the Web page. In my example, the application goes right to the PHP login screen (Figure B) on my server.

Figure B
Login screen


The ActiveX control is called WebBrowser1, and the following code executes when the form loads:
Private Sub Form_Load()
WebBrowser1.Navigate2 http://192.168.192.1/~stew/mysql/
End Sub


Figure C
MySQL screen shot


The rest of the form consists of two buttons, the first (Load Control Panel) of which proceeds to the MySQL screen (Figure C) via this code:
Private Sub LoadControlPanel_Click()
WebBrowser1.Navigate2 http://192.168.192.1/~stew/mysql/mysql_dump.php
End Sub


The second button (Capture Data) does the data capture and processing, which is the bulk of the code. I’ll cover this in more detail later in this article.

Next, you'll find a drop-down list of available Microsoft Access tables that can be populated with the data. This list is populated from another table, Web Control MySQL Tables, which is just a list of table names (in this case, it's just one name, Sales_Data).

Next, you'll see a check box that gives you the option of deleting all old data before loading the new. You could do other things here that would be more sophisticated, such as choosing a cut-off date, but using this option was adequate for this application. If the check box is selected, a purge query clears the table before repopulating it with the new data; otherwise the new data is just appended.

Finally, two text entry fields contain the company name, which appears in the header of message boxes, and the name of the MySQL database, which is used as part of the sanity checks that are run before processing the data.

It works
That's all there is to it. The user must log in to the Web interface and run the dump query on the Web host. When the MySQL dump appears in the form’s browser (Figure D), you press the Capture Data button to capture the data and place it into the Access table. The application checks the dump to see if it is indeed a MySQL dump based on some formatting rules, and it also makes sure that it came from the correct database and table. If these rules are satisfied, the INSERT lines of the dump are processed and fed to an Access function to add the lines to the target table, and a message box will appear telling how many lines were processed. An appropriate message will be generated, and the routine will exit back to the form if any errors are encountered. Listing A contains the complete Access code.

Figure D
MySQL screen dump


 

Editor's Picks

Free Newsletters, In your Inbox