Re: Access database records not sorted

From: Mike (anonymous_at_discussions.microsoft.com)
Date: 06/13/04 

Date: Sun, 13 Jun 2004 14:14:11 -0700

Peter,

I was using DAO but I tried your example of ADO. I
cannot get the rstContacts.Index to work. I have it set
to "COMPANY" which is the Primary Key in my database - In
design view it indicates that "COMPANY" is Indexed (No
Duplicates) yet when I run the program in Word it tells
me that "COMPANY" is not an Indexed field. Everything
else seems to work but after I add a record using the
Word program or the Access program itself the records are
not sordted until I "Compact and Repair" the database
then it is sorted correctly in both Access and when I run
the program in Word. What am I doing wrong? Is there
anyway from the Word Macro to Compact and Repair the
database without opening or starting the Access program?

Mike

>-----Original Message-----
>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: Import Text with existing schema.ini file utility
    ... the database engine looks for schema.ini. ... As far as I know it's not possible to specify schema.ini in SQL; ... DAO is specifically designed to work with the Jet ... >> oDB.Execute strSQL ...
    (microsoft.public.access.modulesdaovba)
  • Re: Knowledge Base Help Not working
    ... Database is a DAO object. ... both Access 2000 and 2002 use ADO. ... Microsoft DAO 3.6 Object Library, ... For example, to ensure that you get a DAO recordset, you'll need to ...
    (microsoft.public.access.formscoding)
  • Re: ADO and the Find method
    ... You cannot compare DAO and ADO. ... database, which has OLEDB provider. ... >>>> Opening of the whole table was not a good design anytime. ...
    (microsoft.public.vb.database.ado)
  • Re: Crosstab query error 3104
    ... Thankfully very few compile errors had to ... I have both DAO & ADO references checked but DAO is higher so it is taking ... Thanks - I inherited the original database & am gradually working through ... then perhaps you've removed the reference to ADO. ...
    (microsoft.public.access.queries)
  • Re: How do I use ADO in VC++ 6.0 to get the columns names of the t
    ... you're using DAO rather than ADO ... (ActiveX Database Objects). ... robust library for working with Jet databases. ...
    (microsoft.public.data.ado)
Loading