MySQL and MS Access

[Paulkirkewalker]

Hi Everyone,

 

I'm looking for a way to write data to a MySQL db (no problem so far) and then access the data on MySQL from a Microsoft Access database on our LAN.  My first thought was to use the MyODBC driver from MySQL but because our databases are on shared webhosting (1and1) you can only access them from 1and1 webspace.  I can't run MS Access on the webserver because the hosting package is Linux.

 

Does anyone have any ideas about how to accomplish this?  Do you know a clever work around for getting the databases to talk despite the problem with the shared hosting?  Maybe the answer is to use a flat file database of some kind?  What do you think?  I'd love to hear your ideas.

 

Yours,

Paul.

[veridicus]

How "live" does the data need to be?  Because you're going to run into some serious performance issues.

 

You could peform one-time or periodic dumps of the MySQL database and import them into Access.  Why do you need Access to get to data from your MySQL server?

[Paulkirkewalker]

Hi veridicus,

 

Here's the story:  We have an online form which collects data that has to end up in our Microsoft Access database on our LAN.  At the moment, the data is just emailed to us from the form so getting it into the database is very time consuming.

 

The data doesn't need to be very "live" at all.  One the data is submitted from the form it won't be changing again until it is in the Access database.  What I'm looking for is a way to cut out the need to email the data from the form and enter it manually to the database.  The online database would only act as a bridge between the form and the access db.

 

I'd certainly be looking at periodic dumps from MySQL to Access but the problem is getting them to talk to each other.  I am familiar with MyODBC but I can't use it to connect from our LAN to the MySQL database as the MySQL server is a commercial shared one and can only be accessed from web space supplied by the hosting company.

 

It might be that MySQL isn't the best "bridge" to use.  I'm looking at using a .csv or even .txt file instead but I'd prefer to use MySQL.

 

Do you have any ideas?

[veridicus]

I would probably append new records to a tab delimited txt file accessible over the web.  Then download the file periodically import into (and update) your Access database.

 

MySQL's not the right storage method if you can't easily access it when you need it.