Access 2000 performance issue

[moolie]

Hi.

I've got a rather complex database residing on a Advanced Server 2000 dual processor loaded server that the users connect to using a the front end on thier local machines. Access 2000 with all the updates etc is loaded on each client machine. Two issues here:

1. If more than one person starts the program all users experience very slow loading of data, loading of reports, loading of forms etc. (I'm talking 2 minutes to wait for a drop down box to display the data).

2. Also, when the user, multi or single, opens anything or runs anything the client machine local resources max out 100% and locks the entire machine for up to 5 minutes. Sometimes this is faster but generally very slow to load anything. We have done some ram upgrades on two of the machines to see if this will fix the problem (I'm confidant that it will not).

Any suggestions from you pro's about what is going on here and how to speed things up (even just a little performance increase from the app and my users will be very happy).

 

[moolie]

I've done some reading regarding my problems and I have looked at some of the code involved and I have pinpointed some coding issues that are causing some of the problems.

Variant datatype will be replaced with explicit datatypes ie. date, currency, string etc.

Open connections, close connection, connection = nothing. I havn't checked my code for this yet as there are many many connections generated by the VBA code throughout the app. I'm pretty sure there are some connections that are not being closed.

Access 2000 loads all forms and code on startup of the application taking most of the system memory for hidden items. Is there anyway to open and close these items as needed? or am I stuck with everything open at the same time (hidden)? If so, if I move the code modules out of the from end into another mdb and link to the modules as needed, will this work? is it even possible?

 

[LarryDeLaruelle]

One of the biggest drags on Access 2K is the Autocorrect feature.

One poster (wish I could remember the name) posted a thread recently on the issue. Unchecking all Autocorrect (track name, etc) will improve response times.



Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[moolie]

I'll try that as well.
Thanks.

Any other suggestions would be great.

 

[GingerR]

make sure any fields that you search or do calculations on are indexed.

in each table, make sure SubDatasheet is set to NONE. it's automatically set to AUTO when you create a new table. made a HUGE diff in my speed. dumbest thing i ever saw MS do.

 

[Philly44]

The main problem with this type of a situationis that Access is not really a very good Client/Server type of software. At least not if the front end is attahced to the backend via "Linked" tables. In that type of situation you may be better of using ODBC or something like that. Where I am we use a hoot load of Access db's and publish over the our WAN using Citrix. You may want to look into a few other options about distributing this app.

 

[JasonPurdueEE]

One poster (wish I could remember the name) posted a thread recently on the issue. Unchecking all Autocorrect (track name, etc) will improve response times.

GingerR reccommended that to me near the bottom of this thread and it shaved about 7 seconds off my report time. very good tip GingerR, thank you!

thread703-594104

______________________________
Sleep is a poor substitute for coffee.

 

[moolie]

AutoCorrect works fine. Is there an easy way to disable it via code?

 

[GingerR]

i put into main (startup) form's OnOpen event:

Application.SetOption "Log Name AutoCorrect Changes", False
Application.SetOption "Perform Name AutoCorrect", False
Application.SetOption "Track Name AutoCorrect Info", False

 

[FancyPrairie]

You could also try using the undocumented /decompile command-line option. Should make your database smaller and run faster. To use it, run access from the command line using the /decompile switch (i.e. \path\Msaccess.exe \path\Yourdatabase.mdb /decompile). If you have an AutoExec macro, rename it temporarily (don't want it to launch at this time). After the database is up, compact and repair it. Then save your database and quit Access. Start Access again, load your database normally, and compile your project. Quit and restart Access. Your database should be smaller, faster and ready to run.

 

[moolie]

Nice!

What about the way access 2000 loads all of the forms etc when the application starts? Also, I have been reading about how access manages memory and how no amount of ram will solve some of the issues this raises...

If I converted to Access XP... does XP handle memory the same as 2000? XP is reverse compatible so I could convert and do some testing but was hoping someone has already done some testing on XP.

 

[psemianonymous]

I think the link says it all:

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

A bunch of assorted tips on speeding up your database.

 

[GingerR]

XP is just Access 2000 rev B so no great improvements there...

 

[moolie]

foolio12, thanks for the link. lots of really good information there. I'll post again after I've had a look at my app.

Probably tomorrorw, thanks again all for the help.

 

[AvGuy]

Another technique for speeding front/back end applications is to create an empty table in the back end. At start-up connect to this table using VBA from the front end and leave the connection open during the session. This prevents the front end from severing and then re-establishing the front/back end connection numerous times during the session.

AvGuy