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

If you want to create a new table in a database having field Table Name which will have all the names of all tables present in the database.

Here is the code-

Sub create_a_new_table_and_add_all_table_names_using_query()
' add all table names existing in a databse to a new table called tblnames
DoCmd.SetWarnings (False)
Dim tbl As DAO.TableDef
Dim stra As String
On Error Resume Next
'close the table if open
DoCmd.Close acTable, "tblnames", acSaveYes
' delete table tblnames if exists
On Error Resume Next
DoCmd.RunSQL "drop TABLE tblnames "
' create table with table name tblnames
DoCmd.RunSQL "CREATE TABLE tblnames (Table_Name Text)"
' adding table names in to tblnames
For Each tbl In CurrentDb.TableDefs
' exclude the system tables
If tbl.Attributes = 0 Then
' exclude table tblnames
If tbl.Name <> "tblnames" Then
stra = "INSERT INTO tblnames ([Table_Name]) values(' " & tbl.Name & "')"
DoCmd.RunSQL stra
End If
End If
Next
DoCmd.SetWarnings (True)
End Sub

Download Access Database