TableDef record count (1 Viewer)

[Pat Hartman]

Try refreshing the collections before doing the final count.

NONE of us KNOWS WHY Access does that. We didn't write MSAccess.exe. We just use it. Use the feature in Access to send a report to MS to complain because it does sound like a bug. The "-1" may be an "uninitialized" value.

 

[Josef P.]

Sub g()
Debug.Print CurrentDb.TableDefs("Prilohy").RecordCount
CurrentDb.Execute "Delete * From Prilohy"
Debug.Print CurrentDb.TableDefs("Prilohy").RecordCount
Debug.Print DCount("1", "Prilohy")
End Sub

Just for interest: does count work in this case if you run DBEngine.Idle dbRefreshCache before?

 

[Petr Danes]

Maybe you should not read selectively, but all.

Find a method to requery the table contents in the current access per code. I have not found one. In bone-stock Access use this is obviously not provided for.

Sub test_1()
    Dim db As DAO.Database
    Set db = CurrentDb
 
    Debug.Print "Table: " & db.TableDefs("tblXX").RecordCount
    Debug.Print "Rs: " & db.OpenRecordset("tblXX").RecordCount
 
    db.Execute "DELETE FROM tblXX", dbFailOnError
 
    Debug.Print "Table: " & db.TableDefs("tblXX").RecordCount
    Debug.Print "Rs: " & db.OpenRecordset("tblXX").RecordCount
 
End Sub

Recalling a recordset automatically means recalling the table.

I did read it all. And I repeat, I have found a simple work-around - using DCount. What do you think I missed by your 'reading selectively'?

 

[Petr Danes]

Try refreshing the collections before doing the final count.

NONE of us KNOWS WHY Access does that. We didn't write MSAccess.exe. We just use it. Use the feature in Access to send a report to MS to complain because it does sound like a bug. The "-1" may be an "uninitialized" value.

I have not run into the -1, but I can understand it in the context of a linked table, as Josef mentioned. It makes no sense in a local table.

And some people here DO know why certain features were implemented in specific ways. Some have contacts into MS, or have located documentation that not everyone has read.

What do you mean by 'refreshing collections'? CurrentDB generates a new pointer on every call, and I have no other collections that I know of.

 

[Petr Danes]

Just for interest: does count work in this case if you run DBEngine.Idle dbRefreshCache before?

Just now tried it - it does not. Count is still 163 from RecordCount, 0 from DCount, and this is AFTER completely closing and re-opening Access. Whatever is going on is not just some locally cached value that I accessed too quickly.

 

[ebs17]

What do you think I missed by your 'reading selectively'?

What is your audible capture of the content with F5?

I have found a simple work-around

For me, this is not a workaround, but the normal logical step - which is why I only pay limited attention to academic discussions about other ways.

Strictly speaking, however, I would want to determine the number of records deleted when deleting. If I delete everything, I know that the table is empty afterwards, I don't have to check that afterwards. In other words, some problems you don't even have to have because you don't target them.

 

[Josef P.]

That makes me curious.
I would like to recreate the scenario, but I can't do it, because TableDef.RecordCount always returns 0 after deletion. I can delete a table with 10k records and TableDef.Recordcount returns 0.
What happens after TableDefs.Refresh?

 

[Pat Hartman]

Currentdb.Containers.Refresh
or

Function RefreshContainers_TSB(dbsTemp As Database, fAccessContainers As Boolean) As Boolean
  ' Comments  : Refreshes all DAO containers
  ' Parameters: dbsTemp - database variable pointing to database to refresh or "" (blank string) for the current database
  '             fAccessContainers - True to refresh Access containers, False to refresh only native Jet containers
  ' Returns   : True if successful, False otherwise
  '
  On Error GoTo Proc_Err

  dbsTemp.Containers("Databases").Documents.Refresh
  dbsTemp.Containers("Relationships").Documents.Refresh
  dbsTemp.Containers("Tables").Documents.Refresh

  If fAccessContainers Then
    dbsTemp.Containers("Forms").Documents.Refresh
    dbsTemp.Containers("Reports").Documents.Refresh
    dbsTemp.Containers("Scripts").Documents.Refresh
    dbsTemp.Containers("Modules").Documents.Refresh
  End If

  RefreshContainers_TSB = True

