If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Opening table - query running?
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 |
#2
|
|||
|
|||
Opening table - query running?
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. "patti" wrote in message ... 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 |
#4
|
|||
|
|||
Opening table - query running?
thanks for the responses.
the record source is the table. So is the query running because of the table's order by property? "Steve" wrote: 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 "Pat Hartman (MVP)" please no wrote in message ... 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. "patti" wrote in message ... 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 |
#5
|
|||
|
|||
Opening table - query running?
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. "patti" wrote in message ... thanks for the responses. the record source is the table. So is the query running because of the table's order by property? "Steve" wrote: 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 "Pat Hartman (MVP)" please no wrote in message ... 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. "patti" wrote in message ... 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 |
#6
|
|||
|
|||
Opening table - query running?
=?Utf-8?B?cGF0dGk=?= wrote in
: 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 |
#7
|
|||
|
|||
Opening table - query running?
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 "Tim Ferguson" wrote: =?Utf-8?B?cGF0dGk=?= wrote in : 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 |
#8
|
|||
|
|||
Opening table - query running?
patti wrote:
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. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#9
|
|||
|
|||
Opening table - query running?
thanks rick.
if there is a filter, where is that code written? i'm curious about the stuff going on "behind the scenes". patti "Rick Brandt" wrote: patti wrote: 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. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#10
|
|||
|
|||
Opening table - query running?
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. -- Joan Wild Microsoft Access MVP "patti" wrote in message ... 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 "Tim Ferguson" wrote: =?Utf-8?B?cGF0dGk=?= wrote in : 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 |
Thread Tools | |
Display Modes | |
|
|