Re: Access database records not sorted

From: Peter Hewett (nospam_at_xtra.co.nz)
Date: 06/13/04 

Date: Sun, 13 Jun 2004 18:50:39 +1200

Hi Mike

As I said before this is more appropriately dealt with in an Access news group. You seem
to have a conceptual problem. Just because you sort and display your data within Access
in Company order it does *not* mean that that's the way Access stores and retrieves it.
Access stores it in the order you specify in the table definition and by default retrieves
it in key order. When you retrieve it you need to use one of the specified indexes or you
can create a query to sort it into the specified order. For simplicity I'd ensure that
your CompanyName field is indexed. If you created your Table using an Access wizard it
(the CompanyName field) may already have an index, if not add one and sets it's Index
property to "Yes (Duplicates OK)".

 Also you need to specify whether your code is using ADO or DAO. Since ADO has superseded
DAO here's how to do it with ADO:

Private Sub UserForm_Initialize()
    Const cDBPath As String = "c:\documents and settings\administrator\desktop\test.mdb"
    Const cTableContacts As String = "Contacts"
    Const cTableContactsIndex As String = "CompanyName"

    Dim acnDB As ADODB.Connection
    Dim rstContacts As ADODB.Recordset

    ' Initialize Connection object
    Set acnDB = New ADODB.Connection

    ' Specify Microsoft Jet 4.0 Provider and then open the
    ' specified database.
    With acnDB
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open cDBPath

        ' Open table-type Recordset object.
        Set rstContacts = New ADODB.Recordset
        With rstContacts
            .Open Source:=cTableContacts, ActiveConnection:=acnDB, _
              CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly, _
              Options:=adCmdTableDirect

                ' Specify the index to use as we don't want the data in key order
            .Index = cTableContactsIndex

            Do While Not .EOF
' Debug.Print .Fields("CompanyName")
               cboCompanyName.AddItem .Fields("CompanyName")
               .MoveNext
            Loop

            ' Done with the recordset object
            .Close
            Set rstContacts = Nothing
        End With

        ' Close connection...
        .Close
        Set acnDB = Nothing
    End With
End Sub

To use the above code you need to add a reference to the "Microsoft ActiveX Data Objects
2.? Library". Substitute the "?" with the latest version of the library available on the
system (for me it's 2.7). You'll obviously need to set the database path, table name,
index field name and the name of the field you want to use to load your ComboBox control.

The above example also assumes that you have a ComboBox control named "cboCompanyName".

HTH + Cheers - Peter
 

"Mike" <anonymous@discussions.microsoft.com>, said:

>I am not sure how to specify the sort order for a field.
>When I right click on the design view of the table and
>click properties it says "OrderBy Rolodex.company". Is
>there somewhere else that I need to specify a sort order?
>
>Mike
>
>
>>-----Original Message-----
>>Hi Mike
>>
>>This is really an Access problem an not a Word problem.
>You need to ensure that the
>>table/query you are using has specified the sort order
>for the field you want returned in
>>alphabetical order. Just sorting the data in the DB
>wont do it as virtual record sets are
>>created using the order specified in the database schema.
>>
>>HTH + Cheers - Peter
>>
>>
>>"Mike" <anonymous@discussions.microsoft.com>, said:
>>
>>>I am writing a VBA macro in Word that opens an Access
>>>database called Rolodex. I intend to be able to insert
>an
>>>address into the Word document from the database. The
>>>macro is also able to create a new access record,
>delete
>>>a record, or edit a record.
>>>
>>>I am listing the Company name in a ListBox for the user
>>>to select from. When I add a new record to the access
>>>database it is not sorted properly instead it is listed
>>>at the top of the ListBox.
>>>
>>>To sort the data I opened up the database using Access
>>>and sorted the list by Company name. Access is then
>>>sorted correctly but the next time I run the Word macro
>>>and the ListBox is populated with the Company names it
>is
>>>still not sorted properly. The new records are still
>>>listed at the top of the ListBox even though Access is
>>>sorted correctly.
>>>
>>>Why is Word not listing the Company names in the same
>>>order as Access?
>>>
>>>How can I get the Company names listed alphabetically
>in
>>>the Word ListBox?
>>>
>>>Also how can I sort the Access database by Company name
>>>from the Word VBA macro so that I do not have to open
>>>Access and sort the data each time a new record is
>added?
>>>
>>>Mike
>>
>>.
>>

Relevant Pages

  • Re: Access database records not sorted
    ... I was using DAO but I tried your example of ADO. ... not sordted until I "Compact and Repair" the database ... > Also you need to specify whether your code is using ADO ...
    (microsoft.public.word.vba.general)
  • Re: where does GiGi schedule so traditionally, whenever Saeed maintains the civic club very physical
    ... While probes furthermore preach punishments, ... sort at first the straight commissions. ... They are extracting subject to extreme, with blue, sort of lonely ... currently specify the agent. ...
    (sci.crypt)
  • Re: Sort database alphabetically
    ... What I am saying is that you cannot sort the database, ... So all you need to do is add "ORDER BY Name" to your existing query. ... In the Query Designer screen use the Sort Order column to specify ...
    (microsoft.public.sqlserver.server)
  • RE: VBA in Excel & ADO: some problems
    ... if I do not specify the mode property, ... I know how to specify userid and pass in ADO, but how can I add password to ... Yes you can connect to the same database more than once. ...
    (microsoft.public.excel.programming)
  • Re: Opening CE database for seek when multiple search orders specified
    ... When you call CeOpenDatabase you specify which index is used ... during that database session. ... Therefore you should specify one of the sort ... function from the MSysFields table Oid. ...
    (microsoft.public.pocketpc.developer)