Use MsAccess as MySql frontend

 

Horses for courses, where MySql, PHP and HTML are native to the web, PHP/HTML suck as table GUI,no offense, I just got extremely frustrated with it today. Another way to get the data off the web onto my desktop is using FTP/ODBC and enclosing MySql tables in my MsAccess desktop database. It has it perks but it works. If you want to try it check with your host (if you are not self hosting) if your host allows external access to your mysql server. For local servers it works flawless.

Download the odbc connector

Over at Dev.MySql they muist have heard my screams of agony an programmed an ODBC (open database connectivity) driver. Windows supports the ODBC standards hence in Office you can use external databases if you install an odbc driver for the database type.

Create the system datasource

Once the driver is installed you can add the MySql database as open database to Windows (odbc works via the operating system) and link the tables in MsAccess. Start by opening the Windows configuration screen and open System manager, where you can access the ODBC Datasources and open the ODBC Administrator screen :

Pick the tab System DSN and choose Add… to select a driver : the new MySql ODBC Driver.

Then comes the magic bit : when you click Finish, the MySql driver pops up its own configuration screen :

The data you fill in is the same login data used in WordPress wp-config.php : the mysql database host, login and password, and what database to select. When you are done, you can make more than one ODBC Sources so you can add every WordPress install you have on the web to it, provided you have access to the mysql server. A lot of hosts do not actually allow it, but mine do.

So far so good, the Datasource is now known in the Windows operating system, now we go to MsAccess.

Enclose the tables in MsAccess

 

Under External Data pick ODBC Database :

In the screen that opens, pick the Link data option :

And in the next screen pick the tab with machine data sources : this picture shows both my new localhost database and the juust.org database so I will pick that one (it is in Rotterdam somewhere on a server)

When I pick that one the driver kicks in and connects to the remote database, retrieving a list with tables for me to link into the database, for the occasion I will link the Ak_Twitter table of the WordPress Twitter Tools plugin that has my most recent Tweets :

And click okay and Voila :

The tables are linked

Note: if you get an error “server has gone away, ( ask your host to) set the connection timeout to 300 or more

 

 

 

 

Posted in juust, mysql, wordpress and tagged .

Leave a Reply

Your email address will not be published. Required fields are marked *