How to build a passthrough query programatically

[SmokeEater]

I am trying to build this passthrough query with a Onclick event

SELECT  format(Workorders.[Internal Work Order Number],"000000") as Workorder, format([Workorders.DateOpened],"dd/mmm/yyyy") as [Date Opened], format([Workorders.DateClosed],"dd/mmm/yyyy") as [Date Closed]
from Workorders 
UNION SELECT "All"," "," "
FROM Workorders
ORDER BY Workorder DESC;

I have built this but only get a "standard" Access query

Private Sub lstCustomers_Click()
On Error GoTo Err_lstCustomers_Click

    Dim i As Integer
    Dim strSQL As String
    Dim strWhere As String
    Dim strIN As String
    Dim flgSelectAll As Boolean



    strSQL = "SELECT  format(Workorders.[Internal Work Order Number]," & Chr(34) & "000000" & Chr(34) & ") as Workorder," & _
"format([Workorders.DateOpened]," & Chr(34) & "dd/mmm/yyyy" & Chr(34) & ") as [Date Opened]," & _
"format([Workorders.DateClosed]," & Chr(34) & "dd/mmm/yyyy" & Chr(34) & ") as [Date Closed] " & _
"from Workorders " 'INNER JOIN Customers ON Workorders.CustomerID = Customers.CustomerID "

flgSelectAll = False
    For i = 0 To lstCustomers.ListCount - 1
        If lstCustomers.Selected(i) Then
            If lstCustomers.Column(1, i) = "All" Then
                flgSelectAll = True
                Exit For
            End If
            strIN = strIN & " " & Chr(34) & lstCustomers.Column(0, i) & Chr(34) & " ,"
        End If
    Next i

    'Create the WHERE string, and strip off the last comma of the IN string
    If flgSelectAll = True Then
        strWhere = " UNION SELECT " & Chr(34) & "All" & Chr(34) & "," & Chr(34) & " " & Chr(34) & "," & Chr(34) & " " & Chr(34) & _
               " From Workorders " & _
                " ORDER BY Workorder DESC"
    Else
        strWhere = " WHERE (Workorders.CustomerID  in " & _
               "(" & Left(strIN, Len(strIN) - 1) & "))" & _
               " UNION SELECT " & Chr(34) & "All" & Chr(34) & "," & Chr(34) & " " & Chr(34) & "," & Chr(34) & " " & Chr(34) & _
               " From Workorders " & _
                " ORDER BY Workorder DESC"
               

    End If

    Debug.Print strSQL & strWhere
        
        
    If flgSelectAll = False Then
    
    lstWorkorder.RowSource = strSQL & strWhere
    End If
        
    
    lstWorkorder.Requery
        
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    Set MyDB = CurrentDb()
    
    For Each obj In dbs.AllQueries
        If obj.Name = "qryPassThroughTest" Then
            MyDB.QueryDefs.Delete "qryPassThroughTest"
        End If
    Next obj
    
    Set qdef = MyDB.CreateQueryDef("qryPassThroughTest")
    qdef.SQL = strSQL

Exit_lstCustomers_Click:
    Exit Sub

Err_lstCustomers_Click:
    MsgBox Err.Description
    Resume Exit_lstCustomers_Click
End Sub

Any suggestions on how to get the passthrough query I am looking for?

 

[Bonediggler1]

First create a pass through query shell in the regular query window, then refer to that query as your querydef rather than creating a new one in the code.

 

[JerryKlmns]

Play with the Connect property with the ReturnsRecords property to create an ODBC SQL pass-through query....