Proc_exit:
  Exit Function

Proc_Err:
  RefreshContainers_TSB = False
  Resume Proc_exit

End Function

I don't know if there will be a difference. Both have worked but in different situations.

 

[Petr Danes]

What is your audible capture of the content with F5?

Sorry, I have no idea what that question means. I do not use F5, there is nothing audible under discussion, I'm not reviewing any content and I'm not capturing anything. I issue a delete SQL command, and check the RecordCount afterwards. Two VBA statements, nothing more.

For me, this is not a workaround, but the normal logical step - which is why I only pay limited attention to academic discussions about other ways.

It's a work-around in the sense of having to issue a command to do something, when simply reading a value should be adequate. Trying to understand the functioning of a tool I use almost daily is not something I consider merely academic.

This gives the correct results sometimes, and wrong results other times. I want to understand why. You obviously neither know nor care. Fine - there is no requirement that you be interested in something. Lots of people go about their business caring only about results and nothing for underlying mechanisms. I am not one of those.

Strictly speaking, however, I would want to determine the number of records deleted when deleting. If I delete everything, I know that the table is empty afterwards, I don't have to check that afterwards. In other words, some problems you don't even have to have because you don't target them.

When I issue a Delete * command, I don't care how many records were deleted, only that ALL of them were. Counting them would require fetching the count before starting the delete - a completely pointless exercise, and a waste of time and resources. Checking for an empty table afterwards is much faster and more sensible than counting everything you're just going to throw away.

 

[Petr Danes]

That makes me curious.
I would like to recreate the scenario, but I can't do it, because TableDef.RecordCount always returns 0 after deletion. I can delete a table with 10k records and TableDef.Recordcount returns 0.
What happens after TableDefs.Refresh?

No change. Still reports the phantom 163 records.

 

[Petr Danes]

Currentdb.Containers.Refresh
or

Function RefreshContainers_TSB(dbsTemp As Database, fAccessContainers As Boolean) As Boolean
  ' Comments  : Refreshes all DAO containers
  ' Parameters: dbsTemp - database variable pointing to database to refresh or "" (blank string) for the current database
  '             fAccessContainers - True to refresh Access containers, False to refresh only native Jet containers
  ' Returns   : True if successful, False otherwise
  '
  On Error GoTo Proc_Err

  dbsTemp.Containers("Databases").Documents.Refresh
  dbsTemp.Containers("Relationships").Documents.Refresh
  dbsTemp.Containers("Tables").Documents.Refresh

  If fAccessContainers Then
    dbsTemp.Containers("Forms").Documents.Refresh
    dbsTemp.Containers("Reports").Documents.Refresh
    dbsTemp.Containers("Scripts").Documents.Refresh
    dbsTemp.Containers("Modules").Documents.Refresh
  End If

  RefreshContainers_TSB = True

Proc_exit:
  Exit Function

Proc_Err:
  RefreshContainers_TSB = False
  Resume Proc_exit

End Function

I don't know if there will be a difference. Both have worked but in different situations.

Tried both, still the same. Whatever is populating this RecordCount value seems immune to reason.

 

[Pat Hartman]

They probably haven't gotten any reports of this issue. Most people don't count the remaining records after their delete query or they use a dCount() for confirmation.

In addition to this, there is no way to get the last update date for non DAO objects. Neither the MSysObjects table nor the Access collections work. The Nav Pain(sic) shows the correct info but MS keeps it a secret from us. There was a much more complicated suggestion I tried when I complained about this last year but it was useless for my purpose since you couldn't use it as a loop, you had to look at each object by name. I suppose I could have used the MSysObjects to create the loop but it was way too much trouble for something that should be easy to obtain so if I want my backup to work, before I close after changes, I look for the last update dt in MSysObjects. If it doesn't show anything except tables, then I manually update my lastUpdate query to force a change in MSysObjects. PITA

 

