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

 

[aziz rasul]

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