Access & ODBC painfully slow

[John B. Smotherman]

I'm trying to migrate an Access back-end to MySQL, and use my existing Access
front-end to connect via ODBC.

As an initial experiment, I've created a sample back-end with some of my
smallest tables in it (exported from Access via File | Export on the main
menu), and created a new front-end linking in those tables. The smallest
table (2 fields, 13 records) takes almost ten seconds to open in table view.
Another small table (5 fields, 20 records) takes fifteen seconds.

My question is:
Is this slow response indicative of a configuration problem in Access (I'm
running Access 2003 SP3), or the ODBC connector (v3.51.22), or is it just
something I'll have to live with in this arrangement?

Thanks.

 

[Golfinray]

I use an access front end and sql server with an ODBC connection. My tables
open almost instantly. It could be that you have server problems or not
enough memory. Look at some of Allen Browne's solutions for slow operation at
WWW.allenbrowne.com

 

[Albert D. Kallal]

As the other poster mentioned, I find the linked ODBC open in a instant, and
are very fast...

a few things to check:

I assume that the front end is being placed on your local computer, and
therefore, there are not other users accessing that same front end at the
same time?

What kind of connection speed are you talking about, are you talking about a
local area network, or we're talking about when or over the Internet type
connection (wan). but when is typically 100 times slower, and thus you will
expect some differences in speed.

My question is:
Is this slow response indicative of a configuration problem in Access (I'm
running Access 2003 SP3), or the ODBC connector (v3.51.22), or is it just
something I'll have to live with in this arrangement?

I suspect you have some connection or some setup issue that is incorrect, as
both me and the other posters stated that we have no problem, and forms open
in an instant for us without delays..

 

[John B. Smotherman]

Yes, the front-end is on my local computer. This is still a development
process, so there are no other users accessing the front-end, and no other
users accessing the same back-end from the server. The server is in the same
building and is connected via LAN.

 

[John B. Smotherman]

I looked at Allen Browne's site but could find nothing on slow performance.

I have already turned off Name Autocorrect and table subdatasheets. There
didn't seem to be any difference in speed from before I turned these off,
though.

 

[Larry Linson]

Are there other ODBC drivers available for MySQL? That'd be worth a try...
the ODBC standard is for connectivity, and, AFAIK, there's no speed/response
requirement they have to meet. I've had good luck with ODBC drivers from
various sources with various versions of Access and several different server
DBs (but haven't used Access and ODBC with MySQL).

There are many factors that affect performance. Particularly if your tables
are large, opening in table view (which will, at least eventually, retrieve
all records) is going to be just about the slowest way. If you are
comparing it to opening the same table in a local Jet database, yes, it is
going to be slower using ODBC across the network.

Larry Linson
Microsoft Office Access MVP

 

[Paulino Padial]

new version of mysql odbc connector ( v 5.x.x ) increase perfomance, try it


regards