Save the names of tables, forms, and reports in a Microsoft Access database to a table named "ObjectNames. (1 Viewer)

Ramzi

New member
Local time
Today, 05:37
Joined
May 8, 2022
Messages
13
Public Sub SaveObjectNames()
On Error GoTo ErrorHandler

Dim db As DAO.Database
Dim obj As AccessObject
Dim strSQL As String
Dim rs As DAO.Recordset
Dim objectName As String

' Open the current database
Set db = CurrentDb

' Loop through each object in the current database and insert names into the table
For Each obj In db.AllTables
objectName = obj.Name
If Not IsObjectNameExist("Table", objectName) Then
InsertObjectName "Table", objectName
End If
Next obj

For Each obj In db.AllForms
objectName = obj.Name
If Not IsObjectNameExist("Form", objectName) Then
InsertObjectName "Form", objectName
End If
Next obj

For Each obj In db.AllReports
objectName = obj.Name
If Not IsObjectNameExist("Report", objectName) Then
InsertObjectName "Report", objectName
End If
Next obj

' Cleanup
Set obj = Nothing
Set db = Nothing

MsgBox "Object names have been saved to the table.", vbInformation
Exit Sub

ErrorHandler:
MsgBox "An error occurred: " & Err.Description, vbExclamation
End Sub

Private Function IsObjectNameExist(ByVal objectType As String, ByVal objectName As String) As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "SELECT COUNT(*) AS CountOfRecords " & _
"FROM ObjectNames " & _
"WHERE ObjectType='" & objectType & "' AND ObjectName='" & objectName & "'"

Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
IsObjectNameExist = (rs!CountOfRecords > 0)
End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Function

Private Sub InsertObjectName(ByVal objectType As String, ByVal objectName As String)
Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb
strSQL = "INSERT INTO ObjectNames (ObjectType, ObjectName) " & _
"VALUES ('" & objectType & "', '" & objectName & "')"
db.Execute strSQL
Set db = Nothing
End Sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:37
Joined
Feb 28, 2001
Messages
27,574
Most system tables, including MSysObjects are read only

In the last few versions. But we have had on this very forum a few complaints/questions from folks who found the way to update an MSysxxx table (to their deep regret.)
 

isladogs

MVP / VIP
Local time
Today, 01:37
Joined
Jan 14, 2017
Messages
18,309
In the last few versions.
No. The crucial system tables including MSysObjects/MSysQueries/MSysRelationships & MSysACEs have been read only in all versions since 1.0

You can update certain 'non-crucial' system tables such as MSysIMEXSpecs/MSysIMEXColumns but doing so will not render your database unusable.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:37
Joined
Feb 19, 2002
Messages
43,854
The trouble with using MSysObjects is that DateUpdate field is not as accurate or up to date as the CurrentProject.Allobjects(objectname).DateModified which I use for various purposes.
I've posted regarding that flaw on multiple occasions.
 

Users who are viewing this thread

Top Bottom