Efficiently populating a combobox or table using ADODB (1 Viewer)

[Derevon]

I have a database using querydefs for connection to the MS SQL Server back-end, but I want to replace all these with ADODB-connections for security reasons (the database uses a service account with credentials in the connection string, and creating querydefs exposes the passwords so you can easily see it when opening the database file in Notepad or something). I tried clearing the connection string in VBA after each use, but until the database is compacted, the information is still there, so this is not a preferable option. The password is stored in encrypted form in VBA.

Anyway, my problem is that all ADODB approaches that I have tried so far that I managed to get to work were much too slow. I tried looping through a recordset and populating a temporary table with CurrentDB.Execute "INSERT ..." etc which was used as a rowsurce, but this resulted in the main form taking twice the time to open, so I would like to find a better way.

I have also tried populating the combobox using the combobox additem method, but this was much slower still, even for very small tables. I also tried copying the recordset to an array, but that was astonishingly even slower yet.

Does anyone know of an effective way to populate a combobox (or a temporarily table used as the rowsource for a combobox) based on ADODB (or perhaps some other way not involving querydefs)?

Thank you

 

[Ranman256]

No. Combo boxes are based on queries/tables for simplicity.
Don't try to complicate the crap out access. Just hide your tables.

 

[MarkK]

I don't know if you've tried this, but a Listbox and Combobox have a recordset property, so you can just do this...

Private Sub Form_Load()
    Dim rs As New ADODB.Recordset
  
    rs.Open "SELECT * FROM tTestData", CurrentProject.AccessConnection
    Set Me.List0.Recordset = rs
    
End Sub

Open a recordset. Assign it directly the recordset property of the control.
hth
Mark

 

[Derevon]

I don't know if you've tried this, but a Listbox and Combobox have a recordset property, so you can just do this...

Private Sub Form_Load()
    Dim rs As New ADODB.Recordset
  
    rs.Open "SELECT * FROM tTestData", CurrentProject.AccessConnection
    Set Me.List0.Recordset = rs
    
End Sub

Open a recordset. Assign it directly the recordset property of the control.
hth
Mark

I thought I had tried it, but I must have used the wrong syntax, because I got errors. Your version kind of worked once now when I tried, but when I click on the combobox the options appear very slowly every time, and it tends to crash my Access almost every time for some reason.

 

[MajP]

Does the combo information change often? If not bring it across and persist it in a table on the client side.

 

[Derevon]

Does the combo information change often? If not bring it across and persist it in a table on the client side.

The combo information shouldn't change often at all, so making them persist in client side tables is probably a good idea actually. I guess I will have to implement some kind of back-end table with latest version timestamps for each source, so updates are only carried out when there has actually been a change. This way the lower performance should have negligible impact on performance I hope.

However, I do have another form where a local table will need to be populated based on a back-end table pretty much all the time. Which makes me wonder if there is a more efficient way to populate a client side table without using querydefs than looping through a recordset and using SQL inserts for every single line? We're talking a couple of hundred rows at the most per update.

Thanks

 

[The_Doc_Man]

Just as a wild-arsed guess, be sure that you have an index on the table on the field that is used to define the order of presentation of the contents. Then in your "created SQL" for the box, include an ORDER BY clause on that field.

The reason I'm offering this suggestion is that a combo box will order things according to the bound column (usually) so if there is an index on that column, it should be faster to populate - because the sort will use the index rather than doing an in situ unindexed sort at load time.