Adding very little slows database lots

[M Skabialka]

I added a new form and report to a database, and now it opens very slowly.
The form and report were simple, based on one table.
When the db opens the entire Access space turns white for several seconds,
but eventually the main form of the database shows up.
I have decompiled and recompiled, done compact and repair several times,
etc.
I even took an old copy of the database and imported the new form, report
and query for the report, and it immediately slowed down when I reopened the
database.

The database is about 2 GB, the backend less than that.
What diagnostics can I run, or other repair technique?

Mich

 

[Jeff Boyce]

Mich

"... the db is about 2 GB ..." ?really? The limit (for newer versions of
Access) is 2 GB. Did you mean 2 MB?

Where's the back-end located? How do you connect to it (?via LAN, WAN,
wireless, ...)?

Is your form "loading" the entire table (i.e., what's the record
source/query for the form)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[M Skabialka]

Sorry - 2 MB, not 2 GB. We seldom work in MB any more - GB is to the
forefront in sizes of everything!

The table has only 3500 records, with about 30 fields. The form is unbound
where the user enters info about a new record. When they click the save
button, SQL is run to insert a new record. There is a list box that fires
up when the main data item is entered to let the user know the status of
other copies of that item. At most it shows about 15 records, 5 columns.
The report shows the info the user just entered, plus the items from the
list box. So neither is loading a whole table.
The table is on a back-end server, accessible through the LAN.
Before adding the form and report the database opened very quickly. Now
Acccess opens, the entire screen turns white for several seconds, then
Access shows the database. This is an Access 2003 database running under
Access 2007. New modifications are done in Access 2007.

Mich

 

[Jeff Boyce]

Slow performance comes from:

* not having a 'live' connection to the data/back-end. If your form is
unbound and your application has to re-establish a connection to the
back-end every time you start it up or do something, that could explain the
problem. Tony Tawes' website has some suggestions for how to hold a
connection open. (And why bother with an unbound form?)

* not all network "pipes" are created equal. Is there a possibility that
the slow down and you adding a new form are coincidental, and the problem is
related to network performance?

Just a couple more ideas to consider...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[M Skabialka]

If I open the previous version without the new form and report, it opens
quickly. I then open the new version and it 'whites out' and is slow. I
de- and recompiled, and compacted it to no avail.

This new form is not the main form to open when the application loads, so
until this form opens the database wouldn't even "know" whether it is
unbound or not. The main form opens a table which stays open no matter what
else is done in the application.

The only thing I haven't done is convert the database to Access 2007 - it
was written in Access 2003, and is modified and used in Access 2007. Users
are probably unaware of the .accdb file extension, so haven't introduced it
yet. Would converting it 'clean it up' and get rid of any sludge that may
be slowing it down?

Mich

 

[Jeff Boyce]

I can't categorically state that clean up would speed up, but I've noticed
better performance after I clean up apps.

Remember to back it up before using Compact & Repair.

The other "fix" I've used, if something might be corrupted, (and again,
after backup) is to create a new empty database and import all the pieces.
Sometimes this eliminates hidden junk.

And there's a "decompile" flag that you can use, probably as a last resort,
to attempt cleanup -- backup, backup, backup!

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP