Speeding up Access 1

[dvannoy]

Is there a way to tweak access so it runs a little faster then the default settings???

My server is a novell server PII 650Mhz dual processors with 1/2GB of memory. all my workstations are PII or greater..

in this database there are less then 20,000 records..

will access get slower with more records or can I speed up the search engine a little?

Thanks DVannoy
A+,Network+,CNA
dvannoy@onyxes.com

 

[Philly44]

If your running this db through LAN shortcuts on users desks your never going to get it run much faster. Access is not a very good network app.

 

[dvannoy]

Will SQL server be much faster?? DVannoy
A+,Network+,CNA
dvannoy@onyxes.com

 

[Databaseguy]

Are you sharing the App from the network, or do you have a front end/nack end setup ???

I've heard SQL is faster. But can't verify...

 

[dvannoy]

I am sharing it from my network...have not split the database... DVannoy
A+,Network+,CNA
dvannoy@onyxes.com

 

[tlbroadbent]

Splitting your database would be a good first step. It is essential for adequate performance. Here are some links with additional performance optimization hints.

http://www.microsoft.com/accessdev/articles/v35perf.htm

http://support.microsoft.com/support/kb/articles/Q112/1/12.asp

http://support.microsoft.com/support/kb/articles/Q208/8/58.ASP

I suggest searching the Microsoft Knowledgebase for Access articles using the keyword 'optimize.' You'll get a lot of articles dealing with query perfromance, faster combo and list boxes, fater form and subforms, etc. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[dvannoy]

This Database is not fully operational yet..some users are using it but not all depts. so I probally should split it before it gets fully rolled out.

My question is, what problems should I look for once I split the database??? I know better to ALWAYS make a backup before doing any thing serious like this..

But once it's split will all code etc.. work the same or will I run into problems???

Thanks DVannoy
A+,Network+,CNA
dvannoy@onyxes.com

 

[tlbroadbent]

Check these links for some guidance in splitting a database and what to look for.

http://msdn.microsoft.com/library/en-us/odeopg/html/deovrthetwodatabaseapproach.asp

http://wa.apana.org.au/~abrowne/ser-01.html

http://www.geocities.com/SiliconValley/Code/5046/access2k3.html

Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Databaseguy]

I, for one, just found out if you're going to use the seek method in your code, that apprarently a split database doesn't support the index property of the recordset.

 

[MichaelRed]

Hmmmmmmmmmmmmm,

For ONE, the 'Seek' issue is NOT quite that simple. You cannot use the 'Seek' on a n'on-native' table -AND- Ms. Access considers all 'Linked Tables' to be non-native, even if they are just in a different ".MDB" file. However a Table in an 'OpenDataBase' object may be considered as a native db, and thus the seek method will work on these tables. On the other hand, If you are using the seek method, you know wnough about the record(s) you are accessing to generate a recordset of the records (via 'query') and thus the whole issue should be a non-starter in the concerns race.

Another whole universe of issues is certainly addressed in the numerous references provided by Terry, I would urge you to thoroughly review these.

One aspect which I feel is not well covered is the actual planning estimates for the load. Do you HAVE projections of the recordcounts for the various tables? Do you HAVE a head count of expected users? Do you HAVE any control over the 'growth' of the number of users? Do you have db security in place. As the Amex Ad says "DON'T LEAVE HOME WITHOUT IT". Even if the "boss" says 'no' -"DON'T LEAVE HOME WITHOUT IT". Check, ctrosscheck, recheck and double check with the net cops in your neighborhood. MAke sure your app settiings and the net settings are properly setup and coordinated and optimized for database applications. The default settings for almost ALL networks are targeted towards the generic office suite. File sharing for (small) documents, a few spreadsheets, some presentations. Printer sharing, e-mail and often for internet/intranet 'surfing'. NOT database operation. It IS different. Most of the local 'net cops' I have met do not even know there is a difference, many do not know what 'settings' are available. You need to tread lightly on their toes in most companies (they are generally considered 'better' than you)-BUT you do need to tread on their toes, at least until you are sure they KNOW about the differences.

More on the network situation. If AT ALL possible, any ane every non-trivial db should be on some server which DOES NOT deal with e-mail and or internet access!!!!!

While not explicitly part of the sharing process, you should carefully check that the db is reasonably normalized and that proper relationships are established before 'going public'.



MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over