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

R

Ramzi

New member
Local time
Today, 20:58
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
 
Sort by date Sort by votes
Guys!
Basically i have a table
ID
ObjectType
ObjectName
HasAccess

I want to get the names of all Forms and Reports in this table instead typing one by one.

Anyone who can help me to get ride on it.
 
Upvote 0 Downvote
Here's a table that is useful when you write queries against MSysObjects. To find out what object types you have run a query on MSysObjects and look at all the Type values. It is possible that this table is not complete because I don't have any databases with a particular object.
1710695235472.png
 
Upvote 0 Downvote
Ramzi said:
Anyone who can help me to get ride on it.
Click to expand...
Use the MSysObjects table. Create your own copy of the table I pasted. That will make it easy for you to select the objects you are interested in and give them a "type" name.
This query will give you just the form's and reports.

1710695698818.png
 
Upvote 0 Downvote
This query will list all your tables:
SELECT DISTINCT MSysObjects.Name, MSysObjects.Type, MSysObjects.Flags
FROM MSysObjects
WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Name, MSysObjects.Type, MSysObjects.Flags;
 
Upvote 0 Downvote
Ramzi said:
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
Click to expand...
Why is it necessary to do this? There is already a hidden table in Access that already maintains this information. What you are doing is redundant and a waste of precious space in the database. Unless you are doing this for educational purposes and training exercises working with Recordsets, I recommend otherwise, echoing what others have stated above.

You can visualize the Access Hidden Tables by enabling the "Show Hidden Objects" as seen here. Click your right mouse on the All Access Objects and select Navigation Options.

Edit: You will need to enable "Show System Objects" too.
1711204292611.png




Here is a simple Query reading the MSysObjects Table. I rename the [Name] Column to [Object_Name] because [Name] is a reserved word and you will run into issues referring to it.

I included the Connecd and Database Columns because they are useful while working with linked Tables to Excel, Access, SQL Server, etc.
1711204610564.png


You can build off of the existing Access information that is already built-in. Why build it again!

You can create an INSERT Query based on the MSysObject and be done with it. You can join your Obejct Table to the MSysObject Table to remove Objects that you have already inserted into you table.

One caveat you need to consider is when you renamed an object. I would be better to store the ObjectID rather than the Name.
 
Last edited:
Upvote 0 Downvote
Ramzi said:
Actually i want to save the names in my own table to give the permission for users where authorized users can access the forms reports.
Click to expand...
Ramzi said:
Actually i want to save the names in my own table to give the permission for users where authorized users can access the forms reports.
Click to expand...
You can build off of the existing Access information that is already built-in. Why build it again!

You can create an INSERT Query based on the MSysObject and be done with it. You can join your Obejct Table to the MSysObject Table to remove Objects that you have already inserted into you table.

One caveat you need to consider is when you renamed an object. It would be better to store the ObjectID rather than the Name.
 
Upvote 0 Downvote
Ramzi,

Based on your original request, saving names of tables/forms and reports to your own table, you have been given various options -all relating to MSysObjects. However, it seems to me that your underlying issue is assigning roles and permissions to various users, and that your desired table is but a step in that process. You may want to research role based access control (RBAC) for more information.

You might find this post/thread offers some insight. And this summary post.
 
Upvote 0 Downvote
@spaLOGICng The OP needs a table to store security information for each object for each user but he technically doesn't need to "duplicate" the MSysObjects table. His security table can use the ID of the MySysObjects table as the FK.
 
Upvote 0 Downvote
Pat Hartman said:
@spaLOGICng The OP needs a table to store security information for each object for each user but he technically doesn't need to "duplicate" the MSysObjects table. His security table can use the ID of the MySysObjects table as the FK.
Click to expand...
Yes, I corrected myself in my most recent comment just two responses above yours, once I read about the security requirements. I, like you, would use as much of the built-in objects as possible. In my corrected statement, I mentioned using the ObjectID of the Table rather than the name, in the event he wanted to rename an object. The Foreign Key I did not mention, but it would be very useful if he add referential integrity for the cascade delete.
 
Upvote 0 Downvote
Learned something in this thread! I have always use Application.CurrentProject for the All(Object) collections and bodged it with iterating querydefs for queries . Now I can swap swap to using Application.Currentdata for queries. Proves that no matter how much you think you know there's always a lot to learn!
 
Last edited:
Upvote 0 Downvote
DickyP said:
Learned something in this thread! I have always use Application.CurrentProject for the All(Object) collections and bodged it with iterating querydefs for queries . Now I can swap swap to using Application.Currentdata for queries. Proves that no matter how much you think you know there's always a lot to learn!
Click to expand...
You can refer to AllTables, AllQueries, AllForms, AllReportss, AllMacros, and AllModules under Application.CurrentProject.

You can loop through each one or refer to a specific object by including its optional name, such as Application.CurrentProject.AllForms("ObjectName").IsLoaded and test the various states or other properties such as I have here testing to see if the form is loaded.
 
Upvote 0 Downvote
DickyP said:
Now I can swap swap to using Application.Currentdata for queries. Proves that no matter how much you think you know there's always a lot to learn!
Click to expand...
Or, you could use the MSysObjects table in a query that uses the object type values that I posted.
 
Upvote 0 Downvote
Just as a cautionary comment, using Application.CurrentProject is safer than MSysObjects because ANY TIME that you are directly dinking around with a system table, one slip-up and you suddenly have no database. Sys tables are dynamite with a nitroglycerin detonator. Even with 20+ years of experience, I don't like mucking about in MSysxxxx tables any time. Even though sometimes that is what you have to do.
 
Upvote 0 Downvote
Pat Hartman said:
Or, you could use the MSysObjects table in a query that uses the object type values that I posted.
Click to expand...
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.
 
Upvote 0 Downvote
The_Doc_Man said:
Just as a cautionary comment, using Application.CurrentProject is safer than MSysObjects because ANY TIME that you are directly dinking around with a system table, one slip-up and you suddenly have no database. Sys tables are dynamite with a nitroglycerin detonator. Even with 20+ years of experience, I don't like mucking about in MSysxxxx tables any time. Even though sometimes that is what you have to do.
Click to expand...
Most system tables, including MSysObjects are read only. You CANNOT mess them up by using them

DickyP said:
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.
Click to expand...
Actually neither method is completely accurate
 
Upvote 0 Downvote
isladogs said:
Most system tables, including MSysObjects are read only. You CANNOT mess them up by using them


Actually neither method is completely accurate
Click to expand...
Agreed to both but MSysObject never updates unbound forms and reports .DateUpdate, whereas CurrentProject .DateModified is good enough for sorting.
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

A
Match two numbers and then update
Replies
10
Views
202
Gasman
Gasman
L
Consuming an API to retrieve data and store it in a Microsoft Access table.
Replies
3
Views
229
lbcurrier
L
adhoustonj
How many db's do I really have open?
Replies
5
Views
320
Pat Hartman
P
A
Restrictions Update Message
Replies
17
Views
272
Gasman
Gasman
N
Solved How to grab the last updated record in a table
Replies
10
Views
323
June7
June7

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom