How to get a "nothing" recordset

[twgonder]

We've talked around this question in other posts,
I've gotten more informed (I think) on how and what a backend (BE) sends to the frontend (FE).
As best as I can tell, the BE essentially says to the FE here's all the data in the table, deal with it.


Without going into SQL server (I know it's meant to do this stuff)...

I have a form that uses VBA and a few variables to build the SQL for the record set. It looks like this:
(Don't try to figure it all out, some lines aren't included, just know that I'm building an SQL for the form's recordsource.)

  ' Set the default records to select
  fCntl(0, 7) = "SELECT * FROM tbl_4_Entity" 'DefBegSql
  temp1 = fDt(Date - fCntl(1, 3)) 'foreign used in past n days
  fCntl(0, 8) = "(NwResOwnID=" & TempVars!LoNwResBeID & " OR NwResOwnID=1) AND DtFgnUsed>=" & temp1 & _
    " AND Ps > 0" 'DefWhereSql
  If fCntl(0, 11) <> "" Then fCntl(0, 8) = fCntl(0, 8) & " And " & fCntl(0, 11)
  fCntl(0, 9) = "Nm1, Nm3, Nm4, DispCD" 'DefOrderSql

After this code is a procedure (used in other places of the form too) the do the following (yes, lots of code between, but you see the basic commands):

    wsql = fCntl(0, 7) & " WHERE " & temp1 & " ORDER BY " & fCntl(0, 9)
    fCntl(0, 12) = fCntl(0, 7) & " WHERE " & temp2 & " ORDER BY " & fCntl(0, 9)


  'MsgBox fcntl(0, 2) & "   " & nProc & vbCrLf & wSql
  Frm.RecordSource = wsql

And it works great for displaying a subset of records in the Entity table.
But now, I'm at a point where, with a huge Entity table, I want to NOT bring down all the records from the BE and instead have an empty record set.
There were suggestions before to use something like:

Select ID from table where ID = false

but I think that would still require bringing the entire table's data down to the FE via the network.

Is there a SQL statement that essentially would avoid SQL processing on the FE of a huge BE table?
I know one idea might be to use a dummy empty table, but maybe there's a more elegant solution?
Another way, if possible, is to send just one default record over the network, not requiring the FE to parse all the BE records to find it. I'm not sure this can be done.

Thanks!

[pbaldy]

I use that type of method on SQL Server BE tables with millions of records. Access passes the criteria to SS so no records are brought over the wire. The recordset opens instantly.

[twgonder]

@ Paul, post# 2 And without SS?

When I started with this project, I naively assumed that the Jet/Ace in a BE would be a bit more sophisticated than a simple "Table Server".

[pbaldy]

My understanding is that an Access BE would react the same, but you can test it to be sure. See how long a recordset with no criteria takes to open vs one with the "ID = 0" criteria. Or maybe somebody who works with Access BE's can give a definitive answer.

[twgonder]

... See how long a recordset with no criteria takes to open .

I'm not sure what you mean. Do you mean no WHERE clause? If so, wouldn't that just bring down the entire table?

[pbaldy]

Yes, for testing. I'm trying to compare the two. My guess is that the criteria ID = 0 will be faster.

[CJ_London]

To bring through an empty record set I just use one of the following

WHERE False
or
WHERE 1=0

no need for the sql to even reference an index which might save a microscopic amount of time

[twgonder]

@CJ_London post #7 As of yet, I don't have a huge BE file to test against. I'm watching blips on the Ethernet<Performance<Task Manager and there are little ones all the time when just moving between records in a form.

Have you seen what happens with a WHERE False? SQL ran on the FE but nothing (or very little) sent from the BE?
Anyone have a little diddy lying about that is a split DB that creates a huge BE file for testing? (Mike Wolfe talked about one in a post, but I don't think he published a .accdb to go with i.)

[CJ_London]

Have you seen what happens with a WHERE False? SQL ran on the FE but nothing (or very little) sent from the BE?

that is what I would expect to happen. If you are in testing mode, don’t forget to check what happens when you set a forms data entry property to true, set the query or recordset to snapshot, etc

re large datasets, I use a vba loop to create say 5m records using the random function to create random values.