Database Objects Properties

[PR]

I want to be able to create a table that contains all the database objects by
name, created date and modify date...

is there a way of doing this... or is there another way to achieve what I am
trying to do...

I have tried to use the following code my it does not pick up all objects or
names...

<<<<<Code Start>>>>>
Function DisplayContainers(strDatabase As String, intCollection As Integer)
As Integer

Dim DefaultWorkspace As Workspace
Dim CurrentDatabase As Database
Dim MyContainer As Container
Dim MyDocument As Document
Dim rst As Recordset
Dim i As Integer
Dim j As Integer
Set DefaultWorkspace = DBEngine.Workspaces(0)
If strDatabase = "" Then
Set CurrentDatabase = DefaultWorkspace.Databases(0)
Else
Set CurrentDatabase = DefaultWorkspace.OpenDatabase(strDatabase)
End If

If intCollection = -1 Then
For j = 0 To CurrentDatabase.Containers.Count - 1
Set MyContainer = CurrentDatabase.Containers(j)
Next j
Else
Set MyContainer = CurrentDatabase.Containers(intCollection)
For i = 0 To MyContainer.Documents.Count - 1
Set rst = CurrentDatabase.OpenRecordset("tlkpMasterContainer s",
dbOpenDynaset)
Set MyDocument = MyContainer.Documents(i)
With rst
..AddNew
!itemName = MyDocument.Name
!itemDateCreate = MyDocument.DateCreated
!itemDateModify = MyDocument.LastUpdated
..Update
End With
Next i
End If
If strDatabase <> "" Then
CurrentDatabase.Close
Set CurrentDatabase = Nothing
End If
DisplayContainers = True
End Function

<<<<<code ends>>>>>

Regards PR

 

[Tom Wickerath]

Which version of Access are you using? If you are not using the newest
version, Access 2007, then there is a free add-in tool that you can download
and install for Access 97, 2000, 2002 or 2003. This tool is called CSD Tools,
created by Access MVP alumni Jeff Conrad. It is available here:

http://www.accessmvp.com/JConrad/accessjunkie/csdtools.html

Jeff's table provides field information as well, when you use it to document
tables. I suppose if you didn't want this level of granularity, you could
always save the results as either a rich text file, or an Excel file, and
remove this extra information. The nice thing about this tool is that once
you install it, it is available for any .mdb database that you are working
on, on your PC. There's no need to import custom VBA code into each database.

By the way, declarations such as these:

Dim MyDocument As Document
Dim rst As Recordset

should use explicit declaration, to prevent run-time errors that can result
from reference priority. For example:

Dim MyDocument As DAO.Document
Dim rst As DAO.Recordset

By specifying DAO.Document, you prevent the possibility of getting
Word.Document, if the Word Object library has a higher priority versus the
DAO object library. By specifying DAO.Recordset, you prevent the possibility
of getting an ADO recordset, if the ADO library has a higher priority. These
types of errors do not show up as compile errors--they only show up as
run-time errors. More information here:

ADO and DAO Library References in Access Databases
http://www.accessmvp.com/TWickerath/articles/adodao.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Pete D.]

Hi, Just so you know Jeff's CSD tool works in 2007 also. Pete

 

[Tom Wickerath]

Hi Pete,

I didn't know that. Jeff includes a disclaimer at the top of the page that
reads:
"Important - This add-in is not compatible yet with Access 2007."

You have verified that it works okay? Would this be for the .mdb file format
only, or does it also work with the newer .accdb format?

Thanks for the information.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Pete D.]

I have used it for a while and added back to 2007 and so far it seems to
work fine in my accdb. Jeff might know something I haven't found yet but
even if it has something I haven't found yet it still beats the native
documenter.

 

[Jeff Conrad [MSFT]]

Correct Pete.

Disclaimer:
"Important - This add-in is not compatible yet with Access 2007."

Translation:
"I haven't done exhaustive testing on 2007 ACCDB file format, but for the most part, everything
should just "work" so just bear that in mind when using with 2007."


I haven't added any ACCDB format-only functionality.

--
Jeff Conrad - Access Junkie - MVP Alumnus
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com