Linking tables from MySQL using ODBC connectivity

G

Guest

This is all new to me, so I'm hoping that someone can help.

I created two Access databases -- frontend and backend. Both files are
stored on the network and there are over 30 end-users using the databases
concurrently. We're having a performance issue being extremely slow, so I've
decided to try out MySQL as the backend database. (I've read quite a few
positive feedback about this.)

I downloaded MySQL to my local station at work, created the tables, and
installed the ODBC driver from mysql.com. When I try to link the tables from
MySQL to the front-end Access database, nothing happens.

From Access (frontend), I go to File >> Get External Data >> Link Tables >>
Files of Type = ODBC databases() >> Machine Data Source tab >> Select MySQL
Databases >> click OK. Then Nothing. I'm not given the option to choose the
tables from MySQL database.

I have a feeling that I need to have a dedicated/shared server for MySQL
database to get this to work. Is my assumption correct? Usually I get
prompted to enter a userid and password at the SQL Server Login window. But
I never got this far.

The reason why I didn't have MySQL installed on a server is because I wanted
to learn MySQL and test it before I have anything installed on a server. Can
someone please tell me what is the proper way to do this? I want to use
MySQL to store the data and link the tables to MSAccess as the frontend.

Thanks,
Nyla
 
T

Tim Ferguson

frontend and backend. Both files are
stored on the network

I think this is the problem. Access does not work well sharing an MDB
containing GUI features: it's slow and prone to corruption. Working across
a network puts in huge delays. The thing to do is to copy the Front End
onto everyone's own local drive: then it'll be as nippy as you like.

The above does not, of course, apply to the back end, which does indeed
have to be a single file on the network server. In this use, though, it's
really pretty quick and stable up to twenty or so concurrent users.

The move to a proper client-server/ ODBC may help a bit, particularly with
stability if you have more users, but you'll need to spend time adjusting
your code to make the most of it. And of course, you have a whole new
application and language to learn.

Best of luck


Tim F
 
G

Guest

Thank you,Tim.

I should've included this in my previous posting, so I apologize for not
doing so.

I did install the frontend on the users' local machines. The performance
improved a lot for some, but it still is extremely slow for those users who
are in a WAN environment. (We have 8 offices spreadout in various cities.)

MySQL or SQL is a whole new beast for me, but I don't want to put in a lot
of time on something if I can't get connected via ODBC. If I'm reading your
response correctly, it sounds like I need to install MySQL on a server in
order to have a successful ODBC connection?

Thanks,
Nyla
 
G

Guest

Hi Nyla,

I think that you need the old DLL file, version of 4.0.8618. It worked for
me.
 
T

Tim Ferguson

The performance
improved a lot for some, but it still is extremely slow for those
users who are in a WAN environment. (We have 8 offices spreadout in
various cities.)

That's the bit you should have put in! Access will never work
satisfactorily over a WAN because it's a file-share system, not a client-
server. Here is some explanation of why; there are better writeups but I
can't find the address at the moment

http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html


I'm afraid you do need to be looking at a client server setup. You can use
MSDE or SQL-Server Express, both of which are free and work very well with
Access; or you could use MySQL which I am sure would do the job adequately
too (I am learning MySQL/PHP myself at the moment). Depends largely on your
experience and on the expertise you have available to call on, I guess.

Best wishes


Tim F
 
G

Guest

Hi Lois,

I now have the DLL file. Where do I put it or what file path should I save
this DLL file to in my local drive?

Thanks,
Maria
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top