Primary Key not sorted

[Mike]

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

 

[Duane Hookom]

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.

 

[SFAxess]

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.

 

[Mike]

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.

 

[Duane Hookom]

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.

 

[John Vinson]

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.

 

[Guest]

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