Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 1st, 2008, 12:03 PM
Registered User
 
Join Date: Aug 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access will not preserve the table order.

I have had a problem with Access not preserving the file order of tables. It seems to take the first 5 to 10 records of the table and move them down a couple of records. I would occasionally run into this problem with Access 2000, but with Access 2007 the problem has become common. Has anyone had this same problem?
 
Old April 1st, 2008, 12:46 PM
Authorized User
 
Join Date: Mar 2008
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm not sure exactly what you are dealing with, but, I have had similar issues with tables that I am simply using for "lookups". I have solved by adding an "ID" field that is auto-number and then made that field the key index. It will keep them in order.

 
Old April 2nd, 2008, 06:38 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

What do you mean by "file order of tables"? Generally databases use indices for record sorting, and the actual display or return of record order is random. In SQL, for example, it is anybody's guess as to what order the records come back in.

If there is a particular order you want records returned in, then the other post is on the right track. Create an index on the field(s) that you will most often do your lookups against. In Access you have a choice of Indexed = Yes, with or without duplicates allowed. This is somewhat equivalent to Clustered and non-clustered indices in SQL server (think book index versus table of contents).

Anywho, the "order" records are returned can be controlled based on your query language, etc.

Did any of that help?

mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old April 23rd, 2008, 01:23 PM
Registered User
 
Join Date: Aug 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the feedback. I guess I did state my question a bit vaguely.

The jobs that I use Access to manage require that the records stay in a sequential order within a given table. Sometime that order is set by another program and the resulting table in imported back into Access. Other times a table is created using a make table query in Access. The sort order is determined by a number value in a field called SEQ_NO.

In both cases, Access will occasionally take a block of 10-20 records at the beginning of a file and move them toward the middle. Instead of the record order being 1,2,3,4... it will be 19,20,21,1,2,3,4,22,23... If the query is used to setup the file, I can run it again (not changing anything) and it will sort it correctly.

Access 2000 would do this only about 1 job in 100. Access 2007 seems to do it 1 job in 10. There is nothing in the Autoindex on Import/Create setting, so what could be doing it?


 
Old April 23rd, 2008, 02:43 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Well, unfortunately you may have to code the entire set of transactions rather than rely on Access to keep things in the order you want. What is the code for the routines the you currently run?



mmcdonal

Look it up at: http://wrox.books24x7.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
copy set of data to another table with order by vk18 SQL Server 2000 2 August 23rd, 2013 11:21 AM
Adding an Order to Table seananderson SQL Server 2000 1 June 28th, 2007 05:33 PM
System to order rows a table DB anteojo Classic ASP Databases 1 December 2nd, 2004 02:34 AM
Access 2K Sort Order rgerald Access VBA 0 August 12th, 2004 10:37 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.