TableDef record count

[Petr Danes]

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").

No. All machines on which I run this act the same way. The end user is stuck in 2007, so when I develop a new version on my modern laptop with 365, I run it through an old server on which I also still have 2007, and then send it to him. The problem occurs on all of them.

The only thing I can see that might be influencing something is that the problematic table is in a relationship. But it is on the 'one' side, and the table on the 'many' side gets emptied first, so it shouldn't be running into any cascading conflicts.

The DCount only counts records. I have to put something in that first field or DCount complains. I use "1" as a placeholder, when I'm not interested in any specific field. But putting in a name of one of the actual fields gives the same result. The issues with an actual field name and nulls could result in an undercount, but that's not an issue for this situation - all fields in this table are required.

 

[Petr Danes]

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

Thank you. Yes, using "*" might be more readable. I've just gotten into the habit of using "1". I'd like to use an empty string, to indicate that I have no interest in any field, but that throws an error.

As for the original topic, I have no clue why it doesn't work, and apparently nobody else does either. There are other tables in the DB that show the correct count. I'm beginning to wonder if maybe just that table is somehow corrupted, and I could fix it by deleting and re-creating the entire table. Worth a shot, I guess. I'll try it.

 

[Petr Danes]

Well, it looks like that was it - a corrupt table definition. I removed the relationship, deleted the table, did a C&R, decompile, another C&R, created a new table with the same definition and added the relationship back in, and did a final C&R.

Loaded the tables in the standard manner, using the code where the DB examines itself on startup and requests a location of the data when it sees it is empty. It read in with no problems, then I ran the ClearDB routine, which empties all the tables prior to shipping to the user. Both DCount and TableDef().RecordCount now give the proper value of zero, so I'm going to consider this solved.

Thank you to everyone who took a run at this - it was a weird one.

 

[The_Doc_Man]

I'm beginning to wonder if maybe just that table is somehow corrupted, and I could fix it by deleting and re-creating the entire table. Worth a shot, I guess. I'll try it.

Given that you reported a case where you got a specific non-zero number for the .RecordCount after a delete, corruption cannot be ruled out. If so, then deleting all the records might not work correctly anyway. The DELETE process might stop when it hits the deletion.

As you suggest, it MIGHT be enough to just delete and manually recreate the table, followed by a Compact & Repair.

I cannot dispute your choice to consider a "corruption" solution. Good luck on the attempt.

EDIT: Looks like our posts crossed each other at the same time.
Glad to see the problem is fixed AND that in the aftermath, it was a simple enough fix.

 

[theDBguy]

Well, it looks like that was it - a corrupt table definition. I removed the relationship, deleted the table, did a C&R, decompile, another C&R, created a new table with the same definition and added the relationship back in, and did a final C&R.

Loaded the tables in the standard manner, using the code where the DB examines itself on startup and requests a location of the data when it sees it is empty. It read in with no problems, then I ran the ClearDB routine, which empties all the tables prior to shipping to the user. Both DCount and TableDef().RecordCount now give the proper value of zero, so I'm going to consider this solved.

Thank you to everyone who took a run at this - it was a weird one.

Glad to hear you got it sorted out. Cheers!

 

[isladogs]

Given that you reported a case where you got a specific non-zero number for the .RecordCount after a delete, corruption cannot be ruled out. If so, then deleting all the records might not work correctly anyway. The DELETE process might stop when it hits the deletion.

Yes a delete might stop where it hits a corrupted record but, in that case, using DCount would also have given a non-zero value.
So that wouldn't explain the discrepancy in the two values after running the delete query that were reported at the start of this thread.

The OP has a solution...but not an explanation.

 

[The_Doc_Man]

