TableDef record count (1 Viewer)

[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?

 

[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.

 

[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

 

[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.

 

[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.

 

[Petr Danes]

Therein lies the problem. split the FE and BE and you don't need the convoluted data transfer. When you change the FE, you just replace the master on the server and the next time he opens the app, the new version gets downloaded. There are various ways to do this from a simple batch file (which is what I use) to complex apps that do more than just replace the FE. You will find them here.

When you have to change the BE, that is a different process. If the user is local, it is pretty easy. You can ask him to not use the app for a few minutes/hours while you change his BE (after backing it up of course) If he is remote, you create a "conversion app". This app adds columns and Adds tables and maybe corrects existing data such as running a query to convert SLS to null. If you have a structural change to make, the conversion is more complex but it should still be automated using DAO code or DDL queries. When I create apps for users whose systems I don't have easy access to, I use the "conversion app" method. One thing though - I never delete tables in the BE or from columns. I leave the old data there.

Yeah, I figured someone would bring this up. Splitting is one way of doing things, but not the only way, and it is certainly not 'the problem' here. I have intentionally not split this one, because it needs to remain portable, and the single-file design is far better for that.

And I am NOT deleting tables or columns. I am reading data from one DB into another. That all works perfectly and I have no intention of changing it. Splitting this DB would introduce a new level of complexity for both the user and me, and have zero benefit for anything.

All I want is to figure out why this RecordCount property returns the wrong value. I do not want to get side-tracked into discussions about other things, like the merits of splitting a database.

 

[Petr Danes]

You can do whatever floats your boat. I would never create a monolithic app for a user. Period.

Jesus - nobody is asking you to. But being pedantic about anything is usually a mistake. There are situations where a monolith is a better solution. If you insist on avoiding it for no reason except that you think splitting is 'always the right way', you add extra work for yourself and extra complexity for the user.

And all this has NOTHING to do with what I asked about. If you're going to engage with me, can you please at least stick to the topic?

 

[The_Doc_Man]

(Quote from post #7) I have a development machine with 365, and one of my users has an older one with 2007. Both do it.

Do you have any machines that DON'T exhibit the behavior? When theDBguy says it works OK for him, that eliminates some possible cases. For instance, working on one machine but not two others sounds like a configuration issue, which is in turn usually caused by either having an old library or having an issue among the library references. The installed libraries stay with the machines even if you move this monolithic app of yours. What happens is if you take the app from place to place, it upgrades its libraries where needed. BUT if you move the app back to a lower version, the libraries do not automatically downgrade.

You said "one of my users has an older one..." which is of uncertain semantics. Do you mean that another of your users has some version on which this problem does NOT occur?

Earlier (post #18) you showed us a DCount( "1", "Prilohy" ) and that made me pause for a moment. Do you actually have a field named "1" in that table? Because that is the syntax you presented. The thing that is interesting in DCount is that you could miss extant records counting that way because if there is a null involved, DCount won't count it UNLESS you were doing a DCount ("*", "Prilohy").

 

[isladogs]

I've stayed out of this thread until now but have watched, skimming posts, as it has deteriorated

As stated earlier .RecordCount gives -1 for linked tables but that is irrelevant here as the database isn't split.
Whether or not it should be split is, in my opinion, not relevant to the question originally posted.

The code written in post #18 should work perfectly. I've used similar code myself on numerous occasions.
I've just tested your exact code on local tables and it worked fine for me
In my case the Debug results were 58, 0 , 0
I don't know why your second figure would be other than zero.

Whilst you could use .RecordsAffected, I see no benefit in doing so in this situation

Whilst I always use DCount("*", "TableName"), your variant using "1" gives exactly the same output. Using "1" doesn't mean it is a real field

 

[Petr Danes]

The discussion is relevant because your problem, now that we know what the actual problem is, is caused by the use of non-standard procedures. If you wish to use non-standard procedures, that is your choice. I was merely pointing out that your choice is non-standard. If you think splitting is more work than what you are doing, is it because you've never split a database before and don't know how to relink tables? You joined the forum over 12 years ago. You've made very few posts so you don't come to help people. You come to be helped. Am I supposed to know automatically your level of expertise?

If you are not interested in further discussion, just mark the thread as closed and we'll leave it at that.

It is NOT caused by any non-standard procedures, and I am not using any non-standard procedures anyway. I am DELETING ALL RECORDS FROM A TABLE. Period. There is nothing non-standard about an SQL Delete command. What else is going on around that is completely irrelevant to the topic.

Yes, I do know how to split databases. I do so when it's appropriate, like when communicating with an external server. I do not do so when it would be a poor design choice, like this one, when the user wants the entire database to be flash-drive portable. He copies his single .accdb file onto a USB stick and done. Forcing this user to keep track of (at least) two files, with the potential to forget one while copying the other would be a very poor design choice. I will not force my users to jump through pointless hoops because of someone else's views on 'proper' procedures.

I should never have even mentioned this, because it always prompts a flood of irrelevant comments.

The topic is the RecordCount property of a TableDef object. Nothing else.