After compacting my database, my queries are taking much more time

[Guest]

I'm using an Access 2000 database, and another mdb file, where I'ev got
froms, scripts and queries. I've made changes (using Access 2002) on the
table database (adding elements to tables and 1 field to 2 of them).
Evertyhing is working fine, until, I'm compacting the database. What happens
is that the queries are taking much more time to be executed (25s instead of
3s). Does anyone have an idea of what this can be due to?

 

[Allen Browne]

Start by working through the list of issues that Tony Toews has at:
http://www.granite.ab.ca/access/performancefaq.htm

Particularly, the Name AutoCorrect and SubdatasheetName issues are crucial.

If the problem persists after working through those issues, open a problem
query in design view, and make some change, such as deleting a field and
adding it back. Save the change. This will cause Access to recompile the
query. The compilation time is usually very minimal, so should not make a
big difference, but see if it's a factor.

 

[Guest]

I've made those changes but nothing changed at all. The Query compilation
from my point of view should not be involved, because they are defined in
another mdb file. The database that generates problem is only the tables one.
Any other idea?

 

[Vincent Johns]

I've made those changes but nothing changed at all. The Query compilation
from my point of view should not be involved, because they are defined in
another mdb file. The database that generates problem is only the tables one.
Any other idea?

[...]

You might try going back to a copy of your last version that ran well
after compacting, and make changes one by one (or a few at a time), to
try to determine which change caused the problem.

A similar approach, again starting from a copy of the last good
compacted version, is to import from your new, slow version some of the
changed elements, such as tables. If you find, for example, that there
is just one table that causes problems, reconstruct that table,
importing only some of the fields into it from the new version,
compacting and testing each time, to try to determine what caused the
problem.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

I've already try this, but I was not able to find the "real" cause of this.
What is very strange, is that sometime it's working fine, sometimes not,
using the same database.
It seems to be a random problem.

I've made those changes but nothing changed at all. The Query compilation
from my point of view should not be involved, because they are defined in
another mdb file. The database that generates problem is only the tables one.
Any other idea?

[...]

You might try going back to a copy of your last version that ran well
after compacting, and make changes one by one (or a few at a time), to
try to determine which change caused the problem.

A similar approach, again starting from a copy of the last good
compacted version, is to import from your new, slow version some of the
changed elements, such as tables. If you find, for example, that there
is just one table that causes problems, reconstruct that table,
importing only some of the fields into it from the new version,
compacting and testing each time, to try to determine what caused the
problem.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Allen Browne]

I did not understand:
The database that generates problem is only the tables one.

Does this mean you have a split database, where both the front end and back
end contain queries, and the front end works fine, whereas the same query in
the back end does not?

 

[Vincent Johns]

I've already try this, but I was not able to find the "real" cause of this.
What is very strange, is that sometime it's working fine, sometimes not,
using the same database.
It seems to be a random problem.

Woopsie! Now it appears that Access may possibly not even be involved.
You might consider using Windows Task Manager (which you can conjure
up via control-alt-delete) or a similar diagnostic tool to attempt to
see if any other process or application is hogging your system at the
slow times.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

In fact, the front end contains only tables.
The back end, contains it own tables, forms, queries.

What I'm compacting is the front end table.
I think that the back end is not involved, because, it's working fine with
an uncompact database.

My system is ok, I mean, no other software accessing to the databases, or
slowing down the computer.

 

[Allen Browne]

That makes no sense to me at all, so I don't think I can help.