Enforce empty table

  • Thread starter Thread starter Hajo
  • Start date Start date
H

Hajo

Hi,

I want to avoid that any content is shown in a Listbox, except the headers
of the assigned query. I assigned a query with a WHERE statement 0=1. Well,
that works, but it is not really nice. Perhaps I should not set such a
strange query but change a property of the ListBox?!

Any ideas?
Thanks, Hajo
 
That is the only way I know that you can do this.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Hi Hajo,

I usually do something like "Where PrimaryKeyField Is Null" since you
cannot have null in a primary key field. But it is essentially the same idea.

Clifford Bass
 
The advantage of using WHERE 1=0 is that it's obvious that you intend to
return no records. This WHERE clause confuses people, even the most
experienced SQL Server DBAs I work with, but even with them, it is clear
that someone WANTED to return no rows. (DBAs don't work with UI and
typically can't understand applications like this.)

If you used some other WHERE Clause, like WHERE PrimaryKey IS NULL, it will
look like a mistake to someone reviewing the code after you've won the
lottery and moved on.

Just my 2 cents.
 
Hi Danny,

In that case, when I win the lottery (not going to happen since I don't
participate), I will remember to change the "where primarykey is null"s to
"where 0 = 1" :-).

Actually, I just have an inspiration. There is a better way, which
documents the deliberate returning of no rows within the query. Use:

where "This query deliberately" = "returns no rows"

Clifford Bass
 
Clifford:

I agree that the lottery is "a tax on people who are bad at math" but I
don't like the alternate scenario, "when you are hit by a bus" and someone
has to support your code. :-)

I do like the self-documenting flavor of your solution. Much better than my
1=0 paradigm.
 
Hi Danny,

One other thing that occurs to me. It may make sense to use both of my
methods together in case the query engine is not smart enough to realize that
the condition is always false. If it is not smart enough, you could end up
with a table scan. This would be true of the 0 = 1 condition also. So if
you do this:

where primarykey is null and "This query deliberately" = "returns no rows"

it should be able to use the primary index to determine that it does not
need to return anything. A quick test in Access with a table containing
about 850,000 rows shows that Access's query engine can figure out that "some
text" = "other text" is always false. The response is pretty instantaneous.
A quick test against a non-Access backend table with about 703,000 rows shows
that that back end is smart enough to figure it out also and returns after a
slight delay--perhaps one second. But adding the primarykey is null
condition results in an instantaneous response.

Clifford Bass
 
Back
Top