[AccessD] Record Count for all tables PART 2

Mark A Matte markamatte at hotmail.com
Tue Mar 11 16:24:20 CDT 2008


Thanks Drew,

Looks great...I'll test tomorrow.

Thanks Again,

Mark A. Matte


> Date: Tue, 11 Mar 2008 16:00:44 -0500
> From: DWUTKA at marlow.com
> To: accessd at databaseadvisors.com
> Subject: Re: [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.
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

_________________________________________________________________
Climb to the top of the charts! Play the word scramble challenge with star power.
http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_jan



More information about the AccessD mailing list