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
|
|||
|
|||
Primary Key not sorted
I had a Paradox database called Rolodex that I imported
into Access. When I did this it asked me if I wanted to create a Primary Key field and I said yes. Access created a Auto Number column and set it up as the Primary Key. After creating the database I decided that I wanted the Company field to be the Primary Key so I deleted the Auto Number column and then set the Company field up as the Primary Key. In design view it tells me that the Company field is Indexed (No Duplicates). I thought the Primary Key was always supposed to be sorted. If I add a record it does not automatically sort the database alphabetically in the Company field. In order to get the records sorted by Company name in the database I have to Compact and Repair the database. I realize that I can sort the records by using the Sort Ascending button on the toolbar but I actually want the records sorted in the database because I am writing a Word VBA program that populates a ListBox with the Company names and I want them sorted alphabetically. If I just use the Sort Ascending button it will look sorted when viewing the database in Access but when the Company names are brought into the Word VBA ListBox they are listed in the same order that they are saved in the Access database. When I add a new record in Access shouldn't it automatically be sorted by Comapny which is the Primary Key? Is there any way to force it to automatically sort the records even though the record was added using a Word VBA program? The record is currently being added using DAO although I could change it to ADO. When I try using the Recordset.Index type statement it tells me that Company is not an Indexed field. Is that why the records are not sorted automatically when I add a new record using Access? Mike |
#2
|
|||
|
|||
Primary Key not sorted
Create sorted recordsets with standard SQL. Tables are like a bag of
marbles. There is no order unless you specify it in a query or other. -- Duane Hookom MS Access MVP "Mike" wrote in message ... I had a Paradox database called Rolodex that I imported into Access. When I did this it asked me if I wanted to create a Primary Key field and I said yes. Access created a Auto Number column and set it up as the Primary Key. After creating the database I decided that I wanted the Company field to be the Primary Key so I deleted the Auto Number column and then set the Company field up as the Primary Key. In design view it tells me that the Company field is Indexed (No Duplicates). I thought the Primary Key was always supposed to be sorted. If I add a record it does not automatically sort the database alphabetically in the Company field. In order to get the records sorted by Company name in the database I have to Compact and Repair the database. I realize that I can sort the records by using the Sort Ascending button on the toolbar but I actually want the records sorted in the database because I am writing a Word VBA program that populates a ListBox with the Company names and I want them sorted alphabetically. If I just use the Sort Ascending button it will look sorted when viewing the database in Access but when the Company names are brought into the Word VBA ListBox they are listed in the same order that they are saved in the Access database. When I add a new record in Access shouldn't it automatically be sorted by Comapny which is the Primary Key? Is there any way to force it to automatically sort the records even though the record was added using a Word VBA program? The record is currently being added using DAO although I could change it to ADO. When I try using the Recordset.Index type statement it tells me that Company is not an Indexed field. Is that why the records are not sorted automatically when I add a new record using Access? Mike |
#3
|
|||
|
|||
Primary Key not sorted
If you use open the table and sort the column, then save
the table, it will save the sorting you applied. The sorting is propigated to other objects based on the constraints of the object referencing the table. If you are accessing the table using DAO, I suggest you create a recordset based on a SELECT statement which uses an ORDER BY clause, instead of creating the recordset based on the table directly. -----Original Message----- I had a Paradox database called Rolodex that I imported into Access. When I did this it asked me if I wanted to create a Primary Key field and I said yes. Access created a Auto Number column and set it up as the Primary Key. After creating the database I decided that I wanted the Company field to be the Primary Key so I deleted the Auto Number column and then set the Company field up as the Primary Key. In design view it tells me that the Company field is Indexed (No Duplicates). I thought the Primary Key was always supposed to be sorted. If I add a record it does not automatically sort the database alphabetically in the Company field. In order to get the records sorted by Company name in the database I have to Compact and Repair the database. I realize that I can sort the records by using the Sort Ascending button on the toolbar but I actually want the records sorted in the database because I am writing a Word VBA program that populates a ListBox with the Company names and I want them sorted alphabetically. If I just use the Sort Ascending button it will look sorted when viewing the database in Access but when the Company names are brought into the Word VBA ListBox they are listed in the same order that they are saved in the Access database. When I add a new record in Access shouldn't it automatically be sorted by Comapny which is the Primary Key? Is there any way to force it to automatically sort the records even though the record was added using a Word VBA program? The record is currently being added using DAO although I could change it to ADO. When I try using the Recordset.Index type statement it tells me that Company is not an Indexed field. Is that why the records are not sorted automatically when I add a new record using Access? Mike . |
#4
|
|||
|
|||
Primary Key not sorted
I think you may be incorrect about the bag of marbles.
If I add a record using Access it is not sorted even though the Company field is the Primary Key but if I Compact and Repair the database and then open it back up it is sorted by Company name. Even if I go the the Word VBA program and populate the ListBox it is sorted by Company name. When I Compact and Repair the database it actually saves the records in order sorted by the Primary key field. Without the Primary Key you are right - the records are saved in the order that they were entered and are not sorted. -----Original Message----- Create sorted recordsets with standard SQL. Tables are like a bag of marbles. There is no order unless you specify it in a query or other. -- Duane Hookom MS Access MVP "Mike" wrote in message ... I had a Paradox database called Rolodex that I imported into Access. When I did this it asked me if I wanted to create a Primary Key field and I said yes. Access created a Auto Number column and set it up as the Primary Key. After creating the database I decided that I wanted the Company field to be the Primary Key so I deleted the Auto Number column and then set the Company field up as the Primary Key. In design view it tells me that the Company field is Indexed (No Duplicates). I thought the Primary Key was always supposed to be sorted. If I add a record it does not automatically sort the database alphabetically in the Company field. In order to get the records sorted by Company name in the database I have to Compact and Repair the database. I realize that I can sort the records by using the Sort Ascending button on the toolbar but I actually want the records sorted in the database because I am writing a Word VBA program that populates a ListBox with the Company names and I want them sorted alphabetically. If I just use the Sort Ascending button it will look sorted when viewing the database in Access but when the Company names are brought into the Word VBA ListBox they are listed in the same order that they are saved in the Access database. When I add a new record in Access shouldn't it automatically be sorted by Comapny which is the Primary Key? Is there any way to force it to automatically sort the records even though the record was added using a Word VBA program? The record is currently being added using DAO although I could change it to ADO. When I try using the Recordset.Index type statement it tells me that Company is not an Indexed field. Is that why the records are not sorted automatically when I add a new record using Access? Mike . |
#5
|
|||
|
|||
Primary Key not sorted
Are you willing to rely on any "natural" sort of records? I am not. I like
to assume the order of records is totally random. -- Duane Hookom MS Access MVP "Mike" wrote in message ... I think you may be incorrect about the bag of marbles. If I add a record using Access it is not sorted even though the Company field is the Primary Key but if I Compact and Repair the database and then open it back up it is sorted by Company name. Even if I go the the Word VBA program and populate the ListBox it is sorted by Company name. When I Compact and Repair the database it actually saves the records in order sorted by the Primary key field. Without the Primary Key you are right - the records are saved in the order that they were entered and are not sorted. -----Original Message----- Create sorted recordsets with standard SQL. Tables are like a bag of marbles. There is no order unless you specify it in a query or other. -- Duane Hookom MS Access MVP "Mike" wrote in message ... I had a Paradox database called Rolodex that I imported into Access. When I did this it asked me if I wanted to create a Primary Key field and I said yes. Access created a Auto Number column and set it up as the Primary Key. After creating the database I decided that I wanted the Company field to be the Primary Key so I deleted the Auto Number column and then set the Company field up as the Primary Key. In design view it tells me that the Company field is Indexed (No Duplicates). I thought the Primary Key was always supposed to be sorted. If I add a record it does not automatically sort the database alphabetically in the Company field. In order to get the records sorted by Company name in the database I have to Compact and Repair the database. I realize that I can sort the records by using the Sort Ascending button on the toolbar but I actually want the records sorted in the database because I am writing a Word VBA program that populates a ListBox with the Company names and I want them sorted alphabetically. If I just use the Sort Ascending button it will look sorted when viewing the database in Access but when the Company names are brought into the Word VBA ListBox they are listed in the same order that they are saved in the Access database. When I add a new record in Access shouldn't it automatically be sorted by Comapny which is the Primary Key? Is there any way to force it to automatically sort the records even though the record was added using a Word VBA program? The record is currently being added using DAO although I could change it to ADO. When I try using the Recordset.Index type statement it tells me that Company is not an Indexed field. Is that why the records are not sorted automatically when I add a new record using Access? Mike . |
#6
|
|||
|
|||
Primary Key not sorted
On Tue, 15 Jun 2004 15:08:14 -0700, "Mike"
wrote: If I add a record using Access it is not sorted even though the Company field is the Primary Key but if I Compact and Repair the database and then open it back up it is sorted by Company name. Correction: The records are *DISPLAYED* in Primary Key order, as a convenience for the user. They are not *STORED* in Primary Key order - just displayed. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#7
|
|||
|
|||
Primary Key not sorted
John,
Maybe they just look like they are sorted by Primary Key when I open the database but when the Word VBA program gets the information from the database file it is listed in my ListBox as if the records are sorted by the Primary Key which is the Comapany Name - they are in order in the ListBox which is what I want but only after I Compact and Repair the database. When I add a record using the Word VBA program is saves the record but when the ListBox is repopulated with the comapny names the new record is at the end of the list but I want the Comapny Names sorted in the ListBox. I have to close the VBA program - open the Access database - Compact and Repair the database - then restart the Word VBA program that populates the ListBox with Company Names then the Company Names are listed in order. I am tring to find a way using the Word VBA program to add the record, clear the ListBox, repopulate the ListBox and have it sorted without closing the Word VBA program. I posted a message in the Word Newsgroup and was told to use the ADO method to access the database and use the Recordset.Index statement so that the records are sorted but when I try to do that program gives me an error that states that the Company field is not a Indexed field. When I open up the database in design view it says that the Company field is Indexed (No Duplicates). I am concerned that if I sort the records using a query or the Word VBA program that I will use the wrong record number when deleting records causing a program flaw that deletes the incorrect record. I am using the ListBox.ListIndex to decide which record is deleted. How can I keep the Company Names sorted and make sure that I am using the right ListBox.ListIndex number? Mike -----Original Message----- On Tue, 15 Jun 2004 15:08:14 -0700, "Mike" wrote: If I add a record using Access it is not sorted even though the Company field is the Primary Key but if I Compact and Repair the database and then open it back up it is sorted by Company name. Correction: The records are *DISPLAYED* in Primary Key order, as a convenience for the user. They are not *STORED* in Primary Key order - just displayed. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public . |
Thread Tools | |
Display Modes | |
|
|