Opening table - query running?

[Guest]

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

 

[Pat Hartman \(MVP\)]

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.

 

[Guest]

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\)]

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.

 

[Guest]

Pat-
Thanks for your time. Excuse my ignorance, I understand that access does not
have any order per se, i thought perhaps the underlying the operating system
or or some other computer workings decided how to return data. So system just
grabs the first piece of data that matches the criteria, then the 2nd, etc.
"Jet retrieves rows as convenient". Thanks again.

 

[Pat Hartman \(MVP\)]

No problem. That's actually the way relational databases, not just Access,
are designed to work. Recordsets are by definition unordered sets of data
unless you impose order by using a query with an order by clause.