Get Names of all the Queries Existing in a Database and Add them to a New Table Using DAO

If you want to know the find the names of all queries existing in the database and add them to new table.

Here is the code-


Sub create_a_new_table_and_add_all_query_names_using_dao()
' add allquery names existing in a databse to a new table called qrynames
DoCmd.SetWarnings (False)
Dim qry As DAO.QueryDef
Dim ntbl As DAO.TableDef
Dim fld As DAO.Field
Dim rcd As DAO.Recordset
Dim stra As String
On Error Resume Next
'close the table if open
DoCmd.Close acTable, "qrynames", acSaveYes
On Error Resume Next
' delete table tblnames if exists
CurrentDb.TableDefs.Delete "qrynames"
' create table with table name tblnames
Set ntbl = CurrentDb.CreateTableDef("qrynames")
Set fld = ntbl.CreateField("QRY_Name", dbText, 255)
ntbl.Fields.Append fld
CurrentDb.TableDefs.Append ntbl
RefreshDatabaseWindow
For Each qry In CurrentDb.QueryDefs
If Left(qry.Name, 1) <> "~" Then
Set rcd = CurrentDb.OpenRecordset("QRYnames", dbOpenDynaset, dbAppendOnly)
With rcd
.AddNew
.Fields![QRY_name].Value = qry.Name
.Update
.Close
End With
End If
Next
DoCmd.SetWarnings (True)
End Sub


Download Access Database