Hi guys

Can you help me with a performance problem.

I have a list box that I populate with a ADO control. I use this list box for a search routine. The problem is that it takes about 10 sec to get filled from a table that has 8000 records...I have included the code, can anybody tell me a better (Faster)way to populate my list box. I'm using a Access 97 database...


=======================================================

'Sets up search record set dependent on option taken
Private Sub optSearch_Click(Index As Integer)
Dim strSQL As String
Dim objListImage As Object


On Error Resume Next




strSQL = "SELECT URNO, Surname, Givenname FROM NameTbl where HospCode = " & intHospID & " and NameTbl.URNO in (select distinct ICBact.URNO from ICBact) Order by Surname"
With AdS
.ConnectionString = cndata
.Mode = adModeRead
.CommandType = adCmdText
.EOFAction = adDoMoveLast
.RecordSource = strSQL
.Refresh
End With





Select Case Index


Case 0 'Surname
strObject = "Surname"
strSQL = "SELECT URNO, Surname, Givenname FROM NameTbl where HospCode = " & intHospID & " and NameTbl.URNO in (select distinct ICBact.URNO from ICBact) Order by Surname"
AdS.RecordSource = strSQL
AdS.Refresh



ListSearch.ListItems.Clear
With ListSearch.ColumnHeaders
.Clear
.Add , , "Surname", 2000
.Add , , "Given", 1900
.Add , , "URNO", 1500, lvwColumnRight
End With
Me!ListSearch.HideColumnHeaders = False

While Not AdS.Recordset.EOF
Set itmAdd = ListSearch.ListItems.Add()
itmAdd.Text = AdS.Recordset!Surname
itmAdd.SubItems(1) = AdS.Recordset!Givenname
itmAdd.SubItems(2) = AdS.Recordset!URNO


AdS.Recordset.MoveNext
Wend
AdS.Recordset.Close
Case 1 'URNO
strObject = "URNO"
strSQL = "SELECT URNO, Surname, Givenname FROM NameTbl where HospCode = " & intHospID & " and NameTbl.URNO in (select distinct ICBact.URNO from ICBact) Order by URNO"
AdS.RecordSource = strSQL
AdS.Refresh
Me!ListSearch.ListItems.Clear
With Me!ListSearch.ColumnHeaders
.Clear
.Add , , "URNO", 1500
.Add , , "Surname", 2000
.Add , , "Given", 1900, lvwColumnRight
End With
Me!ListSearch.HideColumnHeaders = False

While Not AdS.Recordset.EOF
Set itmAdd = ListSearch.ListItems.Add()
itmAdd.Text = AdS.Recordset!URNO
itmAdd.SubItems(1) = AdS.Recordset!Surname
itmAdd.SubItems(2) = AdS.Recordset!Givenname


AdS.Recordset.MoveNext
Wend
AdS.Recordset.Close

End Select





End Sub
==========================End=========================