"RB Smissaert" wrote ...

Your queries maybe inefficient or there
maybe something wrong with the VBA code.
If you post the code and queries you might get
a suggestion how to improve the speed.

And post the table schema i.e. it may be flaw in the design.

With a Jet db on a network, you want to reduce the number of data
pages that get sent, so you will have to pay some regard to physical
storage. If you have an integer primary key (e.g. an IDENTITY
autnumber), read on...

The clustered index determines the physical order of the table i.e.
how it is stored on disk (the rows' physical order is the same as
their logical order). Therefore, a clustered index is particularly
efficient on columns that are often searched for ranges of values e.g.
an application that frequently executes a query to retrieve records
between a range of dates. You only get one clustered index per table
so choosing it wisely is one of the biggest performance boosts you can
give to your application.

The obvious analogy is a telephone directory, which is physically
ordered by surname and first names respectively. This is its clustered
index, it couldn't possibly have another clustered index (physical
order).

With Jet, the primary key is always the clustered index, you have no
choice in the matter. One of the strongest arguments against an
IDENTITY/autonumber PK (or a manually-increasing integer, for that
matter) in Jet is that a unique monotonic integer makes a poor choice
for a clustered index e.g. how many times are you going to want to
search a range of telephone numbers?

You can get round Jet's limitation by making the PK a composite of a
more meaningful column and the integer column *in that order*. Note
that with Jet the a table is only physically reordered on the
clustered index when the database file is compacted; while a compact
is pending, any new rows will physically appended to the table i.e. be
in logical but not physical order.

So to rectify, DROP the existing PK, define a new composite primary
key with the first column being your most often used index column and,
if you truly really need it for uniqueness, your integer column last.
Then ensure the clustered index is being regularly rebuilt.

Jamie.

--