By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,463 Members | 774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,463 IT Pros & Developers. It's quick & easy.

using recordcount and not getting what is in the table

P: n/a
Is recordcount a little messed or what?

For Each tdf In dbs.TableDefs
If Left(tdf.Name, 3) = "tbl" Then
Debug.Print tdf.Name
Debug.Print tdf.RecordCount

it prints the correct name but the RecordCount is off on 8 out of 30
tables. the only thing is all the correct ones don't have any records
LOL

3 of the 8 show 20 records but the tables contain 21
2 or the 8 show 12 when they have 15
3 or the 8 show 17 when they have 18

what am I doing wrong?
Sep 18 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
sparks wrote:
Is recordcount a little messed or what?

For Each tdf In dbs.TableDefs
If Left(tdf.Name, 3) = "tbl" Then
Debug.Print tdf.Name
Debug.Print tdf.RecordCount

it prints the correct name but the RecordCount is off on 8 out of 30
tables. the only thing is all the correct ones don't have any records
LOL

3 of the 8 show 20 records but the tables contain 21
2 or the 8 show 12 when they have 15
3 or the 8 show 17 when they have 18

what am I doing wrong?

Try this, see if it works better
Dim tdf As TableDef
Dim dbs As Database
Set dbs = CurrentDb
Dim rst As DAO.Recordset

For Each tdf In dbs.TableDefs
If Left(tdf.Name, 3) = "tbl" Then
Set rst = dbs.OpenRecordset(tdf.name, dbOpenSnapshot)
If rst.RecordCount 0 Then rst.MoveLast
Debug.Print tdf.name; rst.RecordCount
rst.Close
End If
Next
Set rst = Nothing
Set dbs = Nothing
Sep 18 '08 #2

P: n/a
On Thu, 18 Sep 2008 11:35:34 -0700, Salad <oi*@vinegar.comwrote:
Dim tdf As TableDef
Dim dbs As Database
Set dbs = CurrentDb
Dim rst As DAO.Recordset

For Each tdf In dbs.TableDefs
If Left(tdf.Name, 3) = "tbl" Then
Set rst = dbs.OpenRecordset(tdf.name, dbOpenSnapshot)
If rst.RecordCount 0 Then rst.MoveLast
Debug.Print tdf.name; rst.RecordCount
rst.Close
End If
Next
Set rst = Nothing
Set dbs = Nothing
at first I thought it was the the rst.movelast that would make it
work.
I thought that the movelast would make sure that everything was loaded
before it printed the count.

but that did not make a difference.

It was the dbOpenSnapshot that did it.

I had tried dbOpenTable and dbOpenDynaset and both gave the same
results.

thank you very much for your help it was driving me nuts....I am just
glad I looked and didn't take it as it was....

thanks again

Sep 18 '08 #3

P: n/a
sparks wrote:
On Thu, 18 Sep 2008 11:35:34 -0700, Salad <oi*@vinegar.comwrote:

> Dim tdf As TableDef
Dim dbs As Database
Set dbs = CurrentDb
Dim rst As DAO.Recordset

For Each tdf In dbs.TableDefs
If Left(tdf.Name, 3) = "tbl" Then
Set rst = dbs.OpenRecordset(tdf.name, dbOpenSnapshot)
If rst.RecordCount 0 Then rst.MoveLast
Debug.Print tdf.name; rst.RecordCount
rst.Close
End If
Next
Set rst = Nothing
Set dbs = Nothing


at first I thought it was the the rst.movelast that would make it
work.
I thought that the movelast would make sure that everything was loaded
before it printed the count.

but that did not make a difference.

It was the dbOpenSnapshot that did it.

I had tried dbOpenTable and dbOpenDynaset and both gave the same
results.
How odd!
thank you very much for your help it was driving me nuts....I am just
glad I looked and didn't take it as it was....

thanks again
Entirely welcome.
Sep 18 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.