By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,040 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

how to modify database documentor

P: 1
Access 365 Tab "database tool" select "database documenter", select only table properties. It prints 30 some pages without table name. I: only want table name, last update, record count. Ideally save as a table or excel spreadsheet, so I can track weekly updates.
3 Weeks Ago #1
Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,619
  1. A simple approach to resolving this issue would be as follows:
    1. Create a Table named tblTableUpdates which consists of the following Fields:
      Expand|Select|Wrap|Line Numbers
      1. [TName] - {SHORT TEXT}
      2. [Last Updated] - {DATE/TIME}
      3. [Records] - {NUMBER/LONG}
    2. Execute Code that will iterate thru all the Non-System Tables in your Database and add their Name, Last Updated, and Record Count Data to tblTableUpdates.
  2. Code Definition:
    Expand|Select|Wrap|Line Numbers
    1. Dim tdf As DAO.TableDef
    2. Dim strSQL As String
    3.  
    4. For Each tdf In CurrentDb.TableDefs
    5.   With tdf
    6.     If .Name Like "MSys*" Or .Name Like "USys*" Then
    7.       'ignore these System Tables
    8.     Else
    9.       strSQL = "INSERT INTO tblTableUpdates ([TName], [Last Updated],[Records]) Values('" & _
    10.                 .Name & "',#" & .LastUpdated & "#," & .RecordCount & ")"
    11.       CurrentDb.Execute strSQL, dbFailOnError
    12.     End If
    13.   End With
    14. Next
  3. You also Query MSysObjects:
    Expand|Select|Wrap|Line Numbers
    1. SELECT MSysObjects.Name, MSysObjects.DateUpdate, DCount("*",[Name]) AS Records
    2. FROM MSysObjects
    3. WHERE MSysObjects.Name Not Like 'MSys*' And MSysObjects.Name Not Like 'USys*' AND 
    4. MSysObjects.Name Not Like 'f_*' and MSysObjects.Type=1;
    5.  
  4. Hopefully, this is what you are looking for.
3 Weeks Ago #2

NeoPa
Expert Mod 15k+
P: 31,347
It seems like you're looking in the wrong place for that info. It will print names if you select the tables and clear all other options. It won't give you last-updated or record count.

On the other hand all that information is available one way or another - just not via the Documenter feature. ADezii gives one approach. You can find a fully developed tool for free at Code Documenter.

NB. As non-commercial work and actually provided by one of our own members who's also an MS Access MVP, this link is acceptable.
3 Weeks Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.