Populating List box

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=========================

You could try replacing your While.....Wend with
For .. Next

Something like this

With ads.Recordset
NumRecords = .Recordcount
For X = 1 To NumRecords
itemAdd.text = !Surname
itemAdd.SubItems(1) = !Givenname
itemAdd.Subitems(2) = !Urno
.MoveNext
Next
End With

This should be faster because your not checking for EOF
after every record move. How much faster, I don't know

Regards
SeanR

Thanks I will give it a try ASAP.


Gerard

I have made the change not to great a improvement...

Normally it takes 14 sec with your method it takes it down to 12 sec...maybe this is the best I will get!

Ta


Gerard

Since you are populating a list box with so many records, try using a dBGrid instead. Usually, list boxes work best only when there is a small amount of data that you need to list. Plus, because you are using a query to populate the list box, it slows the application down. Also, what might help in the future (since you already started this application), is using a SQL backend for large db applications. Let me know if this helps.

Thanks


At what stage to you recommend to a client that they need to move to a better backend like Oracle, MS SQL...

Gerard

There are 2 other types of list boxes you can use. Look under the componants menu for other types. You want to use the dblist. It doesn't require that you write code to populate it. It does it automatically, and it doesn't slow the program down while it is populating.

I would have to say in the planning stage of your application is when you decide whether or not to use a SQL backend. You see, Access works the best when the application you are using is somewhat small as well as the database is not increasing rapidly, such as adding 100 new records everyday. If the application is only a temporary or small thing then using the Jet database in Access is best. But if your application is going to be used by many people and hold a lot of information, having the SQL backend with VB frontend is best. Also the security in SQL is much better(I believe). Now the idea the member 666539 of using the dblist should help your current application and well as my solution is another option.

Hope this helps.

PS-also with a SQL backend procedures like your query can be stored in SQL and only accessed when needed and do not need to be compiled in the program. That also would slow your application down

dblist? Is that the same as datalist?

Also I amy have been miss leading...I'm not using a listbox but A listview...main reason is the ability to display multiple columns...Is there a faster control then the basic listview?

Ta

Under the componants you can choose 'data bound list controls' and 'datalist controls'. Both of those (I think) will allow you to set the rowsource and listfield properties to automatically populate the listbox. I think only 1 type works with ADO. You'll have to experiment with that. I'm not sure if they allow you to set up columns.

Look into these controls because it will speed your app up A LOT by automatically populating rather than populating through code.

I'm not familiar with the listview. Where do you find that?

Since you have clarified that it's a listview, then dbGrid might work best, but I am not sure whether you can use it in Access. dbList and dbGrid are ADO data bound list control components in VB.

No problems...this is been down within VB..

Thanks