How to load data in ListBox using one command from ADODB RecordSet

[Alpesh Patel]

I am using ADODB recordset and has SQL server database. I have a ListBox on
the form and need to load large rows around 100 number of rows with about 12
columns. I want to do the same using couple of commands because it is require
to refresh the ListBox many times during different operations on form.

If I am using the Link table which links to the SQL Server table and using
ListBox.RowSource by supplying the normal SQL or using Pass-through query, I
can get the data very easily and can refresh the same very easily. But these
will all requires ODBC connections. The Microsoft Access Application I
created is going to use by different user and will be difficult to install
ODBC to different machine, so that logic may not works.

I am using ADODB recordset to get the data from the SQL Server tables. I can
load individual row in the list box by moving the records by record in the
ADODB Recordset, but that will take time loading the data and not good to
refresh frequently.

Is there any other way I can load the SQL Server table data into ListBox
using ADODB RecordSet or some other easy way by couple of commands?

Thanks in Advance.

Alpesh Patel

 

[Brendan Reynolds]

In recent versions of Access, list boxes have a Recordset property to which
you can assign an ADO or DAO recordset. I *think* Access 2002 was the first
version to introduce this feature, though I'm not 100% sure of that. The
following works in Access 2003. If I'm right about when the feature was
introduced, I believe it should also work in Access 2002, but not in any
earlier version ...

Private Sub List0_DblClick(Cancel As Integer)

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=(local);" & _
"Initial Catalog=Northwind;" & _
"Integrated Security=SSPI"
cnn.Open
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = "SELECT ProductName FROM Products ORDER BY ProductName"
.Open
End With
Set Me.List0.Recordset = rst
rst.Close
cnn.Close

End Sub

 

[Alpesh]

Thanks Brandan for the reply.

I was using the following, but without "CursorLocation".

rst.Open sSql, cnxn, adOpenStatic, adLockOptimistic
Set Me.lstTestCaseStepSelect.Recordset = rst

CursorLocation I found from your reply and used before "set", it works.
Thanks for helping out.
rst.CursorLocation = adUseClient

Just for knowledge, what it will make difference if we don't use the
"CursorLocation"? What exactly that one is doing? Because the normal record
operation we can do in Recordset without that, but not updating the ListBox
like above.

Thanks Again.

Alpesh Patel

 

[Brendan Reynolds]

The following ADO help topic explains the difference between client-side and
data-provider or driver-supplied cursors ...
http://msdn.microsoft.com/library/d.../en-us/ado270/htm/mdcstcursorlocationenum.asp