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

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_dao()
' add all table names existing in a databse to a new table called tblnames
DoCmd.SetWarnings (False)
Dim tbl As DAO.TableDef
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, "tblnames", acSaveYes
On Error Resume Next
' delete table tblnames if exists
CurrentDb.TableDefs.Delete "tblnames"
' create table with table name tblnames
Set ntbl = CurrentDb.CreateTableDef("tblnames")
Set fld = ntbl.CreateField("Table_Name", dbText, 255)
ntbl.Fields.Append fld
CurrentDb.TableDefs.Append ntbl
RefreshDatabaseWindow
For Each tbl In CurrentDb.TableDefs
' exclude the system tables
If tbl.Attributes = 0 Then
' exclude table tblnames
If tbl.Name <> "tblnames" Then
Set rcd = CurrentDb.OpenRecordset("tblnames", dbOpenDynaset, dbAppendOnly)
With rcd
.AddNew
.Fields![table_name].Value = tbl.Name
.Update
.Close
End With
End If
End If
Next
DoCmd.SetWarnings (True)
End Sub


Download Access Database