Query based on another Query in VBA

[Guest]

It is quite common and easy to make a query (let's name it Query2) based on another selection query (Query1) as an object in the database window, where I can even state relationships among the fields in Query1 with other tables inside Query2. However, how can I do this through VBA
Many thank
Francisco

 

[SA]

Francisco:

If you are using DAO as your data access method, then its pretty simple as
in:

Dim CDB as DAO.Database
Dim qdf as DAO.QueryDef

Set CDB = CurrentDB
Set qdf = CDB.CreateQueryDef("Query2")
qdf.SQL = "Select .... From Query 1 etc. etc. including joins"

If you are using ADO as the data access method, its a bit more complex as
you have to use the ADOX library and address the catalog object representing
the db object. This is from the ADO help file:

Views Append Method Example (VB)
The following code demonstrates how to use a Command object and the Views
collection Append method to create a new view in the underlying data source.

' BeginCreateViewVB
Sub Main()
On Error GoTo CreateViewError

Dim cmd As New ADODB.Command
Dim cat As New ADOX.Catalog

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

' Create the command representing the view.
cmd.CommandText = "Select * From Customers"

' Create the new View
cat.Views.Append "AllCustomers", cmd

'Clean up
Set cat.ActiveConnection = Nothing
Set cat = Nothing
Set cmd = Nothing
Exit Sub

CreateViewError:

Set cat = Nothing
Set cmd = Nothing

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub-- Steve ArbaughACG
Softhttp://ourworld.compuserve.com/homepages/attac-cg"Francisco"

 

[Guest]

Steve, I appreciate very much your reply, many thanks. I just have one concern. Did you concider that Query1 is also in the same VBA code as Query2, I mean none of this querys will exist as object in the database window. Will I still be able to make it work
Thanks again
Francisco