simonc said:
I'm looking through a database which is used by a piece of software I
don't
have. There are 63 tables, many of which are empty and most of which have
names which don't give obvious clues as to what they're for. Is there an
easy
way of outputting basic statistics of this database - ie for each table a
list of the fields and the number of records it contains?
Grateful for assistance.
The built-in Documenter will give you a list of fields, but not the number
of records. Here's some example code that will list table names, number of
records, and field names ...
Public Sub ListTables(ByVal DatabaseName As String, ByVal OutputFile As
String)
Dim db As DAO.Database
Dim intFile As Integer
Dim tdfs As DAO.TableDefs
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim flds As DAO.Fields
Dim fld As DAO.Field
If DatabaseName = CurrentProject.FullName Then
Set db = CurrentDb
Else
Set db = DBEngine.OpenDatabase(DatabaseName)
End If
intFile = FreeFile
'this will over-write any existing file with the specified name, use
with care
Open OutputFile For Output As intFile
Set tdfs = db.TableDefs
For Each tdf In tdfs
If LCase$(Left$(tdf.Name, 4)) <> "msys" Then
Print #intFile, "Table: " & tdf.Name
Set rst = db.OpenRecordset("SELECT Count(*) FROM " & tdf.Name)
Print #intFile, "Records: " & rst.Fields(0)
rst.Close
Print #intFile, "Fields:"
Set flds = tdf.Fields
For Each fld In flds
Print #intFile, , fld.Name
Next fld
Print #intFile,
End If
Next tdf
Close #intFile
If DatabaseName = CurrentProject.FullName Then
db.Close
End If
MsgBox "Finished"
End Sub
Example of use, in the Immediate window ...
listtables "c:\SomeFolder\SomeFile.mdb", "c:\SomeFolder\SomeFile.txt"
.... where "SomeFile.mdb" is the database you want to analyse, and
"SomeFile.txt" is the name of the text file to which the output will be
written. Note that this will over-write any existing file with that name, so
be careful.
