Slow sorting/loading

[Bo Hansson]

In my Word 2K VBA-application I'm using an Access database table to store
information. When presenting information to the user, I load some records
into a Word VBA listbox, and by clicking on the different column headings of
that box, the user can choose the desired sorting. It works OK, but the
sorting/loading process is very slow compared to what you experience if you
change the sorting in an Acess table interface. My code structure is as
follows:

Set dbsDok = OpenDatabase(----.mdb")
Set rstDok = dbsDok.OpenRecordset("SELECT * FROM --- WHERE --- ORDER
BY ---;")

If rstDok.BOF = False Then rstDok.MoveFirst
Do While rstDok.EOF = False
listBox.AddItem
listBox(llistBox.ListCount - 1, 0) = rstDok("---")
'
'
'
listBox.List(listBox.ListCount - 1, 7) = rstDok("---")
rstDok.MoveNext
Loop

rstDok.Close
dbsDok.Close

Can anyone explain the slow action and tell me how to improve it ?

/BosseH

 

[Doug Robbins - Word MVP]

Hi Bo,

It might be quicker to load the data into an array using code such as:

Private Sub UserForm_Activate()
'allocate memory for the database object as a whole and for the active
record
Dim myDataBase As Database
Dim myActiveRecord As Recordset
Dim i As Integer, j As Integer, m As Integer, n As Integer
'Open a database
Set myDataBase = OpenDatabase("E:\Access97\Ely\ResidencesXP.mdb")
'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("Owners", dbOpenForwardOnly)
'Get the number of fields in the table
j = myActiveRecord.Fields.Count
'Get the number of Records in the table
'Loop through all the records in the table until the end-of-file marker is
reached
i = 0
Do While Not myActiveRecord.EOF
i = i + 1
'access the next record
myActiveRecord.MoveNext
Loop
myActiveRecord.Close
'Set the number of columns in the listbox
ListBox1.ColumnCount = j
' Define an array to be loaded with the data
Dim MyArray() As Variant
'Load data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 2
Set myActiveRecord = myDataBase.OpenRecordset("Owners",
dbOpenForwardOnly)
m = 0
Do While Not myActiveRecord.EOF
MyArray(m, n) = myActiveRecord.Fields(n + 1)
m = m + 1
myActiveRecord.MoveNext
Loop
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
'Then close the database
myActiveRecord.Close
myDataBase.Close
End Sub

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Bo Hansson]

Thanks Doug,

I'll try your approach and let you know the result.

/BosseH

 

[Bo Hansson]

Doug,

Your idea was just GREAT. Now it works much faster. Thanks a lot.

/BosseH