List all append queries in a database

[KennyDies]

Hi there,

First post, am a seasoned access builder but cant find the answer to this after doing a google...

I want to list all the append queries in my database.

I thught the following would have done but only returns tables and select queries...Anyone help?


Dim catDB As ADOX.Catalog
Dim tblList As ADOX.Table

Set catDB = New ADOX.Catalog
catDB.ActiveConnection = ConnectLocal

For Each tblList In catDB.Tables

Debug.Print tblList.Name & vbTab & tblList.Type

Next

Set catDB = Nothing

Thanks, Ross

 

[RoyVidar]

If I wanted append queries, I think I'd tried the Procedures collection, though I don't know if you can determine the type (append, update...) without studying the SQL.

Another method, is the openschema method, where you can also get to the SQL (PROCEDURE_DEFINITION). Small sample (air code)

set rs = yourconnection.openschema(adschemaprocedures)
for l = 0 to rs.fields.count-1
    debug.print rs.fields(l).name,
next l
debug.print
debug.print rs.getstring

 

[pbaldy]

Another option:

  Dim db            As DAO.Database
  Dim QD            As QueryDef

  Set db = CurrentDb()

  For Each QD In db.QueryDefs
    If QD.Type = dbQAppend Then
      Debug.Print QD.Name
    End If
  Next

  Set QD = Nothing
  Set db = Nothing

 

[KennyDies]

cheers...

Trying to get the schema to work, I was interested in the ADO version as can do it in DAO

Thanks

 

[boblarson]

ADO doesn't have as many options as DAO for things like this. DAO is actually the native way Access does things internally.