How do you create an Access SQL Query to list all Database Objects, or Queries only, with their Size, Date Created and Date Modified

[jpr.c...@gmail.com]

... or any good Access book which would cover this subject.
Thank you for your help.
J.P.

[Rob Parker]

Most of the information you mention is contained in the hidden system table
MSysObjects - with the exception of size, and I don't know any way of
getting that information. You can write a query - using the standard query
builder, if that's your preference - to retrieve what you want. You'll
probably need to do some googling to find what some of the values used in
this table represent, but it's readily available (don't have any links to
hand on the computer I'm using at the moment).

The key thing is to use the Options dialog box (from the Tools - Options
menu in A2003 and earlier; or by right-clicking the Navigate bar and
choosing Navigation options in A2007 (and later, I assume)) to make both
System and Hidden objects visible. Until you do so, you can't browse the
system tables (there are several of them) or use them in a query.

HTH,

Rob


<jpr.c...@gmail.com> wrote in message
news:5e29660d-e08e-432f...@googlegroups.com...

[Phil]

This will get you started

Function GetDescription(ObjName As String, ObjType As Long) As String
' Get descriptions of Forms, Reports. Queries etc

On Error GoTo GetDescriptions_Err

Select Case ObjType
Case -32768 ' Forms
GetDescription cription =
CurrentDb.Containers!Forms.Documents(ObjName).Properties("Description")

Case -32766 ' Macro Scripts
GetDescription cription =
CurrentDb.Containers!Scripts.Documents(ObjName).Properties("Description")

Case -32764 ' Reports
GetDescription cription =
CurrentDb.Containers!Reports.Documents(ObjName).Properties("Description")

Case -32761 ' Modules
GetDescription cription =
CurrentDb.Containers!Modules.Documents(ObjName).Properties("Description")

'Case -32758 ' Admin

'case -32752 ' Access Info

Case 1 ' tables
GetDescription cription =
CurrentDb.TableDefs(ObjName).Properties("Description") ' Or ("DateCreated")
etc

Case 2 ' Databases
GetDescription cription =
CurrentDb.Containers!Databases.Documents(ObjName).Properties("Description")

Case 3 ' Objects
GetDescription cription =
CurrentDb.Containers!Objects.Documents(ObjName).Properties("Description")

Case 5 ' Queries
GetDescription cription =
CurrentDb.QueryDefs(ObjName).Properties("Description")

Case 6 ' Attached tables
GetDescription cription = "Attached " &
CurrentDb.Containers!TableDefs.Documents(ObjName).Properties("Description")

'Case 9 ' SQL

Case Else
GetDescription = "Unavailable Information"

End Select
Exit Function

GetDescriptions_Err:
If If Err = 3270 Or Err = 3265 Then ' Property doesn't exist
Resume Next
Else
MsgBox Err.Description
End If

End Function

Private Sub Form_Open(Cancel As Integer)

Dim MyDb As Database
Dim SQLStg As String
Dim RecCount As Long

Set MyDb = CurrenrDb

SQLStg LStg = "SELECT DISTINCTROW
IIf([Type]=5,'Queries',IIf([Type]=-32768,'Forms'," SQLStg = SQLStg &
"IIf([Type]=1,'Tables',IIf([Type]=6,'Attached Tables'," SQLStg = SQLStg &
"IIf([Type]=-32764,'Reports'))))) AS ObjectType, " SQLStg = SQLStg &
"MSysObjects.Name, " SQLStg = SQLStg &
"GetDescription([MSysObjects].[Name],[MSysObjects].[Type]) AS Description, "
SQLStg = SQLStg & "MSysObjects.Flags, InStr([Name],'Sub') AS Expr2,
MSysObjects.Type " SQLStg = SQLStg & "FROM MSysObjects "
SQLStg = SQLStg & "IN '" & MyDb.Name & "' "
SQLStg LStg = SQLStg & "WHERE (((MSysObjects.Flags) = 0 Or
(MSysObjects.Flags) = 16 " SQLStg = SQLStg & "Or (MSysObjects.Flags) = 128 "
SQLStg LStg = SQLStg & "Or (MSysObjects.Flags) = 2097152) And ((InStr([Name],
'Sub')) = 0) " SQLStg = SQLStg & "And ((MSysObjects.Type) <> 2 And
(MSysObjects.Type) <> 3 " SQLStg = SQLStg & "And (MSysObjects.Type) <> 8 And
(MSysObjects.Type) <> -32758 " SQLStg = SQLStg & "And (MSysObjects.Type) <>
-32761 And (MSysObjects.Type) <> -32757 " SQLStg = SQLStg & "And
(MSysObjects.Type) <> -32766) And ((Left([Name], 1)) <> '~'))" SQLStg =
SQLStg & "ORDER BY IIf([Type]=5,'Queries',IIf([Type]=-32768,'Forms'," SQLStg
= SQLStg & "IIf([Type]=1,'Tables',IIf([Type]=6,'Attached Tables'," SQLStg =
SQLStg & "IIf([Type]=-32764,'Reports'))))), MSysObjects.Name;"

Me.RecordSource = SQLStg
Me.RecordsetClone.MoveLast
RecCount = Me.RecordsetClone.RecordCount

End Sub

Phil

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

[Phil]

On 25/02/2014 00:57:53, wrote:

[Access Developer]

Unless you are planning on creating an application to sell to people who
want to list all those items, it would probably be worth your while to do
some Googling or Binging because there are a lot of for-free and for-fee
applications or addins to document database objects.

FMS has software packages for that purpose, but they are not free.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

<jpr.c...@gmail.com> wrote in message
news:5e29660d-e08e-432f...@googlegroups.com...