MySQL to Access

Icklesheep

ok, i have a MySQL database running for a message board i am hosting on my computer. I've been trying to work out a way how i can transfer data easily between databases etc. and it just occurred to me that an ODBC database would be ideal, because then i could access it in Access. However, i already have a MySQL 3 database set up which has existing user information, post information etc. for the message board. How could i convert this to a database which could be opened, edited etc. in Access? i.e. an ODBC database.

Thanks for your help
Matthew Stibbs

nnichols

if you donwload MyODBC driver you will be able to connect to MySQL using Access.

I wouldn't do it though as there are loads of good GUIs available for MySQL. Try MySQL-Front - http://efux.de/mysqlfront/MySQL-Front_2.5_Setup.exe

Hope this helps 😉

barand

Access is a lousy database for use with a web server. It is , however, good for querying and reporting from other databases.

As Icklesheep said, get a mysql ODBC driver and link to your MySql database from access. This gives the best of both worlds - a reasonable database for your web pages (mySql) and the query and reporting facilities of Access.

There is also an addin for Access called MyAccess which lets you open mysql tables from within access.

saidbakr

As the previous two replys you must install myodbc first
Now I will till you how to open a MySQL database through MS Access to edit or any thing you want.
1 install myodbc
2- Control panel>>ODBC DATA Source...>>System DSN Tab>>Click on ADD
"Create New data source" dialog box will be displayed, Scroll down to select MySQL, click Finish.
-- Now "TDX MySQL driver default configuration" Dialog Box will be displayed. Fill it with the suitable data it may be as follows:
Windows DSN Name type any name you want like fox1
mysql host name localhost or the ip
mysql database name: the name of your database in my sql
NOTe the attached file is an Image for this box
then click OK
Now we will start work inside MsAccess

1- Run your MSAccess, create new DB(Blank)
2-File menu>>Get External Data>>Link Tables
Note: you may choose "import", but Link tables is better because
it allow any change or update using any interface, e.g through msaccess or MySQL, to be appeared on the rest.
--Now the "Link" dialog box will be poped up, from it do:
3-"Files of Type" scroll down to select "ODBC database()"
4-Select data source dialog box will poped up, "Machine Data source" Tab Select the DSN name you typed previously for your MySql data base(fox1) Ok
5- Now Link Tables dialog box will poped up containing all tables available in the desired MySQL DB you have choosen before. you can select all tables or any set of tables you want.
Please Tell me if this helped you or not.
Regards😃

Icklesheep

thanks, that has helped. i couldnt work out how to open it in acces 😛 great help, thanks a lot.
Matt

saidbakr

Never mind and welcome!😃