Passthrough queries and listboxes

[Xipooo]

Those settings had no effect using a local table. I certainly have access to SQL Server, but to fully replicate your environment I'd need to recreate your class, db object, etc, which I don't have time to do right now.

Completely understand. People have better things to do.

I'll go ahead and post the class object here though for everyone. It's actually quite useful.

'---------------------------------------------------------------------------------------
' Module    : GTSDAO
' Author    : Steve Bishop
' Date      : 3/13/2014
' Purpose   : DAO connection that interacts with the SQL server directly
'---------------------------------------------------------------------------------------


Option Compare Database
Option Explicit


Public ODBC As String
Private p_Server As String
Private p_Database As String
Private p_Trusted As Boolean
Private p_UserID As String
Private p_Password As String
Public SQL As String
Private settings As New settings
Public db As DAO.database


Public Property Let Server(value As String)
    p_Server = value
    UpdateODBC
End Property
Public Property Let database(value As String)
    p_Database = value
    UpdateODBC
End Property
Public Property Let Trusted(value As Boolean)
    p_Trusted = value
    UpdateODBC
End Property
Public Property Let UserID(value As String)
    p_UserID = value
    UpdateODBC
End Property
Public Property Let Password(value As String)
    p_Password = value
    UpdateODBC
End Property


Public Property Get Server() As String
    Server = p_Server
End Property
Public Property Get database() As String
    database = p_Database
End Property
Public Property Get Trusted() As Boolean
    Trusted = p_Trusted
End Property
Public Property Get UserID() As String
    UserID = p_UserID
End Property
Public Property Get Password() As String
    Password = p_Password
End Property


'---------------------------------------------------------------------------------------
' Procedure : DLookup
' Author    : Steve Bishop
' Date      : 3/13/2014
' Purpose   : Lookup a value from a specific field on a table
'---------------------------------------------------------------------------------------
'
Public Function DLookup(Field As String, Table As String, Optional Criteria As String) As Variant
    Dim rs As Recordset
    Dim sSQL As String
    sSQL = "SELECT " & Field & " FROM " & Table & IIf(Criteria = "", "", " WHERE " & Criteria)
    Set rs = db.OpenRecordset(sSQL, 4, 64)
    If rs.EOF Then
        DLookup = Nothing
    Else
        rs.MoveFirst
        DLookup = rs.Fields(0).value
    End If
End Function
'---------------------------------------------------------------------------------------
' Procedure : DSum
' Author    : Steve Bishop
' Date      : 3/13/2014
' Purpose   : Perform a sum on a particular field of a table
'---------------------------------------------------------------------------------------
'
Public Function DSum(Field As String, Table As String, Optional Criteria As String) As Variant
    Dim rs As Recordset
    Dim sSQL As String
    sSQL = "SELECT sum(" & Field & ") as FieldSum FROM " & Table & IIf(Criteria = "", "", " WHERE " & Criteria)
    Set rs = db.OpenRecordset(sSQL, 4, 64)
    If rs.EOF Then
        DSum = Nothing
    Else
        rs.MoveFirst
        DSum = rs.Fields(0).value
    End If
End Function
'---------------------------------------------------------------------------------------
' Procedure : DCount
' Author    : Steve Bishop
' Date      : 3/13/2014
' Purpose   : Perform a count on a specific field in a table
'---------------------------------------------------------------------------------------
'
Public Function DCount(Field As String, Table As String, Optional Criteria As String) As Variant
    Dim rs As Recordset
    Dim sSQL As String
    sSQL = "SELECT count(" & Field & ") as FieldCount FROM " & Table & IIf(Criteria = "", "", " WHERE " & Criteria)
    Set rs = db.OpenRecordset(sSQL, 4, 64)
    If rs.EOF Then
        DCount = Nothing
    Else
        rs.MoveFirst
        DCount = rs.Fields(0).value
    End If
End Function


'---------------------------------------------------------------------------------------
' Procedure : OpenRecordset
' Author    : Steve Bishop
' Date      : 3/13/2014
' Purpose   : Opens a recordset for a query from the SQL server
'---------------------------------------------------------------------------------------
'
Public Function OpenRecordset(SQL As String, Optional dbType As RecordsetTypeEnum = dbOpenSnapshot, Optional dbOptions As RecordsetOptionEnum = dbSQLPassThrough) As Recordset
    Set OpenRecordset = db.OpenRecordset(SQL, dbType, dbOptions)
End Function


'---------------------------------------------------------------------------------------
' Procedure : Execute
' Author    : Steve Bishop
' Date      : 3/13/2014
' Purpose   : Execute a SQL command on the SQL server
'---------------------------------------------------------------------------------------
'
Public Function Execute(Optional SQL As String)
    If Nz(SQL, "") <> "" Then
        Me.SQL = SQL
    End If
    db.Execute Me.SQL, dbSQLPassThrough
End Function


'---------------------------------------------------------------------------------------
' Procedure : class_initialize
' Author    : Steve Bishop
' Date      : 3/13/2014
' Purpose   : Class constructor to set the default settings
'---------------------------------------------------------------------------------------
'
Private Sub class_initialize()
    p_Server = settings.ViewSetting("SQLServer")
    p_Database = settings.ViewSetting("SQLDatabase")
    p_Trusted = CBool(settings.ViewSetting("WinAuth"))
    p_UserID = settings.ViewSetting("SQLUserID")
    p_Password = settings.ViewSetting("SQLPassword")
    UpdateODBC
End Sub
Private Sub Class_Terminate()
    Set db = Nothing
End Sub


'---------------------------------------------------------------------------------------
' Procedure : UpdateODBC
' Author    : Steve Bishop
' Date      : 3/13/2014
' Purpose   : Sets the ODBC connection string and then connects to the database
'---------------------------------------------------------------------------------------
'
Private Sub UpdateODBC()
    If Me.Trusted Then
        ODBC = "ODBC;DRIVER=SQL Server;SERVER=" & Me.Server & ";DATABASE=" & Me.database & ";Trusted_Connection=Yes"
    Else
        ODBC = "ODBC;DRIVER=SQL Server;SERVER=" & Me.Server & ";DATABASE=" & Me.database & ";UID=" & Me.UserID & ";PWD=" & Me.Password
    End If
    Set db = OpenDatabase(Me.database, dbDriverNoPrompt, False, Me.ODBC)
End Sub

The Settings class is another object I created with just helps store and retrieve values from a hidden table which has the SQL connection information.

[Xipooo]

I think I've solved the problem, but this is pretty clearly a bug in Access. The workaround I came up with is to save the SQL as a query on DAO database that is created by the class. Then use it to create my recordset which gets returned from the OpenRecordset function. Here is my new method on my class:

Public Function OpenRecordset(SQL As String, Optional dbType As RecordsetTypeEnum = dbOpenSnapshot, Optional dbOptions As RecordsetOptionEnum = dbSQLPassThrough) As Recordset
    Dim qdef As QueryDef
    Set qdef = db.CreateQueryDef("", SQL)
    qdef.Connect = Me.ODBC
    Set OpenRecordset = qdef.OpenRecordset(dbType, dbOptions)
End Function