MS Access & mySQL

[Frostie]

Tweet

Anyone got experience on accessing/updating a mySQL database using Microsoft Access?

I have MS Access 2002, and my websites use a mySQL database thats provided by my hosting company. I just thought it would be good, and pretty useful, if I could somehow get the tables and data downloaded to my PC for use in a local Access database, then I could update locally and send back to the servers mySQL database.

Is it possible? Is it easy? Anyone do this now? How? lol

[ianm]

Does your host allow you to import and export your MySQL database as a CSV file?

If so then you can download the CSV and import it into Access.

Then when you've finished, you can export the table from Access as a CSV again upload it and then import it back into the MySQL database.

In theory this should work, but have to say I've never tried it - I usually run the SQL I want directly on the MySQL database in the host's control panel.

[999gi]

Theres a program called mySQL front that I use that is very good for this sort of thing.
You can easily import access on your C drive into mySQL at your host + you can edit the data in the MySQL database in real time

[Frostie]

Want to try and use something like mySQL Front, but it wont work for me. Enter all my details and get an error saying;

1130-HOST 'host23.56.123.34.in-addr.btopenworld.com' is not allowed to connect to this mySQL server.

I guess this is a restriction from the hosting company

[Freebie]

That may be controlled by you.

To allow remote access to a databse you normally have to tell it who to allow.

Many places seem to have a wilcard facility to allow access to all, certainly CP does. This would give you a quick and easy way to check if it worked, but i wouldn't recommend keeping it that way.

carl

[NaturalInstinct]

if you install mysql on your machine (it's free) you can use the command line to log in by going to c:mysql\bin and typing.....

mysql -h(host ip) -u(username) -p(password)

once you know you can get access to it you can download a program i use called "excel to mysql" that will use those login details to log on to mysql and pump in the excel file.

Any questions just give me a shout.