Thread: Multiuser Environment - ODBC
01-12-04, 12:08 #1Registered User
- Join Date
- Jan 2004
Unanswered: Multiuser Environment - ODBC
got a long lasting problem here and would really appreciate a hint from someone:
Access 2000 "Database"
1 main table with some 240 fields
1 Form with some 240 controls, amongst which many combo lists fed by other tables and some 30 combo lists fed by an external *.mdb on the same computer.
4 Clients W2000P
Problem Description 1:
The database was originally done to run on just one machine and worked just fine. Now it has to run in multiuser environment and problems started out when the form took forever to open on the clients machines ..sometimes forced machines to crash. Time needed to open form varied on client's boxes .. from slow to very slow to not at all.
Resolution attempt 1:
Setup a fresh database
Installed latest MDAC components and latest Jet Engine Service pack on all clients + server.
Setup an ODBC connect to the database file on all clients.
Result: Perfect .. form opened instantly on all clients ..no delay whatsoever ))
Problem Description 2:
Now the issue turned on securing the database in concerns to more than onbe user using it at the same time + forcing a login prompt upon opening.
Resolution attempt 2:
Created new workgroup file using wrkgadm.exe, did all needed in concerns to permissions. (deleted Admin from Administrators.. took all rights from the user group and setup a new group with my 4 clients inside with the appropriate permissions + distributed *.mdw file to all client boxes + joined workgroup locally on every client box)
Result: Concerning Login and the workgroup file everything is just fine... BUT ... the form again has turned terribly slow .. on the fastest client some 15 seconds ..on the slowest client som 1.5 Minutes to open ..if at all. ((
So now I am out of ideas what to do.... the database is not split into front- plus backend .. but I don't think it it's necesssary as it opened perfectly speedwise until setting up the new workgroup file. Only thing left might be some ODBC settings about which I don't know too much ...
Anyone has any idea ? Any help would be really appreciated...
01-12-04, 15:29 #2Registered User
- Join Date
- Oct 2003
Where are keeping your security file. If its on network then everytime a user starts the app, login/password has to be authenticated over the network. So try breaking your app into FE/BE with FE on each individual pc and BE in the same directory as the security file (on the server).
You'll be amazed how trivial things like that can have a huge impact on performance.
01-13-04, 02:27 #3Registered User
- Join Date
- Jan 2004
Thx for the reply ..
The Security Files I assume you refer to as the *.mdw files. They are not on the network ...rather local on each client machine. So that cannot be the cause. The entire security process doesnt seem to be the problem in the first place as the login prompt and the initial opening of the database goes very fast and smooth .. problem only comes when opening that big form ...
Concerning FE/BE ... it does not seem logical to me that this could improve performance as the form would then need to get all the data from the network, which could only slow up things more (or am I seeing something wrong here?)
I feel it must have to do something with Network connection, ODBC, or some other settings ... hmmm..