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.)
Code:
' 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):
Code:
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!