ADO Multi Select (1 Viewer)

[hi there]

hey folks,

i'm trying to write some code to perform a multi select list box parameter query using ADO. i'm trying to model this code around some sample DAO code i found here. the sample DAO code uses the QueryDef object to modify the stored query. i read somewhere that ADO has replaced the QueryDef object with a somewhat similar Command object. so you can use the Command object in ADODX to modify the stored query and append it to the Procedures collection. can you then call the query with the DoCmd object and view the dynaset?

basically is it possible to use ADO to perform a multi select list box query or must you use DAO?

thanks

 

[SilentBreaker]

Please read the ADO and DAO papers available from the www.microsoft.com. You'll know the differrence.

If you working with the Multi List box and querydefs, always use the DAO.

 

[dcx693]

If you working with the Multi List box and querydefs, always use the DAO.

SilentBreaker, I'm not sure in what context you are making this general statement, but I don't agree.

To retrieve the selected items from a multi-select list box, you must use VBA - not ADO or DAO.

The DAO QueryDef object has been replaced with ADOX Views, but they're easy to use. Take a look at this thread: AllQueries Collection, which discusses working with queries using ADOX.

 

[SilentBreaker]

You still can write VBA with DAO to retrieve data and load them in the list box.

.Overview of DAO
Data Access Objects (DAO) enables you to use a programming language to access and manipulate data in local or remote databases, and to manage databases, their objects, and their structure.

I totally agreed with you that you can do the VBA without the DAO to retrieve the selected items from a multi-select list box

 

[dcx693]

You still can write VBA with DAO to retrieve data and load them in the list box.

True, but the poster asked:

basically is it possible to use ADO to perform a multi select list box query or must you use DAO?

To do the query, you don't need to load anything into a list box. You need to retrieve the values from the listbox. Once you've got those values, you can then use them in a query. For that query, you can use DAO or ADO.

 

[hi there]

hello dcx693 and silentbreaker,

thanks for the responses. i was planning on using VBA to loop through the listbox control to create a criteria string to pass in the where clause of my select statement. i was planning on using ADO for my connection and to open/modify my stored query.

dcx you said to use the views collection of ADODX, i read somewhere that you have to use the procedures collection of ADODX for parameter and action queries. the article i read said the views collection is only for returning non-parameter queries. do agree with this? by the way the link to that thread you posted about using ADODX was very helpful.

thanks for all the help.

 

[dcx693]

Yes, the a view object "Represents a filtered set of records or a virtual table" according to the Access help. IOW, a select query.

A procedure object is "A precompiled collection of code such as SQL statements and optional control-of-flow statements stored under a name and processed as a unit. Stored procedures are stored within a database; they can be executed with one call from an application and allow user-declared variables, conditional execution, and other powerful programming features."

Here's a good example from the Access help file on using the Procedures Append method:

Sub CreateProcedure()

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim prm As ADODB.Parameter
Dim cat As New ADOX.Catalog

' Open the Connection
cnn.Open _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=c:\Program Files\Microsoft Office\" & _
  "Office\Samples\Northwind.mdb;"

' Create the parameterized command (Microsoft Jet specific)
Set cmd.ActiveConnection = cnn
cmd.CommandText = "PARAMETERS [CustId] Text;" & _
  "Select * From Customers Where CustomerId = [CustId]"

' Open the Catalog
Set cat.ActiveConnection = cnn

' Create the new Procedure
cat.Procedures.Append "CustomerById", cmd

End Sub

 

[SilentBreaker]

That's a good stuff, mate