Re: Opening table - query running?

I just told you. It is undefined. Jet retrieves rows as convenient.
Normally, the rows will be returned in primary key sequence because Jet
physically reorders every table when the database is compacted.. But if the
table has been updated since the compact, the key sequence is not
guaranteed.

"patti" <patti@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D7F65A3F-71B8-4789-87DA-1D7C2BD418E9@xxxxxxxxxxxxxxxx
Hi Pat-

Thanks for the help. I have never used orderby prop on tables - always on
queries & reports.

Out of curiosity, how does access decide the order of a table for
datasheetview?

thanks,
patti

"Pat Hartman (MVP)" wrote:

Just because you don't see a query running message doesn't mean there
isn't
one. See my earlier responses. Especially the one to "Steve".

As I said in my first post, removing the order by should improve
response.
Without the order by, the table will be returned by the query in an
undetermined order. The order will be approximately key sequence since
Access reorders all tables into key sequence when the database is
compacted.
This fools people into thinking that their data is always stored in key
sequence which is far from the truth. NEVER depend on order unless you
specifically include an order by clause in your query.

"patti" <patti@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6AD5CA70-0469-4314-AFA5-6C230CAD2662@xxxxxxxxxxxxxxxx
Hi Tim -

Removing the orderby property from the table sped up the opening of the
table.
And there was no "query running" message.

Thanks again for all the help & insight.

patti

"Tim Ferguson" wrote:

=?Utf-8?B?cGF0dGk=?= <patti@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
news:2411605C-C504-456D-9D3D-9BC38B8BBE3A@xxxxxxxxxxxxx:


Is there an index on the field you use for sorting? If not, put one
on.

i inherited this db and don't see the need to sort the table itself;
i'd rather remove the table sort and do that in queries. does that
make sense?

Yes it does make sense, but it's only half the question.

Records in a table have to be presented in _some_ order. If you don't
state what you'd like, the most likely order is that in which they are
recovered from disk. In the specific case of Access (or, Jet actually)
those are nearly always in Primary Key order: but this is not
universally
true and it would be a Bad Idea to rely on it.

The lesson, then, is to use a query with an explicit ORDER BY clause
if
you care which order the records come out it (which is what you say
above) and only use the default table ordering if you don't care. And
create the appropriate indexes to support it. To be honest, setting an
Order By property on the table is another piece of the Access design
team
putting user-hostile traps in for new users who don't know their way
round databases, along with AutoFill and LookUp Fields etc etc.

By the way, did any of this help you to track down the cause of your
slow
loading?

B Wishes


Tim F







.