AllQueries Collection

A lot of my DAO code uses the QueryDef object (to add, delete, etc) but now I am trying to convert to ADO

I can list the queries but cannot seem to create a new query nor find the properties of the AllQueries items (AccessObjects)

Any help would be appreciated

I've found the AllQueries collection useful only for testing to see if a specific query already existed.

It's actually ADOX (an extension to ADO), not ADO, that you use for data definition. Make sure you have a reference to ADO Extensions for DDL and Security checked.

Here's some sample code for listing the names of existing queries using ADOX:

Sub ListViews()
Dim cat As ADOX.Catalog
Dim vew As ADOX.View

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
For Each vew In cat.Views
Debug.Print vew.Name
Next
Set cat = Nothing
End Sub


And here's some ADODB and ADOX code for adding a query to your collection. Queries are actually called views in ADOX.

Sub AddView()
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim vew As ADOX.View

Set cat = New ADOX.Catalog
Set cmd = New ADODB.Command
cat.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "SELECT * FROM tblCalls WHERE Name Like 'D*'"
cat.Views.Append "qryNames", cmd

Set cmd = Nothing
Set cat = Nothing

End Sub

many thanks

thankyou for such a helpful reply - no problems now !!