Need 4 Speed (1 Viewer)

First up thanks to everyone for reading this and, an extra big THANK YOU to all those that read and helped out with my other questions....

I am still stumped on this one.....

I have written a db for a charity org, with 8 Users (max about 4 at a time). db split into backend and front end, be on a server (XP - Pro) via NT host for login. All other PC are well spec-ed, plenty of RAM and HDD space, 100 mb conections, db size 30mb. and it is slow as a dead snail... any ideas how to speed it up???

I have compacted the db, tried an MBE version, fiddled about with network settings till my fingers were sore... But still like a dead snail when running from the server... Even when only one person is using it!?!

Is there any legs in making it an HTLM?? Or converting it / re-writing in VB... Or going over to SQL..... Help need there, cos I know nothing about SQL...

Only other thing I can think of doing is giving everyone paper and pencils instead....

Thanks in advance........

My friend I feel your pain! I too have run into the same problem as you have (had about 5 users w/ split fe/be and it took MS Access 20 min. to open the db!!!!)

There are things that can be done to increase the speed of the application and there are many, many posts on this topic as well so I would also suggest searching the forum with keywords such as 'speed' and 'performance' -this should yeild a ton of hints and tips to increase the speed of the application.

My first suggestion would be to make sure you don't have forms that use tables are their recordsources. This will absolutely make your application very, very slow - especially if you have a large amount of records or if the network for which you application is residing on is less then stellar (dial-up connections, etc...) This is because in this design MS Access spools and returns ALL of the records associated with the table - even if you plan on using all of the records or not! To keep this from occuring make sure your forms/reports are based on queries and that you use some method to pass an arguement to the form through the queries WHERE clause - if you not familiar with this method search for 'forms based on queries' for help on this. By limiting the number of records Access has to gather the speed of the application will be increased as the db will only have to pull a few records at a time.

Secondly, if this doesn't help and you have the funds - moving the be to SQL Server (or another robust db system like Oracle, etc) should definitly take care of your problem. My application that I spoke of before that took 20 min to open now has no delay what so ever. SQL Server and Oracle dbs are great because they allow you have to the MS Access front-end (which is very users friendly and makes data entry/reporting much easier for users) while still having the robust db (SQL Server/Oracle/etc) as the be to increase speed/serve data faster/and allows many more concurrent users.... but they cost of bunch $$$$$ For more info on SQL Server or Oracle try searching for them in this forum

HTH,
Kevin

Thanks

Hi Kevin

Yea, you are right... I have a couple of Forms based on tables.. Its a medical record db and the users like having all the records at their finger tips... So I will have to go back and re-design them to run from a query.. wot a pain...

SQL / Oracle may be out of the question cos of funds.. being a charity based re-hab unit, all spare cash goes into patient treatment... Only took the project on as a favour.. and it now eat alot of my time....

Thanks again..

This is an issue that I asked about recently, i've found out that turning of the "Name AutoCorrect" features in both the back end and the front end gives a huge increase in performance

Also read the "performance" Q & A here:
http://www.microsoft.com/office/using/column05.asp

Check out some of the excellent replies I got here:
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=47182

Cheers for that, just found a ref to it in the Forms forum as well.. But all help greatly recieved.. By the way, anyone know what does this option do excactly???

Thanks again..

I do know that on Access 2000, it causes reports to come out as landscape instead of portrait! Not sure what its good points are...

Thats not so good unless you want them that way.. wots it do on Access 97?? Hope it makes the tea..

I just saw a post that said moving the db "up" the directory tree on the server dramatically improved performance.

Thanks for that, worth a try.. getting desperate to speed it up... I even move the server.. LoL

The Autocorrect did help, but not enough.. Average time to open form is about 96 seconds.... And to Close the db.. about 210 seconds... Ewwwwwwwwwwwww!

There are several excellent articles on db design and speed here.
http://www.fmsinc.com/tpapers/index.html

Thanks for that tip... some more stuff to try there... Cheers

Just my 2 cents...
Using effects / colors / graphics greatly reduces the performance of a database. So that boring gray with no lines or sunken effects might help a bit.

Jon

Done all that.. but can't remove my tabs as the db uses them to show data of the same type in one location... Thanks for the tip though

bodvoc said:

Done all that.. but can't remove my tabs as the db uses them to show data of the same type in one location... Thanks for the tip though

Click to expand...

Not sure how large the database is but try the following:

Use this on a copy of the database.
Open the copy and rename the autoexex macro so the db would not start automatically and close the db.
Create a shortcut to the copy of your mdb.
Open the properties of the shortcut.
In the target, enter the following:
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\Documents and Settings\yourusername\Desktop\Yourdb.mdb" /decompile
Check the path to access and to the copy of Yourdb.mdb and the name you give to the copy and close the shortcut.
Double click on the shortcut to open the copy of the db.
Open a module and select the menu Debug and compile the database.
Close it and look at the size. It should have decreased a lot.

Jon