HELP!!! NEED SPEED UP ACCESS DATABASE.

[AnnYH]

Hi, Experts:

Are there anyone can help me to solve this problem?

Our database is growing up to 42K records on main table. When we open a form it takes a long time (about 1 minus). Even I only need 6 records to show on the screen. The user keep complain. The boss asked me solve it ASAP.

Right now we are using front-end and backend at Access 2000. Main database .mdb is on the server. Application .mdb is running on client side and link the table to server.

I need every idea to help me.

Thx.

 

[JesseL]

AnnyH,

Hello AnnyH. I am no expert, but you just might have to "Compact and Repair the database". If you know about this, I am sorry, but im just trying to help Go to tools; Go to Database Utilities; and then click on "Compact and Repair Database". You should "Compact on Close" also. This will compact and repair the database whenever you close it out. Go to tools; click options; click on the general tab; and check the "Compact on close" check box. HTH
"Anyone who has never made a mistake has never tried anything new".
Albert Einstein (1879-1955)

 

[Garridon]

Other things that might help:

* Put as much of your code into a module, not on the forms. That way, it doesn't have to load when the form opens.

* If you have the Name Autocorrect log tracking turned on, turn that off and delete the table. I had a 15 meg database that went down to 5 meg when I did that.

* If you use the form templates with the pretty graphics, switch to a simple color. Those take up a lot of space and may contribute to load time as well.
Linda Adams
Linda Adams/Emory Hackman Official Web site

http://www.hackman-adams.com

Official web site for actor David Hedison:

http://www.david-hedison.com

 

[cascot]

AnnYH,

Is your form a continuous list form? What do you have the Record Source of the form set to?

When you say the form only needs to display 6 records, what is the criteria? Is it the most recently added 6 records, or maybe the 6 records with the highest sales value?

 

[AnnYH]

Hi, Friends

I¡¯m so happy; lot of people likes to help me.

KSharp, Garridon . Thanks all you response me.

Maybe Cascot can help me. Thank you. Can you tell me more?

My main form is single form, subform is continuous form.

My criteria is OrderDate=date(), only showing recently order entry .

Is continuous form effect the speed or subfrom?



Thanks again

AnnYH

 

[cascot]

AnnYH,

Are you specifying that crieria within a query or are you specifying a criteria within the continuous list form?

If you are doing the latter, depending on exactly how you are doing it, it's qiote possible the form will be retrieving every single record from your table THEN applying the selection criteria to that recordset, before eventually displaying the small number of records that meet the criteria.

What you should do is create a Select type query which returns the fields you require from your table and has a criteria against the OrderDate field of Date(), then specify that query as the Record Source of your continuous list form.

Let us know how you get on.

 

[Zeroanarchy]

I do not know how your system is setup but you may want to look at splitting the database a second time perhaps add new field to your form that renders the record historic or current if the box is select it transfers the data across to a new table on exit of the form if current remains where it is, this way you will filter through less records.

Hope that is of some help to you, you could also look at splitting your form into more tables rather than just the one.

Zeroanarchy Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[Paddyo]

Make sure that you have indexes setup - Particularly for the date field in your criteria.

Also make sure that in your queries or sql statements that you are only selecting the fields that you require.

Hope this helps
Paul

 

[Guest_imported]

Thx for every one that response my question.

For your suggest I have done before. Currently it seems only way we can do to migrate our Access database to SQL server. The file size of our database (only data no include application) is almost 65MB and over 30 users. I think Access 2000 can¡¯t handle it well very. So if some one has same situation, probably need to think about SQL server.

I know I¡¯ll have lot of things need to do for this change.
I¡¯ll ask your helping again. Thank you so much.


AnnYH

 

[Guest_imported]

For a simpler fix try opening your code and save; then open your form in desing view and save. I ran into that problem with XP and for some reason if open anything in design or code and do not save, my app slows down even when size is not an issue.
Good luck