|
Subject:
|
How to do record count for Link table in access
|
|
Posted By:
|
ayazhoda
|
Post Date:
|
4/16/2008 4:43:14 AM
|
Hi All
I've wrote a module, which does record count for each table in database
Question: How can only count records from linked table?
currently it does count records for each table in database.
Option Compare Database
Sub Recordsintables()
Dim x As Integer
Dim count As Integer
Dim Name As String
Dim i As Integer
Dim tables As String
Dim r As New ADODB.Recordset
'Debug.Print "Total Table In Database include system Table: " & CurrentDb.TableDefs.count
For i = 0 To CurrentDb.TableDefs.count - 1
DoEvents
tables = CurrentDb.TableDefs(i).Name
If Left(tables, 4) <> "dbo_" And Left(tables, 4) <> "bak_" And Left(tables, 4) <> "MSys" Then
Debug.Print "Table Name: " & tables & " - Count: " & recordcount(tables)
End If
Next i
End Sub
Function recordcount(tbl As String) As Long
Dim c As New ADODB.Command
c.ActiveConnection = CurrentProject.Connection
c.CommandText = "select count(*) from [" + tbl + "]"
Dim r As ADODB.Recordset
Set r = c.Execute
r.MoveFirst
recordcount = r.Fields(0).Value
r.Close
End Function
|
|
