Form opens very slowly

[Guest]

I have a very straight forward DB with 54 Yes/No tick boxes and 36 text or
Lookup fields from a single paper form. To make data entry easy I have set
up a form with 3 pages incorporating all the Fields on them in groups so when
entering the data we can Enter or Tab to the required field(s), space to
select and press Pg Down to go on to the next Page. (I am currently altering
this to use Tab Controls) The problem I am currently having is that either
way (all pages together or with Tab Controls) it takes 30 to 45 seconds to
open the Form for entry and the same to go to Design mode and when saving.
We currently have 6300 records, but when I copied the Table using only the
Structure and no data it took the same time. Arrhg!

Any ideas on why so long?

Thanks for any help -
Kernow Girl

 

[Allen Browne]

Several aspects here.

You can probably solve the immediate problem by working through the items in
Tony Toews "Access Performance FAQ" at:
http://www.granite.ab.ca/access/performancefaq.htm
Particularly, note the issues of Name AutoCorrect, Subdatasheet Name, and
also the length of the path name if the tables are attached.

More fundamentally though, this is a flawed design. Insread of dozens of
yes/no boxes, you need a related table with a *record* for each of those
choices that do apply.

 

[Guest]

Thanks Allen - I've already de-selected the Name AutoCorrect but will read
the FAQs and see how I get on.
Dika -KG

 

[Jeff Boyce]

I'll echo Allen's comment -- any time I see repeating fields (Date1, Date2,
Date3, ... Y/N1, Y/N2, ...), I see ... a spreadsheet! Access is a
relational database, so to get the best out of it, you need to consider
relational database design/structures, normalization, etc.

From your post, it sounds like you took a paper form and created a table to
match it. Again, this approach works if you are using a spreadsheet, but
only makes more work for both you and Access if you try that in Access.

Good luck!

Jeff Boyce
<Access MVP>

 

[Guest]

Thanks for your response Jeff, and I do agree with both you and Allan about
the basic problem with the structure of the DB itself. However I have a
problem - the nice young lady who works with the data knows very little about
Access. I have trained her to set up and use Queries, but she sill has not
quite grasped the concept of Reports (!) so takes the printout from her query
and COUNTS the number of records. I have managed to explain the concept of
(at least) pulling the data into Excell, which she has a somewhat larger
knowledge of, but even this is difficult for her. She goes a bit blank when I
thy to explain linked files. Plus I only work part time, and as this is a
local council, by the time I got her to the degree that she could understand
she is off and away to another job. Sigh. So the closer to Excell it is, for
us, the better. But thanks every so much for all your input. Yours - Dika
(Kernow Girl)

 

[Jeff Boyce]

Dika

If you get the opportunity, you can:
1) create the tables with a more normalized structure
2) load the data to this structure
3) create one/more queries that "flatten" (denormalize) the data, for use
by the person

It isn't necessary for the end user to understand relational db design.
This can be "hidden" through queries and forms that look more like what they
are familiar with (?!spreadsheets).

Best of luck...

Jeff Boyce
<Access MVP>

 

[Todd K.]

WOW! Thanks Allen, I had seen this link before but there was so much
information on it that I got about halfway into it and (having no luck) quit.
I tried the AutoCorrect fix you recommended and went from a form taking 60
seconds to load to it taking 1-2 seconds. - Todd K.