summary of db content

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good afternoon,

Is there a simple way to extract a summary of a db's content. something
along the lines of:

-Number of Tables
-Number of fields within each table
-Number of Forms
-Number of Queries
-Number of Report

And is there a way to determine the number of lines of code within a db
(modules, forms... everything combined)?

Thank you,

Daniel
 
Number of tables, number of forms, number of queries and number of reports
are easy.

CurrentDb.TableDefs.Count and CurrentDb.QueryDefs.Count will give you the
number of tables and the number of queries respectively. Unfortunately, the
Forms and Reports collections only contain those Forms and Reports that are
currently open, so it's a little more involved for them: you need to use
CurrentDb.Containers("Forms").Documents.Count and
CurrentDb.Containers("Reports").Documents.Count respectively.

Number of fields withint each table is a bit more involved. You'd have to
loop through all of the tables and get the number of fields in each:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim lngTotalFields As Long

lngTotalFields = 0
Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
lngTotalFields = lngTotalFields + tdfCurr.Fields.Count
Next tdfCurr

Lines of code is possible as well, but unfortunately I've forgotten how. (Of
course, it's a pretty meaningless measure of anything...)
 
Here is some code to get you started on getting the lines of code in a
database.

Sub LinesOfCode()

Dim db As Database
Dim Doc As Document
Dim mdl As Module
Dim lngCount As Long
Dim lngFormsCode As Long
Dim lngReportsCode As Long
Dim i As Integer
Dim strForm As String

Set db = CurrentDb()
' count module LOC
For Each Doc In db.Containers("Modules").Documents
DoCmd.OpenModule Doc.Name
Set mdl = Modules(Doc.Name)

lngCount = lngCount + mdl.CountOfLines
Debug.Print mdl.CountOfLines, Doc.Name

Set mdl = Nothing
DoCmd.Close acModule, Doc.Name

Next

Debug.Print lngCount & " lines of code in Modules."

' count Code in Forms
For i = 0 To db.Containers("Forms").Documents.Count - 1
strForm = db.Containers("Forms").Documents(i).Name
DoCmd.OpenForm strForm, acDesign
lngFormsCode = lngFormsCode + Forms(strForm).Module.CountOfLines
Debug.Print strForm, Forms(strForm).Module.CountOfLines
DoCmd.Close acForm, strForm, acSaveNo
Next

Debug.Print "forms code = " & lngFormsCode

' count LOC in Reports
For i = 0 To db.Containers("Reports").Documents.Count - 1
strForm = db.Containers("Reports").Documents(i).Name
DoCmd.OpenReport strForm, acViewDesign
Reports(strForm).Visible = False

lngReportsCode = lngReportsCode + Reports(strForm).Module.CountOfLines
Debug.Print strForm, Reports(strForm).Module.CountOfLines
DoCmd.Close acReport, strForm, acSaveNo
Next

Debug.Print "reports code = " & lngReportsCode

Debug.Print "total = " & lngFormsCode + lngCount + lngReportsCode
End Sub
 
Thanks, John. I knew it was something relatively simple, but I couldn't find
the correct property to use.
 
Thank you both for your help!



John Spencer said:
Here is some code to get you started on getting the lines of code in a
database.

Sub LinesOfCode()

Dim db As Database
Dim Doc As Document
Dim mdl As Module
Dim lngCount As Long
Dim lngFormsCode As Long
Dim lngReportsCode As Long
Dim i As Integer
Dim strForm As String

Set db = CurrentDb()
' count module LOC
For Each Doc In db.Containers("Modules").Documents
DoCmd.OpenModule Doc.Name
Set mdl = Modules(Doc.Name)

lngCount = lngCount + mdl.CountOfLines
Debug.Print mdl.CountOfLines, Doc.Name

Set mdl = Nothing
DoCmd.Close acModule, Doc.Name

Next

Debug.Print lngCount & " lines of code in Modules."

' count Code in Forms
For i = 0 To db.Containers("Forms").Documents.Count - 1
strForm = db.Containers("Forms").Documents(i).Name
DoCmd.OpenForm strForm, acDesign
lngFormsCode = lngFormsCode + Forms(strForm).Module.CountOfLines
Debug.Print strForm, Forms(strForm).Module.CountOfLines
DoCmd.Close acForm, strForm, acSaveNo
Next

Debug.Print "forms code = " & lngFormsCode

' count LOC in Reports
For i = 0 To db.Containers("Reports").Documents.Count - 1
strForm = db.Containers("Reports").Documents(i).Name
DoCmd.OpenReport strForm, acViewDesign
Reports(strForm).Visible = False

lngReportsCode = lngReportsCode + Reports(strForm).Module.CountOfLines
Debug.Print strForm, Reports(strForm).Module.CountOfLines
DoCmd.Close acReport, strForm, acSaveNo
Next

Debug.Print "reports code = " & lngReportsCode

Debug.Print "total = " & lngFormsCode + lngCount + lngReportsCode
End Sub
Click to expand...
 
Back
Top