1 user great, more than 1 . . . nightmare!

[proximity]

Hi,

Hope someone can help me with this as it is driving me nuts!
I have an MS Access 2002 front end connecting via ODBC to MySQL 5.1. DB is fully normalised, indexes all fine and dandy etc.

Question is this: I have 10 users, each with their own PC and Access front end connecting via an ODBC link to the db. If all users have the DB open, but only one user is actually entering data or running a report, it is a thing of beauty. Runs ultra fast.

If another user decides to do something, the system slows to a crawl. When the application was 100% Access, all users could do stuff simultaneously (except editing the same record, of course!) with little or no speed penalty.

I presume I am missing something obvious, but I don't know what! I can't migrate it to a web solution until the hybrid access/mysql solution is working satisfactorily

Any help greatly received . . . .

 

[DonQuichote]

Does Access use long transactions? If you enable the query log on the MySQL server, you can see what Access does exactly and see if you can change settings to speed things up.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[hdingman]

Just a guess, but it sounds like Jet/ODBC may be issuing a "Lock DB" (request exclusive access) instead of a record lock. It depends on the programming and client settings on the Access side.

Frequently, Jet "does not work and play well with others.