Access database records not sorted

[Mike]

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

 

[Peter Hewett]

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]

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

 

[Peter Hewett]

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]

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".

 

[Peter Hewett]

Hi Mike

If your being told that the Company field in not an indexed field, then it's not! I don't
work with Access often enough to accurately diagnose what you are doing or what's going on
at your end.

If you are running the code with the ".Index" statement removed or commented out then it
*will* return out of order data, otherwise I expect it to work.

You need to check that you are using the correct Database and the correct Table in your
database. Also check that the Field names are spelt correctly make sure you are using the
fields name and *not* the fields caption (which can be different).

The code does work. For testing the code I posted I created a database and table and
populated it with data. My initial table used a numerical primary key. Since you were
using the Company name as the primary key I duplicated the Table and set the Company name
as the primary key with its Indexed value set to "Yes (No Duplicates)". I then just
changed the table name in the code and it worked as expected - it displayed an
alphabetically (correctly) ordered list of company names.

This *really* is an Access issue, at the moment it's got absolutely nothing to do with
Word. What you want to do can be done from any VBA enabled application. I think you
would get more help by reposting to an Access news group.

Good luck + Cheers - Peter

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


in an Access news group. You seem
display your data within Access
Access stores and retrieves it.
definition and by default retrieves
one of the specified indexes or you
Table using an Access wizard it
not add one and sets it's Index
or DAO. Since ADO has superseded
the "Microsoft ActiveX Data Objects
version of the library available on the
use to load your ComboBox control.
control named "cboCompanyName".

HTH + Cheers - Peter

 

[Mike]

Thanks I try with the Access people.

Mike

-----Original Message-----
Hi Mike

If your being told that the Company field in not an

indexed field, then it's not! I don't

work with Access often enough to accurately diagnose

what you are doing or what's going on

at your end.

If you are running the code with the ".Index" statement

removed or commented out then it

*will* return out of order data, otherwise I expect it to work.

You need to check that you are using the correct

Database and the correct Table in your

database. Also check that the Field names are spelt

correctly make sure you are using the

fields name and *not* the fields caption (which can be different).

The code does work. For testing the code I posted I

created a database and table and

populated it with data. My initial table used a

numerical primary key. Since you were

using the Company name as the primary key I duplicated

the Table and set the Company name

as the primary key with its Indexed value set to "Yes (No Duplicates)". I then just
changed the table name in the code and it worked as expected - it displayed an
alphabetically (correctly) ordered list of company names.

This *really* is an Access issue, at the moment it's got absolutely nothing to do with
Word. What you want to do can be done from any VBA

enabled application. I think you