TableDef record count

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.
 
Petr Danes said:
Code: 
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
Click to expand...
Just for interest: does count work in this case if you run DBEngine.Idle dbRefreshCache before?
 
ebs17 said:
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.

Code: 
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.
Click to expand...
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'?
 
Pat Hartman said:
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.
Click to expand...
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.
 
Last edited:
Josef P. said:
Just for interest: does count work in this case if you run DBEngine.Idle dbRefreshCache before?
Click to expand...
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.
 
What do you think I missed by your 'reading selectively'?
Click to expand...
What is your audible capture of the content with F5?

I have found a simple work-around
Click to expand...
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.
 
Last edited:
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?
 
Currentdb.Containers.Refresh
or
Code: 
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.
 
ebs17 said:
What is your audible capture of the content with F5?
Click to expand...
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.

ebs17 said:
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.
Click to expand...
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.

ebs17 said:
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.
Click to expand...
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.
 
Josef P. said:
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?
Click to expand...
No change. Still reports the phantom 163 records.
 
Pat Hartman said:
Currentdb.Containers.Refresh
or
Code: 
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.
Click to expand...
Tried both, still the same. Whatever is populating this RecordCount value seems immune to reason.
 
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
 
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.
 
ebs17 said:
Nonsense. Those who want to learn should be more defensive with their assertions.
learn.microsoft.com

Database.RecordsAffected property (DAO)

Office developer client VBA reference documentation
learn.microsoft.com
Click to expand...
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.
 
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?
 
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.
 
Pat Hartman said:
You never told us what your actual objective was and so we are guessing.
Click to expand...
Sorry - my intention is to empty all the tables in the database - completely. Nothing else.

Pat Hartman said:
You counted the rows in the table first, so technically you know how many you deleted.
Click to expand...
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.

Pat Hartman said:
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.
Click to expand...
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.
 
ebs17 said:
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.
Click to expand...
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.
 
Pat Hartman said:
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?
Click to expand...
See my answer in #38. I explain all that.
 
You must log in or register to reply here.

Similar threads

J
Solved Count blank detail sections in report to minus from grand total
Replies
1
Views
185
Jomat
J
D
Need help with SQL in an Access query that has UNION for the non-relational queries/tables
2
Replies
26
Views
655
dlhappy
D
C
Deleting record
Replies
9
Views
341
ClaraBarton
C
Z
DCount with multiple criteria in subform
Replies
5
Views
109
zebrafoot
Z
P
  • Locked
DLookup returning Null instead of empty string
2 3
Replies
50
Views
1,861
Jon
Jon

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom