Long long wait for Loading Database Schema in DW mx 2004

On one of my Access tables, which has about 40 columns, I have to wait several minutes! On smaller tables much less. I am on broadband and never had this problem before. The problem is worst  when using INSERT RECORD dialog. Sometimes, after waiting all that time, the column field is still not populated and I have to start all over.

I read old messages here and one person who had this problem was using 10 recordsets on a single page. I am not doing that. Just one recordset.

Does anyone know why this happening?

Could this mean that my Access 2000 database is corrupted? Is there a tweak ?

thanks.

Oh, forgot to say: the database is on remote.
polaatxAsked:
Who is Participating?
 
alexhoganConnect With a Mentor Commented:
That's a little bit of a typo.., :-}

It should read, "Create indexes on key fields".

Creating indexes allows databases to query and sort records faster.
To set an indexed field in Access;
1. Select a field that is commonly searched
2. Change the Indexed property to Yes (Duplicates OK) if multiple entries of the same data value are allowed or Yes (No Duplicates) to prevent duplicates.

This will speed up the query.

You will still take a hit because of the table size. (I'm talking about the number of columns)
0
 
alexhoganCommented:
40 columns in a single table isn't really something that you want to see happen in your database.

However, here is a couple of things that you should look into.

If you haven't already, create a primary key on a unique column.
Create an indexes on key fields.
Normalize your database.
Optimize data type in your database.  Don't store varchar values where int will work.

Remember that Access has a 1 gig limit.  As the size of your database grows, the greater the importance of normalization, indexing and strict datatyping.  Especially as it starts to reach it's limit.
0
 
polaatxAuthor Commented:
Hi alexhogan,

Could you please explain what you mean by "Create an indexes on key fields." ?

This is an Access 2000 database, so I don't think the varchar example applies to me. I am using memo only when I really need it and otherwise it is text.
0
 
polaatxAuthor Commented:
thanks for the explanation of the indexing. I checked all the userID fields in every table are already set to be indexed. I guess Access did it by default.

I didn't find a index setting the queries. Can you set the index for those too?

Additionally, is there a way in ASP VB to put the contents of a recordset in some sort of already-compiled format that the application can pull immediately? I am thinking perhaps something like a stored procedure that already has the most requested data ready to go.  I am calling on a couple of tables that contain search categories from sidebar of every page on the site.  (If you want, I can post this question as a new question).

I am very concerned about what you said about 1 gig limit on Access. My database is 932kb (and it doesn't contain any data!). It will be much more, especially because it has memo fields that will contain text for entire pages.

Here's the database. If you can look at it perhaps you can tell me why it is so big already: aasg.brinkster.net/database2/aura.mdb  
(If you want, I can post this question as a new question).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.