[ebs17]

Counting them would require fetching the count before starting the delete - a completely pointless exercise, and a waste of time and resources

Nonsense. Those who want to learn should be more defensive with their assertions.

Database.RecordsAffected property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

 

[Pat Hartman]

You never told us what your actual objective was and so we are guessing. You counted the rows in the table first, so technically you know how many you deleted. However @ebs17 's suggestion to use the RecordsAffected property is actually a more correct solution if the record count is what you want to show the user. Otherwise, you should probably assume that the delete deleted all the rows. But, in a very busy system, a new row could have been added after the delete but before you ran the count.

 

[Petr Danes]

Nonsense. Those who want to learn should be more defensive with their assertions.

Database.RecordsAffected property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

Not nonsense - getting the RecordsAffected property tells you nothing if you don't know how many records you had to start with. And finding THAT out requires counting them all. If RecordsAffected says one thousand, do you know if your table is now empty (which is what I need to know)? You do not, unless you issued a count operation before doing the delete. I see zero sense in counting something when you only intend to throw it all away.

 

[Pat Hartman]

Did you read #35? The world keeps moving in a mult-user application. Just because a table has no records now doesn't mean it has no records a second later. What is the importance of the table being empty? Do you have a way of stopping activity to keep it that way until the user takes whatever action he must take only when the table is empty?

 

[ebs17]

You know what you want, and if there's a chasm, you have to go through it. Have fun with it.
I'd better welcome spring then and sow some lettuce.

 

[Petr Danes]

You never told us what your actual objective was and so we are guessing.

Sorry - my intention is to empty all the tables in the database - completely. Nothing else.

You counted the rows in the table first, so technically you know how many you deleted.

I do NOT count the records in the table first. I have no reason to do so. I don't care how many records there are, I'm emptying the dumpster. In the small test routine I posted earlier, I tested the count first just to show what was going on. In the real app, I do not do that - there is no reason to count anything. I just want it all gone. I delete everything, and expect that it will happen. And it does, apparently, but his count gets stuck for some reason.

However @ebs17 's suggestion to use the RecordsAffected property is actually a more correct solution if the record count is what you want to show the user. Otherwise, you should probably assume that the delete deleted all the rows. But, in a very busy system, a new row could have been added after the delete but before you ran the count.

It is a single-file, single-user system, so there is no question of conflicts. The reason for this 'delete all from everything' methodology is my development cycle. I have a version under development, the user has the current version with actual data. I have an outdated copy of data for test purposes. When I have made whatever changes I need, I empty all the tables in the database and send it to the user. There is code built in that does all sorts of attendant things, but the main one is that it reads in the data from the copy the user was working in, AFTER checking that all tables in the new copy are empty, and data import can begin. He can continue working while I make changes, then gets the new version, sucks in his current data and continues working in that new version. If the import fails, he lets me know and continues working in the old version while I fix the problem. All this has been in place for years and works perfectly, EXCEPT if I forget to do a C&R as the very last step before sending him the new version. In such a case, it halts on this test, thinking that there is something in this table, even though it is, in fact, empty.

And again, I do not have a problem I'm trying to solve. I switched to DCount, which seemingly gives the correct value every time, but I am trying to figure out what is happening with this incorrect RecordCount value. As it seems now, nobody knows anything about it, and it's just one of those things in Access that don't work properly.

 

[Petr Danes]

You know what you want, and if there's a chasm, you have to go through it. Have fun with it.
I'd better welcome spring then and sow some lettuce.

I have no idea what any of that means, but I'm getting the impression you're jerking me around for fun, and I'm not interested in that. Bye.

 

[Petr Danes]

Did you read #35? The world keeps moving in a mult-user application. Just because a table has no records now doesn't mean it has no records a second later. What is the importance of the table being empty? Do you have a way of stopping activity to keep it that way until the user takes whatever action he must take only when the table is empty?

See my answer in #38. I explain all that.