 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / May 2007Opening 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 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. > 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. [quoted text clipped - 12 lines] > > patti <<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 resource@pcdatasheet.com > 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 [quoted text clipped - 24 lines] >> >> patti thanks for the responses. the record source is the table. So is the query running because of the table's order by property? > <<Opening a table in datasheet view always runs a query.>> > [quoted text clipped - 33 lines] > >> > >> patti 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. > thanks for the responses. > [quoted text clipped - 48 lines] >> >> >> >> patti 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. > <<Opening a table in datasheet view always runs a query.>> > [quoted text clipped - 33 lines] >>> >>> patti > 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 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 > > the table's property sheet shows "order by" field filled in > > with one of the fields; is that triggering the message about the [quoted text clipped - 5 lines] > > Tim F > thanks for helping. > [quoted text clipped - 4 lines] > 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.  SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com thanks rick. if there is a filter, where is that code written? i'm curious about the stuff going on "behind the scenes". patti > > thanks for helping. > > [quoted text clipped - 9 lines] > just open the table, click "Remove all filters" in the toolbar and then save > that. > 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. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ 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. SignatureJoan Wild Microsoft Access MVP > thanks for helping. > [quoted text clipped - 15 lines] >> >> Tim F no lookups. i am really curious to see the code behind the table if there is a "sort by" property. where is that stored? > 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. > [quoted text clipped - 21 lines] > >> > >> Tim F > 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. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ 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! > > i am really curious to see the code behind the table if there is > > a "sort by" property. where is that stored? [quoted text clipped - 7 lines] > executable so you have no control over it, except to remove the Sort > Order property value. > 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. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ 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. > no lookups. i am really curious to see the code behind the table if there > is [quoted text clipped - 36 lines] >> >> >> >> Tim F 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. > 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 [quoted text clipped - 40 lines] > >> >> > >> >> Tim F >> 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 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 > >> Is there an index on the field you use for sorting? If not, put one > >> on. [quoted text clipped - 25 lines] > > Tim F 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 > >> Is there an index on the field you use for sorting? If not, put one > >> on. [quoted text clipped - 25 lines] > > Tim F 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. > Hi Tim - > [quoted text clipped - 35 lines] >> >> Tim F 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 > 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". [quoted text clipped - 46 lines] > >> > >> Tim F 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. > Hi Pat- > [quoted text clipped - 64 lines] >> >> >> >> Tim F 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. > I just told you. It is undefined. Jet retrieves rows as convenient. > Normally, the rows will be returned in primary key sequence because Jet [quoted text clipped - 70 lines] > >> >> > >> >> Tim F 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. > Pat- > Thanks for your time. Excuse my ignorance, I understand that access does [quoted text clipped - 99 lines] >> >> >> >> >> >> Tim F |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.