On the grounds that someone else may have this problem, and in response to
John's post, below, I am providing a "cookbook" of how to do what I asked.
The problem: I have a bunch of users, all with small local MySQL databases
on their Windows boxes (which get synchronized with a large server
database--but they need to be able to work offline.) They mostly have and
know Microsoft Access, and we have a requirement to support letting them get
to their data in Access. Turns out you can bring up the MySQL database in
Access just fine.
BTW, we did consider just using Access locally, with the JDBC-ODBC bridge.
It worked, but we weren't very impressed with how robust it was. Also, it
would have been harder to avoid having the users modify "my" database.
(1) Install ODBC:
- Download ODBC from the mysql.com page and unzip into a directory. (Note
there are downloads for 95/98/ME and NT/2000/XP. These differ by one file:
The file odbc.inf is a copy of odbc.95 or odbc.NT, as appropriate. Also,
there are both readme and README--which confuses Windows--they are the
- Run the installer, SETUP.EXE
(2) Link to your MySQL database from Access
- Start Access
- Use the wizard to create a new Access database
- Click File | Get External Data | Link Tables
- Select Files of Type: ODBC Database
- Select the Machine Data Sources tab
- Click New
- Select User or System Data Sources, as desired
- Click Next
- Select MySQL and click Next
- Click Finish
- You should get an ODBC driver configuration screen. Fill in:
Windows DSN name: Name you would like to use for this data source.
MySQL host (name or IP): localhost
MySQL database name: The name of the database.
- Click OK
- Your new data source should be selected; Click OK
- You will get a Driver connect screen (which looks like the configuration
screen above) - Click OK
- You will get a list of tables. Select those you want to see through
- Voila! You can now access the tables through the Access database screen.
> From: "john" <john@stripped>
> To: "MySQL Lists" <mysql@stripped>
> Subject: RE: accessing MySQL database from Access
> Date: Fri, 5 Apr 2002 08:33:56 -0600
> You know this sounds like a good idea, and I know this is what the
> are for that come provided with mysql(d). (MYODBC and mysqlaccess)I would
> like to inquire, what do you mean. I don't comprehend what you just said.
> Please someone give more specific details...
> -----Original Message-----
> From: Dan Nelson [mailto:dnelson@stripped]
> Sent: Thursday, April 04, 2002 8:42 PM
> To: Bill Easton
> Cc: mysql@stripped
> Subject: Re: accessing MySQL database from Access
> In the last episode (Apr 04), Bill Easton said:
> > I have a bunch of users with a MySQL database on their local
> > machines.
> > Life would be simpler if they were able to access the MySQL database
> > (read only) from Microsoft Access.
> > Can this be done through an ODBC connection? Can somebody tell me
> > how to set it up?
> Just install myodbc on the windows machines, set up a DSN pointing to
> the MySQL server, and link the tables in your Access project.
> Dan Nelson