Row Count From All Tables 3

[OrWolf]

I'm looking for some code samples to help me cycle through all of the tables in my database and retrieve the record from each. I'll then write those values to a new table using an SQL insert statement. Suggestions please?

 

[hcisteve]

Why not just create a query in Access that includes all of your tables. Then request the field you are looking for matched to your criteria. You can then either run the query in VB using:

DoCmd.OpenQuery

Or you can look at the SQL code for the query you created in Access (use a right mouse click), and modify the syntax so it will work in VB.

 

[hcisteve]

Oh, yes and have your query be a "Make Table" query that puts those selected values in your new table.

 

[Tranman]

OrWolf,
This does it with a recordset instead of an insert, but the effect is the same. It omits system tables with the "mSys" code.

You could just substitute the "Insert" if you wish.

Table is: TABLE_INFO
Fields are: TBL_NAME (character)
TBL_ROWCOUNT (long integer)


Tranman

 

[Tranman]

Guess you'd like the code, too.

Private Sub CountAllTablesRows()
Dim rs As New ADODB.Recordset
Dim rsRC As New ADODB.Recordset
Dim strTbName As String
Dim lngRowCount As Long
Dim tbl As TableDef
CurrentProject.Connection.Execute "Delete from TABLE_INFO"
rs.Open "TABLE_INFO", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
For Each tbl In CurrentDb.TableDefs
  Select Case Left(tbl.Name, 4)
    Case "mSys"
    Case Else
      rs.AddNew
      rsRC.Open "Select count(*) as The_Count from [" & tbl.Name & "]", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
      rs.Fields("TBL_NAME") = tbl.Name
      rs.Fields("TBL_ROWCOUNT") = rsRC.Fields("The_Count")
      rs.Update
      rsRC.Close
      Set rsRC = Nothing
      Debug.Print tbl.Name
  End Select
Next
rs.Close
Set rs = Nothing
End Sub

 

[RoyVidar]

I think the tabledefs have a .RecorCount property, so (due to laziness, I don't bother typing it all)

[tt]for each tbl In currentdb.tabledefs
if left(tbl.Name, 4)<>"mSys" then
debug.print tbl.name, tbl.recordcount
end if
next tbl[/tt]

Note however, that for linked tables, this will probably return -1.

Roy-Vidar

 

[MichaelRed]

... and getting further afield ... To What purpose? Such a "device" is only fleetingly 'correct' and of poerhaps even less real interest (value if you must). It 'smells like' the old issue (oft discussed here abouts) of the count of hte number of lines of code in the entire app. This usually elicits a spate of issues and examples of the utter ueslessness thereof. What constitutes a "Line"? Should Blanks count? Should continuations coune individually or as the composite (aggregate anyond) they represent? Does a code "style" which works at doing the least ammount of "work" get penalized for the style? If so, what criteria (programmatically speaking, or course) identifies this and what level of penalty should be applied?

Some several responses will assuredly chastize my thoughts as being not just impure, but obviously inappropiate to the subject. These will certainly point out that Lines of Code" are naturally of a different nature than records and so the characteristics (or characterizations) cannot be made.

I, opn the other hand, revert to the simplistic defense that records are defined by humans, using the same (or at least similar) cognative powers and have much in common. Normalization rules (and / or the lack thereof) may have a large impact on the aggregate record count. Whilst I could ramble on ad naseum (or even more ad naseum, if you prefer). I will count on the asembled company to continue the discussion in more ernest tones, but leave this final quotation:

"Figures don't lie but liars do figure"

Which, I believe, was first used in reference to ye grande olde stats class.





MichaelRed

 

[TheAceMan1]

How are ya OrWolf . . . . .

Or you can try this ([blue]You![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim db As DAO.Database, tdf As DAO.TableDef, SQL As String
   Dim tblNam As String, rowCnt As Long
   
   Set db = CurrentDb()
   
   For Each tdf In db.TableDefs
      If Left(tdf.Name, 4) <> "MSys" Then
         tblNam = tdf.Name
         rowCnt = Nz(DCount("*", tblNam), 0)
         
         SQL = "INSERT INTO [purple][b]TableName[/b][/purple] ([purple][b]NameField[/b][/purple], [purple][b]CountField[/b][/purple]) " & _
               "SELECT '" & tblNam & "', " & rowCnt & ";"
         DoCmd.RunSQL SQL
      End If
   Next
   
   Set tdf = Nothing
   Set db = Nothing
[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

How are ya MichaelRed . . . . .

Highest respects to you . . . . Your post compelled me to leave the following:

[purple]Proverbs 19.21[/purple]
[purple]There is a way that seems right to a man . . . but in the end, it is Gods will that will prevail.[/purple]

See Ya! . . . . . .

 

[Tranman]

OrWolf,
Are you sorry you asked yet???

RV, the recordcount property doesn't work with linked tables (always returns -1) that's why I didn't use it.

Speaking of philosophy, I wonder why we [red]ALL[/red] used the [red]OLD DAO Tabledefs collection [/red]to enumerate the tables. (I have an excuse--I AM old.) The AllTables collection would actually be the more up-to-date method:

Sub AllTables()
Dim aobj As AccessObject
For Each aobj In CodeData.AllTables
Debug.Print aobj.Name
Next
End Sub

And again speaking of philosophy, there are several very valid reasons for wanting to know how many table rows are in a given schema. We use this very technique to let us know when we need to do cleanup and move older records to archive(for performance purposes). It can be a rough indicator that we need to expand tablespace. Sometimes we're just curious...

Tranman

 

[RoyVidar]

Tranman - yes I know, I even stated it - last line of previous reply ;-)

Ok - for the fun of it, a tad ADO, then, but only dao tabledefs, I think, epxoses a recordcount property, so...

[tt]dim rs as adodb.recordset
dim cn as adodb.connection
set cn=currentproject.connection
set rs=cn.openschema(adschematables)
do while not rs.eof
if ((rs.fields("TABLE_TYPE").value <> "VIEW") and _
(left$(rs.fields("TABLE_NAME").value,4)<>"msys")) then
debug.print rs.fields("TABLE_NAME").value, _
cn.execute("select count(*) from [" & _
rs.fields("TABLE_NAME").value & "]",,adcmdtext)(0)
end if
rs.movenext
loop
rs.close
set rs=nothing
set cn=nothing[/tt]

Roy-Vidar