[AccessD] Record Count for all tables PART 2

No problem, here's one option, though the debug print may not work
right, since it might be longer then the list allows, I'll post a
modified solution in a minute.

Option Compare Database
Public Enum RecCountType
    intTableOnly = 1
    intQueryOnly = 2
    intTableAndQuery = 3
End Enum
Private Function FieldDataTypeString(intType As ADODB.DataTypeEnum) As
String
Dim strTemp As String
Select Case intType
    Case adInteger
        strTemp = "Long"
    Case adDouble
        strTemp = "Double"
    Case adCurrency
        strTemp = "Currency"
    Case adUnsignedTinyInt
        strTemp = "Byte"
    Case adVarWChar, adLongVarWChar
        strTemp = "String"
    Case adDate
        strTemp = "Date"
    Case adBoolean
        strTemp = "Boolean"
End Select
If strTemp <> "" Then
    FieldDataTypeString = strTemp
Else
    FieldDataTypeString = "UNKNOWN"
End If
End Function
Function GetAllTableRecordCount(intCountType As RecCountType, Optional
ShowFields As Boolean = False)
Dim rsTables As ADODB.Recordset
Dim rs As ADODB.Recordset
Dim i As Long
Set rsTables = CurrentProject.Connection.OpenSchema(adSchemaTables)
If rsTables.EOF = False Then rsTables.MoveFirst
Do Until rsTables.EOF = True
    If intCountType = intTableOnly Or intCountType = intTableAndQuery
Then
        If rsTables.Fields("TABLE_TYPE") = "TABLE" Then
            Set rs = New ADODB.Recordset
            rs.Open "SELECT Count(*) FROM " &
rsTables.Fields("TABLE_NAME").Value, CurrentProject.Connection,
adOpenKeyset, adLockReadOnly
            rs.MoveFirst
            Debug.Print rsTables.Fields("TABLE_NAME") & " - Record
Count: " & rs.Fields(0).Value
            rs.Close
            Set rs = Nothing
            If ShowFields Then
                Set rs = New ADODB.Recordset
                rs.Open "SELECT TOP 1 * FROM " &
rsTables.Fields("TABLE_NAME").Value, CurrentProject.Connection,
adOpenKeyset, adLockReadOnly
                For i = 0 To rs.Fields.Count - 1
                    Debug.Print rsTables.Fields("TABLE_NAME").Value & "
Field: " & rs.Fields(i).Name & " Type: " &
FieldDataTypeString(rs.Fields(i).Type)
                Next i
                rs.Close
                Set rs = Nothing
            End If
        End If
    End If
    If intCountType = intQueryOnly Or intCountType = intTableAndQuery
Then
        If rsTables.Fields("TABLE_TYPE") = "VIEW" Then
            Set rs = New ADODB.Recordset
            rs.Open "SELECT Count(*) FROM " &
rsTables.Fields("TABLE_NAME").Value, CurrentProject.Connection,
adOpenKeyset, adLockReadOnly
            rs.MoveFirst
            Debug.Print rsTables.Fields("TABLE_NAME") & " - Record
Count: " & rs.Fields(0).Value
            rs.Close
            Set rs = Nothing
        End If
        If ShowFields Then
                Set rs = New ADODB.Recordset
                rs.Open "SELECT TOP 1 * FROM " &
rsTables.Fields("TABLE_NAME").Value, CurrentProject.Connection,
adOpenKeyset, adLockReadOnly
                For i = 0 To rs.Fields.Count - 1
                    Debug.Print rsTables.Fields("TABLE_NAME").Value & "
Field: " & rs.Fields(i).Name & " Type: " &
FieldDataTypeString(rs.Fields(i).Type)
                Next i
                rs.Close
                Set rs = Nothing
            End If
    End If
    rsTables.MoveNext
Loop
rsTables.Close
Set rsTables = Nothing
End Function

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Tuesday, March 11, 2008 2:27 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Record Count for all tables PART 2


Drew,

Worked like a charm!!!...But I guess I didn't ask enough
detail...because I was thought I would get the same Documentor
functionality like in A97...anyway...

Is there a similar way to get field names and types?

(Sorry for having to ask twice)

Thanks,

Mark A. Matte
The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.