Re: Primary Key not sorted

From: Duane Hookom (duanehookom_at_NO_SPAMhotmail.com)
Date: 06/16/04 

Date: Tue, 15 Jun 2004 20:56:10 -0500

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" <anonymous@discussions.microsoft.com> wrote in message
news:1cf9a01c45325$416f11f0$a401280a@phx.gbl...
> 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" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:1c55301c45268$5eb604c0$a101280a@phx.gbl...
> >> 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
> >>
> >
> >
> >.
> >