Access2k Form taking 5 minutes to open over network

[rozuki]

Hi

I have a database with a main data entry form consisting of approx 20 fields, a tabbed control with 5 tabs and 8 subforms (3 of the tabs have one subform and 1 tab has 3 subforms and 1 tab has 1 subform). I am using replication (which works fine).

The database has 12,000 recs and the form opens to the first record in the database. (There is no sorting on the file)

Testing it on Windows 2000 server the form takes a few seconds to open. Testing it on Windows NT 4.0, the form takes anywhere from 2 1/2 to 5 minutes to open!!

Tried increasing page sizing files (on pc's and server), but there was no difference. Have the latest Jet 4.0 with service pack 8.

I need to find a way to resolve this as it is a major slowdown for the users. Anyone else had this issue or know how to solve it?

Thanks,

Rosanne

 

[rdodge]

Okay, not sure what all you have done, but 2 things comes to mind.

Item 1, if you haven't already done so, you need to split your DB file into 2 parts (FE - Front End and BE - Back End) This will help in not only the efficiency matter that you are having, but it will also help by reducing the chance of the DB program getting corruptted. Access has an Autosplit program under the Tools, Database Utilities

Item 2, your DB needs to be Compiled for the code you have behind the scenes, should you have coded it. You will then need to compact it. This is also under Tools, Database Utilities. It's also been suggested that you convert a copy of the DB to MDE and have that copy on the server for the users to download onto their local PCs. By them downloading a copy of that file onto their PCs, it also helps in the prevention of DB file corruption.

The conversion to MDE is just so the users can't go into any of the design stuff and change anything in the DB file, especially the code as all code editing stuff has been removed from a db file that has been converted to MDE.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[rozuki]

Ronald,

Thanks for the reply...I have it split FrontEnd/Back End...and compiled/compacted and converted into an MDE. I have 20 users accessing the MDE which sits in root of folder on the network server along with the BE. I have tested that the speed of the program is much faster when the MDE is placed in a local folder on each user's pc (several seconds vs minutes). What am i trying to find out is what it is regarding connection, switch, hub, router, patch panel, wiring that affects all users running the same FE from the network server .....

Any ideas?

Thanks,

 

[rdodge]

When everyone is accessing the same FE on the server side, it is most definitely a network congestiveness issue that you have to take into account, not only for openning and closing, but also during the time it's in use. The more stuff you have going on in the FE part of the program, the more it's going to clog the network system. Not only that, but there has been stories of the FE corrupting cause of so many different users trying to do different things, which in turn can cause code conflictions (macros or code). For these different reasons, that's why it's been suggested that the FE are loaded onto the individual users' PCs.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[rozuki]

Thanks for your input! The issues I am dealing with are: this app was converted from Access 97 where it ran OK with the FE on the network (WinNT) being accessed simultaneously by 20 users.

Since converting to Access 2K, even if i put the Front End MDE local on each pc....it still is taking minutes to load that form instead of seconds...? These users are all on WinNT 4.0 server.....

Yet, if I run it local from our Win2k server...the form opens in seconds as it should.

Thanks for your input!

 

[faeryfyrre]

I guess the most obvious question is: Does the WinNT4 server have a 100mb or 10mb ethernet card?

I agree with rdodge in that opening the FE over the network will increase network congestion quite significantly.

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[rdodge]

After converting, did you follow through with decompiling, then recompile the code? There is a series of steps to be done for this, as explained in the book, "Access 2002 Bible" by Michael R Irwin and Cary N. Prague.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[evalesthy]

Check out this link:

http://www.granite.ab.ca/access/performancefaq.htm

 

[Eupher]

Another thing to check is Tools->Options, then the "General" tab - If Name AutoCorrect is enabled, this can slow down form loading *dramatically*.

Ken S.

 

[67peshawar294]

rozuki,
Don't know if this is revelant or not but you might try it
thread702-206410
jim

 

[rozuki]

Evalesthy,

Thank you! I had downloaded that information about a week ago...but of course did not read it carefully! I went back to it and found that Tony Toew's had provided me with an answer to the slowness problem. I created a table with two fields....an autonumber and a text field. It didn't matter what was in it...I just needed it to be the source behind a form which I open through autoexec macro when application opens. This form is kept hidden and maintains a persistent connection to the database so that the ldb file is not checking things over and over and slowing things down! My main data entry form now takes anywhere from 1 - 2 seconds to open!!!!! It was taking 2 1/2 minutes previously.....

Note: One thing I also had to do was add DoCmd. Close code that closes that hidden form....to my command button that closes the application, otherwise you receive an "Object variable not found" message.

Thank you to all who responded to this question! I really appreciate the help...

Rosanne