Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-10-2003, 08:48 AM   #1
bodvoc
Red Sea Pedestrian
 
bodvoc's Avatar
 
Join Date: May 2003
Location: Dorset, England
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
bodvoc
Question Need 4 Speed

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........

bodvoc is offline   Reply With Quote
Old 05-10-2003, 09:39 AM   #2
Kevin_S
Registered User
 
Join Date: Apr 2002
Location: Lansing, MI
Posts: 635
Thanks: 0
Thanked 2 Times in 1 Post
Kevin_S is on a distinguished road
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
__________________
MCAD .NET
MCP VB.NET
MCP C#.NET
Kevin_S is offline   Reply With Quote
Old 05-10-2003, 10:49 AM   #3
bodvoc
Red Sea Pedestrian
 
bodvoc's Avatar
 
Join Date: May 2003
Location: Dorset, England
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
bodvoc
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..

bodvoc is offline   Reply With Quote
Old 05-10-2003, 11:05 AM   #4
Meltdown
Newly Registered User
 
Join Date: Feb 2002
Posts: 470
Thanks: 4
Thanked 2 Times in 2 Posts
Meltdown
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/...threadid=47182

Last edited by Meltdown; 05-10-2003 at 11:08 AM.
Meltdown is offline   Reply With Quote
Old 05-10-2003, 11:20 AM   #5
bodvoc
Red Sea Pedestrian
 
bodvoc's Avatar
 
Join Date: May 2003
Location: Dorset, England
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
bodvoc
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..
bodvoc is offline   Reply With Quote
Old 05-10-2003, 11:29 AM   #6
andrewf10
Registered User
 
andrewf10's Avatar
 
Join Date: Mar 2003
Location: Ireland
Posts: 114
Thanks: 0
Thanked 0 Times in 0 Posts
andrewf10
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...
andrewf10 is offline   Reply With Quote
Old 05-10-2003, 11:56 AM   #7
bodvoc
Red Sea Pedestrian
 
bodvoc's Avatar
 
Join Date: May 2003
Location: Dorset, England
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
bodvoc
Thats not so good unless you want them that way.. wots it do on Access 97?? Hope it makes the tea..

bodvoc is offline   Reply With Quote
Old 05-10-2003, 05:36 PM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,065
Thanks: 12
Thanked 1,144 Times in 1,086 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
I just saw a post that said moving the db "up" the directory tree on the server dramatically improved performance.
Pat Hartman is offline   Reply With Quote
Old 05-11-2003, 12:16 AM   #9
bodvoc
Red Sea Pedestrian
 
bodvoc's Avatar
 
Join Date: May 2003
Location: Dorset, England
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
bodvoc
Thumbs up

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!
bodvoc is offline   Reply With Quote
Old 05-12-2003, 03:22 AM   #10
Autoeng
Why me?
 
Autoeng's Avatar
 
Join Date: Aug 2002
Location: Kentucky, USA
Posts: 1,300
Thanks: 0
Thanked 2 Times in 2 Posts
Autoeng
There are several excellent articles on db design and speed here.
http://www.fmsinc.com/tpapers/index.html
__________________

Autoeng
Autoeng is offline   Reply With Quote
Old 05-12-2003, 03:31 AM   #11
bodvoc
Red Sea Pedestrian
 
bodvoc's Avatar
 
Join Date: May 2003
Location: Dorset, England
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
bodvoc
Thumbs up

Thanks for that tip... some more stuff to try there... Cheers
bodvoc is offline   Reply With Quote
Old 05-12-2003, 04:50 AM   #12
mission2java_78
Guest
 
Posts: n/a
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
  Reply With Quote
Old 05-12-2003, 05:06 AM   #13
bodvoc
Red Sea Pedestrian
 
bodvoc's Avatar
 
Join Date: May 2003
Location: Dorset, England
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
bodvoc
Thumbs up

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 is offline   Reply With Quote
Old 05-12-2003, 05:09 AM   #14
mission2java_78
Guest
 
Posts: n/a
Quote:
Originally posted by bodvoc
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
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

  Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 05:31 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World