TableDef record count (1 Viewer)

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

 

[Petr Danes]

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

Thank you, but no - it is not a timing problem. This erroneous value persists even after closing and reopening the database. Only a C&R fixes it.

 

[Petr Danes]

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.

I haven't dug into it enough to isolate a specific set of circumstances. But I don't understand your statement here, "...all records on a page are overwritten with the first record."

I thought the space was just marked as available. Why would a 'first record' be written everywhere? That seems pointless and error-prone. And if it was, you would have multiple copies of a record, when quite possibly the table has restrictions that do not even allow duplicates, like an autonumber primary key. A command to delete all should do just that, and nothing more.

I haven't investigated the issue of indexes. Seems an interesting point, though. Also dropping and recreating indexes after the delete might make a difference. I'll look at it and post back here if I come up with anything.

And thanks for the tip about DCount. I've just gotten into the habit of using "1", but for the purposes of simply counting, when I am forced to put in the first parameter even though I have no interest in the contents of that parameter, I think rather than "1" a simple zero would be clearer, and I am going to switch to that.