recordcount

[aziz rasul]

I have written code which goes thru each table and does a count and places the values into a table. I use MoveLast & RecordCount to obtain the answers.

If I come across a table that contains no records I get a 3021 error. How can I check whether a table has any records before doing the RecordCount? I have been trying to use AbsolutePosition but can't get it to work. Here's the code so far.

With dbs
For Each tdf In .TableDefs
strTableName = tdf.Name
Set rst = .OpenRecordset(strTableName)
lngX = rst.AbsolutePosition + 1
rst.MoveLast
lngCount = rst.RecordCount
With rstCount
.AddNew
!TableName = strTableName
!RecordCount = lngCount
!DateofCount = Now()
.Update
End With
rst.Close
Next
End With

I get a run-time error on the lngX line i.e. 3251 (Operation is not supported for this type of object)

 

[Jon K]

Instead of opening recordsets, I think you can use DCount() in an "Insert Into" statement to put the values into your table.

The code would be something like this:

   .............
   .............
   Dim SQL As String
   
   For Each tdf In dbs.TableDefs
     If Left(tdf.Name, 4) <> "Msys" Then
     
       SQL = "Insert Into [yourTable]" & _
             " ([TableName], [RecordCount], [DateOfCount])" & _
             " values (""" & tdf.Name & """," & _
               DCount("*", tdf.Name) & ",'" & Now() & "')"
       
       dbs.Execute SQL
     End If
   Next

If Left(tdf.Name, 4) <> "Msys" will exclude the system tables.
.

 

[DonK]

On the other hand you can try using the EOF property. Try this:

Set rst = .OpenRecordset(strTableName)
If rst.EOF Then
MsgBox "The table contains 0 records."
End If

 

[May Parry22]

   SQL = "Insert Into [yourTable]" & _
         " ([TableName], [RecordCount], [DateOfCount])" & _
         " values (""" & tdf.Name & """," & _
            DCount("*", tdf.Name) & ",'" & Now() & "')"

Jon,

DateOfCount is obviously a Date/Time field. I think we should use the # signs to surround the value of Now() rather than using the single-quotes.

May

 

[Jon K]

May,
When we surround a date/time value with the # signs, by default, Access would treat the value as in US date format.

Since the member is in the UK, the # signs cannot work properly. For an append query, the single-quotes work on both US and UK systems, probably on many other systems too.
.

 

[Surjer]

just a guess here

With dbs
For Each tdf In .TableDefs
strTableName = tdf.Name
Set rst = .OpenRecordset(strTableName)
if not rst.eof = true then
lngX = rst.AbsolutePosition + 1
rst.MoveLast
lngCount = rst.RecordCount
else
lngCount = 0
end if
With rstCount
.AddNew
!TableName = strTableName
!RecordCount = lngCount
!DateofCount = Now()
.Update
End With
rst.Close
Next
End With

 

[Pat Hartman]

Each table has a RecordCount property. You don't need to open each table and move physically to the last record to get a count. Here's some code I use in my documentation db:

Sub Create_tblTables()

    Dim db As DAO.Database
    Dim fldLoop As DAO.Field
    Dim tblLoop As DAO.TableDef
    Dim TD1 As DAO.TableDef
    Dim TD2 As DAO.TableDef
    Dim QD1 As DAO.QueryDef
    Dim TempSet2 As DAO.Recordset
    Dim ThisDB As DAO.Database
    Dim strDatabase As String

    On Error GoTo Err_Create_tblTables
    strDatabase = Forms!frmPrintDoc!txtDBName [COLOR=MediumTurquoise]You'll need to change this line[/COLOR]
    Set ThisDB = CurrentDb()
    If strDatabase = "" Then
        Set db = CurrentDb()
    Else
        Set db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
    End If
    db.Containers.Refresh
    Set QD1 = ThisDB.QueryDefs!QdeltblTables
        QD1.Execute
    Set TD2 = ThisDB.TableDefs!tbltables
    Set TempSet2 = TD2.OpenRecordset

    For Each tblLoop In db.TableDefs
        If Left(tblLoop.Name, 4) = "msys" Or Left(tblLoop.Name, 1) = "~" Then
        Else
            TempSet2.AddNew
            TempSet2!TableName = tblLoop.Name
            TempSet2!RecordCount = tblLoop.RecordCount
            TempSet2!ConnectString = tblLoop.Connect
            TempSet2!SourceTableName = tblLoop.SourceTableName
            TempSet2!Updateable = tblLoop.Updatable
            On Error Resume Next ' the following property is only available when it is not null
            TempSet2!Description = tblLoop.Properties("Description")
            TempSet2.Update
        End If
    Next tblLoop

Exit_Create_tblTables:
    db.Close
    Exit Sub

Err_Create_tblTables:
    Select Case Err.Number
        Case 3043
            MsgBox "Please select a valid database", vbOKOnly
        Case 91   ' db was not opened so it cannot be closed.
            Exit Sub
        Case Else
            MsgBox Err.Number & "-" & Err.Description
    End Select
    Resume Exit_Create_tblTables    
End Sub

Create the two queries that the sub uses and create the table that it populates. The sub is getting the name of the database to analyze from a form. The form uses the common dialog box to let users browse to the db they want to analyze.

 

[aziz rasul]

Thanks for the input people. Got the code to work. Much appreciated.