TableDef record count (1 Viewer)

[Petr Danes]

I've crashed repeatedly on the following: when I issue an SQL command to empty a table, the command CurrentDB.TableDefs("tbl").RecordCount continues to show the count BEFORE the records were deleted. And they ARE deleted. Opening the table shows it empty, and functions like DCount give the proper count of zero. Only after a C&R does this syntax show the proper count. Does anyone know what is going on with this? Obviously something is cached somewhere, but what is the point of caching something as basic as a record count?

 

[theDBguy]

Can you post the code you're using? Perhaps it's just a matter of refreshing your variable.

 

[Petr Danes]

That was the code:

CurrentDB.TableDefs("tbl").RecordCount

It's not stored in any variable - THIS gives me an outdated count.

 

[The_Doc_Man]

Just jumping in here, that SHOULD re-evaluate the CurrentDB based on a "snapshot" of Workspace(0).Database(0) - which should be correct.

What version of Access are you using when this happens? And is that table in a split DB or a monolithic DB ... or a non-Access back-end server?

 

[theDBguy]

That was the code:

CurrentDB.TableDefs("tbl").RecordCount

It's not stored in any variable - THIS gives me an outdated count.

Well, I just gave it a try, and it worked fine for me.

As you can see, I originally had 7 records in that table. After I deleted them all, I used the same command, and it gave me the correct count, which was 0.

 

[ebs17]

This is also a piece of experimental programming. Usually and safely one counts by query-Count or by Recordset.RecordCount.
Special paths require separate considerations.

 

[Petr Danes]

Just jumping in here, that SHOULD re-evaluate the CurrentDB based on a "snapshot" of Workspace(0).Database(0) - which should be correct.

What version of Access are you using when this happens? And is that table in a split DB or a monolithic DB ... or a non-Access back-end server?

I have a development machine with 365, and one of my users has an older one with 2007. Both do it.

The specific app that's giving me fits is a monolithic system - the user needs to be able to move it around occasionally, and so the single-file design is best for him.

I haven't yet tracked what all circumstances make this happen or not, whether SQL command only, VBA recordset approach, manual table open or what, but it seems to me it should not happen under ANY circumstances.

 

[Petr Danes]

Well, I just gave it a try, and it worked fine for me.

View attachment 106637

As you can see, I originally had 7 records in that table. After I deleted them all, I used the same command, and it gave me the correct count, which was 0.

How did you delete them? Maybe that makes a difference. I need to set up some test cases with different delete methods to investigate this further, but I don't think it should matter. This syntax should always work, I would think. I cannot imagine any scenario where having this wrong count available would be an advantage.

 

[Petr Danes]

This is also a piece of experimental programming. Usually and safely one counts by query-Count or by Recordset.RecordCount.
Special paths require separate considerations.

What is special or experimental about this? It looks completely straightforward to me. Is there some mention in BOL or similar that cautions against this syntax?

 

[ebs17]

Just follow the logic and common usage:
- Tables are for storing information.
- Information from tables is read by queries. DCount, as you know, is a query (encapsulated in an access function).
- With a new call or requery, the query pulls the current information.

 

[Petr Danes]

Just follow the logic and common usage:
- Tables are for storing information.
- Information from tables is read by queries. DCount, as you know, is a query (encapsulated in an access function).
- With a new call or requery, the query pulls the current information.

Yes, I know how to do all that. But I don't see why TableDef.RecordCount should be a problem. If the table knows how many records it has, this should provide the proper reply, always. If it does not, what is the point of this even existing?

 

[Josef P.]

TableDef.RecordCount does not always work. Try this once with a linked Access table. => returns -1.

How did you delete them?

What is your code for delete and then query count?

 

[ebs17]

The table you see and where you can count is actually a recordset on the real table in the database engine reality. A recordset is a snapshot, as already mentioned by Doc.

So if you have opened a table in the datasheet view, then delete some records by query, you get, back in the table, only the correct new view via a refresh (of the recordset), e.g. by pressing F5. So the question arises, at which state you have started the counting query.

Additionally CurrentDb creates a (new) instance of the current DB. It can happen that this own instance is not aware of actions in other instances or in forms or via DoCmd.

 

[Petr Danes]

TableDef.RecordCount does not always work. Try this once with a linked Access table. => returns -1.


What is your code for delete and then query count?

Yes - I see that it doesn't always work. That was the point of my post. What I don't know is WHY.

The code for deletion is an SQL command, issued via Currentdb.Execute "Delete * From..."

The count code is what I posted above.

 

[Petr Danes]

The table you see and where you can count is actually a recordset on the real table in the database engine reality. A recordset is a snapshot, as already mentioned by Doc.

So if you have opened a table in the datasheet view, then delete some records by query, you get, back in the table, only the correct new view via a refresh (of the recordset), e.g. by pressing F5. So the question arises, at which state you have started the counting query.

Additionally CurrentDb creates a (new) instance of the current DB. It can happen that this own instance is not aware of actions in other instances or in forms or via DoCmd.

I have two commands, both using CurrentDB. One is an SQL command to do the delete, and further on in the process, another command to retrieve the TableDef's RecordCount property.

And this 'ghost' count persists, even through closing and re-opening the DB. Of what I've tried so far, only a C&R fixes it.

 

[Josef P.]

The code for deletion is an SQL command, issued via Currentdb.Execute "Delete * From..."
The count code is what I posted above.

in this order - without any other code between this 2 lines.
How many records will be deleted? 1-100 ... 1 million

Your code looks like this?

Debug.Print "before delete", Currentdb.TableDefs("YourTable").RecordCount
Currentdb.Execute "Delete * From YourTable"
Debug.Print "deleted ..."
Debug.Print Currentdb.TableDefs("YourTable").RecordCount

What happens when you try this:

with CurrentDb
     Debug.Print "before delete", .TableDefs("YourTable").RecordCount
     .Execute "Delete * From YourTable"
     Debug.Print "deleted ..."
     Debug.Print .TableDefs("YourTable").RecordCount
end with

 

[ebs17]

Of what I've tried so far

That's why I say yes: For once, don't follow your superior thoughts for special ways, but just do what is made common and practical.

 

[Petr Danes]

in this order - without any other code between this 2 lines.
How many records will be deleted? 1-100 ... 1 million

Your code looks like this?

Debug.Print "before delete", Currentdb.TableDefs("YourTable").RecordCount
Currentdb.Execute "Delete * From YourTable"
Debug.Print "deleted ..."
Debug.Print Currentdb.TableDefs("YourTable").RecordCount

What happens when you try this:

with CurrentDb
     Debug.Print "before delete", .TableDefs("YourTable").RecordCount
     .Execute "Delete * From YourTable"
     Debug.Print "deleted ..."
     Debug.Print .TableDefs("YourTable").RecordCount
end with

Yes, exactly as you write. In the real app, there is a good bit of code between the two commands, but I just now wrote a tiny sub doing the same thing and the result are identical:

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

RecordCount before delete is 324, RecordCount after delete is 161, DCount after delete is 0. Putting the commands in a With block does not change this.

 

[Petr Danes]

That's why I say yes: For once, don't follow your superior thoughts for special ways, but just do what is made common and practical.

I don't know what my 'superior thoughts' might be. This is bone-stock Access syntax, and it should do what it says.

I'm not trying to solve a problem - I already did that, by switching to DCount. I'm trying to LEARN something here. You just telling me to not do that does not advance my understanding of the situation. Do you actually know anything about this? If so, I'd like to hear it.

 

[ebs17]

I'm trying to LEARN something here

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.