The question is whether DCount could follow the table's contents past the corruption point, because if it couldn't, then after the deletion it would return a 0 (representing as far as it got.) Then the before-and-after non-zero .RecordCount is how many records were there to start, then how many records were not deleted because they were behind the corruption point. This assumes that the record count in question is actually not representative of a normalized result. I.e. if the .RecordCount is kept as a property, not a "hidden DCount" method, then it is updated only when an action query adds or removes records. (Just like the method we DON'T recommend for inventory-on-hand counting.) I'm not saying that is actually what happens, but that is one way it COULD happen. Call it speculation. I've been known to go out on a limb before.

 

[Petr Danes]

Yes a delete might stop where it hits a corrupted record but, in that case, using DCount would also have given a non-zero value.
So that wouldn't explain the discrepancy in the two values after running the delete query that were reported at the start of this thread.

The OP has a solution...but not an explanation.

True, I don't really have an explanation - corruption is such a vague and all-encompassing word for so many problems, and similar-appearing problems can have vastly different causes. But trying to go through the machine code of exactly what was happening is more than I am willing to tackle. Obviously the two counts use different methods, since they give different results - beyond that, I haven't a clue.

Mostly I was concerned that there was something strange about the TableDef().RecordCount property that I was overlooking, but seemingly no - it does exactly what one would expect. Given what resources I have to investigate the innards of a malfunctiong Access instance (essentially none), that will have to suffice me.

I appreciate your insights on the matter.

 

[Petr Danes]

One more note to this. After replying to Colin about Access obviously using two different methods, I got to wondering about performance, so I wrote a tiny test routine to count the records in the largest table in this app, almost 200,000 records.

Here it is. I ran it three ways, each time commenting out two lines and testing only the performance of the third.

Sub testcnt()
Dim i&, j&, t As Date
t = Now
For i = 1 To 10000
    j = CurrentDb.TableDefs("Zaznamy").RecordCount
    j = DCount("1", "Zaznamy")
    j = CurrentDb.OpenRecordset("Select Count(0) From Zaznamy").Fields(0)
Next i
Debug.Print Format$(Now - t, "#.#######")
End Sub

The results of the tests, in the same order as the code, are:

,0001157
,0002546
,0002662

And the vast majority of the time involved is the CurrentDB operation. When I put the loop inside a With CurrentDB block, I had to bump the iterations from ten thousand to a million just to get a non-zero elapsed time from the .RecordCount property. The other two methods were not affected almost at all by being inside the block. So I'm guessing that this property is stored as a value, updated by Access's internal equivalent of a trigger, while the other two methods DO count the entire table each time they are called.

 

[isladogs]

The question is whether DCount could follow the table's contents past the corruption point, because if it couldn't, then after the deletion it would return a 0 (representing as far as it got.) Then the before-and-after non-zero .RecordCount is how many records were there to start, then how many records were not deleted because they were behind the corruption point. This assumes that the record count in question is actually not representative of a normalized result. I.e. if the .RecordCount is kept as a property, not a "hidden DCount" method, then it is updated only when an action query adds or removes records. (Just like the method we DON'T recommend for inventory-on-hand counting.) I'm not saying that is actually what happens, but that is one way it COULD happen. Call it speculation. I've been known to go out on a limb before.

I don't like speculating as I tend to get burnt!

I've tested on a table with a badly corrupted record

I then ran the following routine to measure the record count using two methods before & after deletion

As you can see, the corrupted record was deleted and, in this case, the two methods did give exactly the same result each time.

It would obviously be worth rechecking in a case where the corrupted record isn't deleted

I've attached the test database with the corrupted record

@Petr Danes
You may be interested in a couple of my speed test articles
This article has recently been updated & extended

Speed Comparison Tests 7

This article discusses 7 different methods for checking if a record exists. It is the seventh in a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on your own workstations

www.isladogs.co.uk

This one is about to be rewritten with additional tests & different conclusions

Speed Comparison Tests 2

This article compares the speed of four methods used to reference the current database - CurrentDb vs DBEngine(0)(0) vs a custom property ThisDb and a custom function CurDb. It is the second in a series of articles discussing various tests done to compare the efficiency of different approaches...

www.isladogs.co.uk

Setting CurrentDb does indeed take up a measurable time. In my latest tests, I found the following:

These are average times in milliseconds to set a database variable & to set & clear the variable.
Clearing takes a insignificant amount of time
Setting using CurrentDb is comparatively slow at about 0.325 milliseconds though the time is still very small.

 

[Josef P.]

Complementary to Colin's tests:

For the time measurements, it should be noted that the creation of CurrentDb is also measured.

With CurrentDb inside loop:

Private Sub TestCountCurrentDb()

    Const Maxloops As Long = 1000
    Dim i As Long
    Dim cnt As Long

    Dim T As QpcStoppuhr
    Set T = New QpcStoppuhr

    T.StartT
    For i = 1 To Maxloops
        cnt = CurrentDb.TableDefs("TableA").RecordCount
    Next
    T.StopT
    Debug.Print "TableDef.RecordCount", cnt, T.Time


    T.StartT
    For i = 1 To Maxloops
        cnt = DCount("1", "TableA")
    Next
    T.StopT
    Debug.Print "DCount       .", cnt, T.Time

    T.StartT
    For i = 1 To Maxloops
        With CurrentDb.OpenRecordset("TableA", dbOpenTable, , dbReadOnly)
            '.MoveLast ' not required by dbOpenTable!
            cnt = .RecordCount
            .Close
        End With
    Next
    T.StopT
    Debug.Print "RS.RecordCount tbl", cnt, T.Time

    T.StartT
    For i = 1 To Maxloops
        With CurrentDb.OpenRecordset("TableA", dbOpenDynaset, dbReadOnly)
            .MoveLast
            cnt = .RecordCount
            .Close
        End With
    Next
    T.StopT
    Debug.Print "RS.RecordCount dyn", cnt, T.Time

    T.StartT
    For i = 1 To Maxloops
        With CurrentDb.OpenRecordset("select count(*) from TableA", dbOpenDynaset)
            cnt = .Fields(0)
            .Close
        End With
    Next
    T.StopT
    Debug.Print "RS + Sql Count dyn", cnt, T.Time

    T.StartT
    For i = 1 To Maxloops
        With CurrentDb.OpenRecordset("select count(*) from TableA", dbOpenForwardOnly)
            cnt = .Fields(0)
            .Close
        End With
    Next
    T.StopT
    Debug.Print "RS + Sql Count fwd", cnt, T.Time

End Sub

Result:

TableDef.RecordCount         65536         383,1742
DCount       .               65536         1072,1626
RS.RecordCount tbl           65536         285,2852
RS.RecordCount dyn           65536         2565,4264
RS + Sql Count dyn           65536         1383,167
RS + Sql Count fwd           65536         1169,7228

=> Winner: CurrentDb.OpenRecordset("TableA", dbOpenTable).RecordCount

But now with a reference of CurrentDb (created outside loops):

Private Sub TestCountDbRef()

    Const Maxloops As Long = 1000
    Dim i As Long
    Dim cnt As Long
    Dim db As DAO.Database

    Set db = CurrentDb  '<--- create instance outside measurement

    Dim T As QpcStoppuhr
    Set T = New QpcStoppuhr

    T.StartT
    For i = 1 To Maxloops
        cnt = db.TableDefs("TableA").RecordCount
    Next
    T.StopT
    Debug.Print "TableDef.RecordCount", cnt, T.Time


    T.StartT
    For i = 1 To Maxloops
        cnt = DCount("1", "TableA")
    Next
    T.StopT
    Debug.Print "DCount       .", cnt, T.Time

    T.StartT
    For i = 1 To Maxloops
        With db.OpenRecordset("TableA", dbOpenTable, dbReadOnly)
            '.MoveLast ' not required by dbOpenTable!
            cnt = .RecordCount
            .Close
        End With
    Next
    T.StopT
    Debug.Print "RS.RecordCount tbl", cnt, T.Time

    T.StartT
    For i = 1 To Maxloops
        With db.OpenRecordset("TableA", dbOpenDynaset, dbReadOnly)
            .MoveLast
            cnt = .RecordCount
            .Close
        End With
    Next
    T.StopT
    Debug.Print "RS.RecordCount dyn", cnt, T.Time

    T.StartT
    For i = 1 To Maxloops
        With db.OpenRecordset("select count(*) from TableA", dbOpenDynaset)
            cnt = .Fields(0)
            .Close
        End With
    Next
    T.StopT
    Debug.Print "RS + Sql Count dyn", cnt, T.Time

    T.StartT
    For i = 1 To Maxloops
        With db.OpenRecordset("select count(*) from TableA", dbOpenForwardOnly)
            cnt = .Fields(0)
            .Close
        End With
    Next
    T.StopT
    Debug.Print "RS + Sql Count fwd", cnt, T.Time

End Sub

Result:

TableDef.RecordCount         65536         2,0105
DCount       .               65536         1256,3409
RS.RecordCount tbl           65536         107,7835
RS.RecordCount dyn           65536         2167,2701
RS + Sql Count dyn           65536         944,8136
RS + Sql Count fwd           65536         938,5551

Winner: TableDef.RecordCount
.. I think this is a cached value. (2 ms for 1000 loops)

 

[Petr Danes]

Crap - spoke too soon. A few more hours of work and the problem is back. In the same table, with the same incorrect ghost count, and a new table added to the mix, also with a non-zero count on an empty table. Whatever is going on, it's not reliable enough for me to use. I'm giving up for now - I've switched everything to DCount.

 

[gemma-the-husky]

I have only skimmed this thread

The only thing I can think is that the "delete * from table" fails because of some dependencies that affect the delete. It would be better to error trap the delete, and count the records after to make sure they were all deleted. I know this was mentioned.

 

[Petr Danes]

I have only skimmed this thread

The only thing I can think is that the "delete * from table" fails because of some dependencies that affect the delete. It would be better to error trap the delete, and count the records after to make sure they were all deleted. I know this was mentioned.

I don't blame you for only skimming this thread - it got somewhat bogged down by irrelevant BS at one point. However, you missed what was actually happening. The Delete command does NOT fail. It works fine. What fails is the subsequent value returned by the syntax I used. The table is completely empty - no question about that, but the syntax in question continues to (erroneously) report a non-zero value for the record count, and nobody has any idea why. I finally gave up trying to make it work and went with a much slower and clunkier DCount call.

 

[CJ_London]

Also just skimmed the thread. Given the delete action takes ‘some’ time and the record count returns a reduced value but not zero perhaps it is being read ‘to soon’ so perhaps including a doevents before getting the record count would solve the problem

just a thought

 

[The_Doc_Man]

Also just skimmed the thread. Given the delete action takes ‘some’ time and the record count returns a reduced value but not zero perhaps it is being read ‘to soon’ so perhaps including a doevents before getting the record count would solve the problem

just a thought

CJ, the problem with that idea is that in a monolithic database that is local to the machine in question, Access is totally synchronous. When you do some equivalent to CurrentDB.Execute "DELETE * FROM Prilohy;", dbFailOnError there are no events pending with Access itself for a subsequent DoEvents to catch.

It is a complex problem. Since it happens for O365, though, it might be something that MS would look into, since they still have some interest in that product. The fact that it happens on Ac2007, not so much interest for them.

The ONLY explanation I can offer remains that something corrupts the file in some odd way such that the DELETE fails mid-way through the process and at that point, the TableDef.RecordCount property has been improperly maintained. A Compact & Repair after-the-fact would, sadly, remove all evidence of the incomplete deletion and thus offer no evidence. It would also reset the erroneous count. IF someone had a data recovery tool for Access that actually COULD look into the content, it might help - but most of the tools I've seen would not make a difference for this case.

 

[Galaxiom]

A few more hours of work and the problem is back. In the same table, with the same incorrect ghost count, and a new table added to the mix, also with a non-zero count on an empty table.

A couple of random thoughts.

Deleted records in a table are not actually deleted but all records on a page are overwritten with the first record.
Perhaps the count error doesn't manifest until the number of records exceeds one page?

What indexes are on the table? A DCount will use an index if it exists. Maybe RecordCount property would too. Would be interesting to see if that makes a difference to the count.

BTW The first parameter of Domain Functions is not a field name (as many developers assume). It is an expression. A DLookup will return the results of the expression which will include values from the fields included in the expression if present.

Anything that evaluates to an alphanumeric string will be used as a fieldname. Those that evaluate to numbers or Booleans are quietly accepted in a DCount to mean all. So if you want to use a digit instead of "*" you might as well omit the double quotes too.

 

[ebs17]

CurrentDb.Execute "DELETE FROM Prilohy", dbFailOnError

I don't know how others hold it, but if I delete EVERYTHING in the table, I don't count afterwards if the table is empty or not. Because I know the table is empty.
About the speed tests: Doing nothing is even faster than doing something fast.
If an error occurs, this is indicated with the attribute dbFailOnError, and it is thereby also executed a rollback, so that if there is an error, the initial state of the table. Then all records would still be available, the table is in the defined initial state.

What can cause errors when deleting: The table is still being accessed by another process, or the set referential integrity prohibits me from doing so at this moment, or there is a check constraint. Since I know my table as a developer, I certainly can't be surprised by this. I will have to take appropriate precautions.

 

[The_Doc_Man]

Because I know the table is empty.

Petr Danes offered evidence that Access gives mixed signals regarding the success or completeness of that deletion. That inconsistency was the focus of his original question. He discovered an anomaly regarding completeness. Petr was exploring the anomaly. Yes, he KNOWS that the table is empty, but for some reason, the TableDef's properties were inconsistent after the deletion. Lighten up! Have YOU never explored a curiosity before?

You commented on my reply to CJ in which I used an example statement for discussion purposes. CJ wondered whether it would be possible that some actions were deferred and I pointed out that in a monolithic database (not split; no separate back-end), all Access actions are synchronous, so nothing would be deferred.

In your list of what causes deletion errors, you left out an important one: Table corruption. And the reason we don't know what is actually going on in Petr's specific case is because Access is not open-sourced.

 

[ebs17]

In your list of what causes deletion errors, you left out an important one: Table corruption.

Good hint.
Above it was described that the deletion is done because the table is to be passed on as part of an update, as a workaround for the usual division of an application into frontend and backend.
Suspecting a corrupt table should ring some bells.

Of course, investigating some special cases is sometimes quite interesting and instructive.
However, in the context of the actual task, this topic is quite academic: good to know, but brings no countable return.