?
Solved

Long long wait for Loading Database Schema in DW mx 2004

Posted on 2004-08-11
4
Medium Priority
?
137 Views
Last Modified: 2010-04-25
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.
0
Comment
Question by:polaatx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 8

Expert Comment

by:alexhogan
ID: 11773864
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
 

Author Comment

by:polaatx
ID: 11778731
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
 
LVL 8

Accepted Solution

by:
alexhogan earned 2000 total points
ID: 11778847
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
 

Author Comment

by:polaatx
ID: 11805804
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question