Opening table - query running?

[Guest]

When opening a table to view, i notice in the status bar(?- i think that's
what it's called) says something about running a query.

How do i determine what is going on behind the scenes when this table opens?
In design view, the table's property sheet shows "order by" field filled in
with one of the fields; is that triggering the message about the query?

It is a very large table of almost 2 million rows and maybe 15 columns. it
takes a long time to open. is this related to the order by or mysterious
query?

thank you very much for any help.

patti

 

[Pat Hartman \(MVP\)]

Opening a table in datasheet view always runs a query. That is how data is
retrieved from a relational database. Usually the data is returned in
primary key sequence. Try removing the order by value and see if that
speeds up the opening of the table/query. It should. Access will display
results as soon as it has a full screen of data. However, in the
background, Access will keep fetching rows until all 2,000,000 have been
brought to your workstation. That is why it is poor practice to just open a
recordset that returns the entire table. Use a query that returns only the
rows you want.

 

[Steve]

<<Opening a table in datasheet view always runs a query.>>

Not if the recordsource is a table!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

thanks for the responses.

the record source is the table. So is the query running because of the
table's order by property?

 

[David Cox]

I believe that if you order a table by clicking on the column heading and
then "save changes", you will then, in effect, run an order by query when
next you open it.

 

[Tim Ferguson]

the table's property sheet shows "order by" field filled in
with one of the fields; is that triggering the message about the
query?

... it takes a long time to open.

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

Tim F

 

[Guest]

thanks for helping.

does having a "sort by" property slow the datasheet load? and where is the
code behind the table located?

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?

patti

 

[Rick Brandt]

thanks for helping.

does having a "sort by" property slow the datasheet load? and where
is the code behind the table located?

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?

Any delay caused by sorting the table will only be imposed when you open the
table's datasheet. It won't affect anything else. If you want to removed it
just open the table, click "Remove all filters" in the toolbar and then save
that.

 

[Guest]

thanks rick.

if there is a filter, where is that code written? i'm curious about the
stuff going on "behind the scenes".

patti

 

[Joan Wild]

Yes it does make sense. Open the table in design view and in the properties sheet remove anything in the filters and order properties. Then save the table. That should do it.

Another source for querying in the background could be a lookup defined in the table. When you view the table in datasheet view, are any of the columns showing as comboboxes? If so, then you have some lookups defined. Back in design view you can remove the lookups (via the lookup tab for the field).

When you want/need a combo to lookup something in another table, you'd use a combobox defined *on your form*, not in the table.

 

[Guest]

no lookups. i am really curious to see the code behind the table if there is
a "sort by" property. where is that stored?

 

[David W. Fenton]

if there is a filter, where is that code written? i'm curious
about the stuff going on "behind the scenes".

It's not code. It's a property of the datasheet.

If you want to see it, open the table in design view and choose
PROPERTIES from the VIEW menu. Order By is one of the properties.
Clear the value in that field and save the table.

This applies to both linked and local tables, by the way. I'm not
sure the interaction between linked tables and back end tables with
sort order defined on them. It may be that a back end table with a
saved sort order will override anything you try to do in the front
end. So, if the instructions in the previous paragraph don't work,
you need to open the back end and execute the same instructions on
the back end table.

 

[David W. Fenton]

i am really curious to see the code behind the table if there is
a "sort by" property. where is that stored?

There is no code. It is a property of the table, just as Order By is
a property of forms and reports. It is normally not set on table
datasheets, but it can be easily removed by following the
instructions you've been given.

The actual code that executes the sort is part of the Access
executable so you have no control over it, except to remove the Sort
Order property value.

 

[Guest]

thanks, david. so the table build and its filters are buried in the code of
access?

i would like to know the code running behind access itself but i guess that
will wait for another day!

 

[David Cox]

All of the information Access uses internally is kept in hidden system
tables. It is possible to open these and view them, but mostly they make
little sense to those not in the know. Sometimes ignorance really is bliss.

 

[David W. Fenton]

thanks, david. so the table build and its filters are buried in
the code of access?

No, the code that processes the filters and sorts is in the Access
executable.

The Order By property itself is quite plainly exposed in the
properties in table design.

i would like to know the code running behind access itself but i
guess that will wait for another day!

Why? This is nothing more than a property setting that has been set
to a value you don't like. It's no different than if the background
color of the table datasheet had been set to chartreuse. Well, the
one difference is that the latter wouldn't cause a performance
drain, but in terms of fixing the problem, it's exactly equivalent.

 

[Tim Ferguson]

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

 

[Pat Hartman \(MVP\)]

Just how do you think the recordset is produced if Access doesn't run a
query? Relational databases are NOT flat files. You do not read them
directly. The database engine reads the tables and it does this by creating
and running a query. Rather than arguing with me, prove it to yourself -
use the Database profiler to see what is sent to the server. I'm not sure
how to prove this with Jet tables but perhaps YOU can come up with a way to
disprove it.

 

[Guest]

Hi Tim -

Sorry for the delayed response.

Actually, i am not sure. PC crashes, memory parity issues, life annoyances.
So many factors. but now that i trust my pc (knock plastic) i will recreate
original setting & post back.

patti

 

[Guest]

Hi David-

Thanks for the input.

i like to know how things work. how do the on-off switches translate
through to access? how does electricity turn those switches on & off? who is
the wizard of oz?etc.

A little bit of knowledge is a dangerous thing; and add some adult beverages
and you can think you're einstein.