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

[Ramzi]

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

 

[Ramzi]

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.

 

[Pat Hartman]

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.

 

[Ramzi]

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.
View attachment 113112

Thanks alot I got my problem solved.....

 

[Pat Hartman]

Anyone who can help me to get ride on it.

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.

 

[LarryE]

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;

 

[Ramzi]

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.

View attachment 113113

Thanks Pat Hartman i done the same and got my issue resolved.

 

[isladogs]

View Database Objects & Metadata

This article includes an example app used to view all objects in a database together with their properties and metadata.

www.isladogs.co.uk

 

[Pat Hartman]

Thanks Pat Hartman i done the same and got my issue resolved.

You're welcome

 

[spaLOGICng]

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

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.

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.

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.

 

[spaLOGICng]

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.

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.

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.

 

[jdraw]

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.

 

[Pat Hartman]

@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.

 

[spaLOGICng]

@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.

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.

 

[DickyP]

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!

 

[spaLOGICng]

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!

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.

 

[Pat Hartman]

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!

Or, you could use the MSysObjects table in a query that uses the object type values that I posted.

 

[The_Doc_Man]

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.

 

[DickyP]

Or, you could use the MSysObjects table in a query that uses the object type values that I posted.

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.

 

[isladogs]

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.

Most system tables, including MSysObjects are read only. You CANNOT mess them up by using them

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.

Actually neither method is completely accurate

 

[DickyP]

Most system tables, including MSysObjects are read only. You CANNOT mess them up by using them


Actually neither method is completely accurate

Agreed to both but MSysObject never updates unbound forms and reports .DateUpdate, whereas CurrentProject .DateModified is good enough for sorting.