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, I can get the data very easily
and can refresh the same very easily.

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

 

[Douglas J Steele]

Is there something unique about how you use the ADODB recordset? Can you
create a pass-through query that retrieves the data, and use that query as
the recordsource?

 

[Alpesh Patel]

It is Microsoft Access Application using SQL Server data and I am using
connection string which will not require the ODBC definition on the client
computer, so that anybody has access to the application can run the
application without any ODBC definition. The Pass-through query will require
to define the ODBC which will conflict my requirement of no ODBC.

Any idea how to resolve my problem?

Thanks for try.

Alpesh Patel

 

[Douglas J. Steele]

It's trivial to create a pass-through query that doesn't require that a DSN
be created on the client.

Simply use an DSN-less connection string as the ODBC Connection String.

See http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer
for a comprehensive list of SQL Server-related DSN-less connection strings
for ODBC. Note that all you need is the actual connection string. For
example, whereas the first sample string is given as

oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

what you'd put as the ODBC connection string would be:

ODBC;Driver={SQL
Server};Server=MyServerName;Database=myDatabaseName;Uid=myUsername;Pwd=myPassword