How to mimic the SQL Server VALUES feature in ACCESS?-CodePudding

I have this SQL query in SQL Server

SELECT v.val FROM (VALUES ('MONTROSE'), ('STERLING'), ('GREELEY'), ('FRED'), ('BILL')) v (val) LEFT JOIN co_wtr t ON t.Quad = v.val

WHERE t.Quad IS NULL;

The point of the query is to find which of he VALUES are not in a table.

Need to run this type of query in ACCESS, but it does not have a VALUES feature. Any suggestions as how to do this in ACCESS.

The gotcha here is that the users don't have write privilege to the database. So the target set of values can't be put into a table.

CodePudding user response：

Not easily, but you can use a UNION ALL query. We use MSysObjects, a default table, here. It can be replaced by any table that has at least one row and has a primary key.

SELECT v.val FROM 
(
   SELECT TOP 1 'MONTROSE' AS val FROM MSysObjects UNION ALL
   SELECT TOP 1 'STERLING' FROM MSysObjects UNION ALL
   SELECT TOP 1 'GREELEY' FROM MSysObjects UNION ALL
   SELECT TOP 1 'FRED' FROM MSysObjects UNION ALL
   SELECT TOP 1 'BILL' FROM MSysObjects
) v LEFT JOIN co_wtr t ON t.Quad = v.val
WHERE t.Quad IS NULL;

Note that we cannot omit the table, Access does do select queries without a FROM (e.g. SELECT 'A' is valid), but not unions of selects without a FROM (SELECT 'A' UNION ALL SELECT 'B' is not valid)

This leads to clunky queries, however. I'd reconsider if a temp table can't be used. Note that the table can be in Access, even though we join it with a linked table on SQL server, so only write permissions on the Access file are needed (and those are always needed to perform locking).

CodePudding user response：

Comprehensive answer from @Erik A, nothing to add.
The method requires to form a string " ('MONTROSE'), ('STERLING'), ('GREELEY'), ('FRED'), ('BILL'))" in VBA and create dynamic query.
In the style inherent in MS Access, I may do the same

Dim OutList As Object
    Set OutList = CreateObject("System.Collections.ArrayList")
    With OutList
        .Add "MONTROSE"
        .Add "STERLING"
        .Add "GREELEY"
        .Add "FRED"
        .Add "BILL"
    End With
    if CheckOutOffTable(OutList) then
       ... 
    endif
...
public function CheckOutOffTable( checkList as object) as boolean
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("SELECT t.QUAD FROM co_wtr t GROUP BY t.QUAD")
    Do While Not rst.EOF
        If checkList.Contains(rst!Quad.Value) Then checkList.Remove rst!Quad.Value
        rst.MoveNext
    Loop
    CheckOutOffTable=(checkList.Count>0)
end function

I like both approaches. Which is preferable in practice?