VBA DAO passthrough query unexpected behaviour-CodePudding

I've just copypasted solution found here in stackoverflow, did some tweaks to adopt it to my needs and... it doesn't work :| Here is the code:

Public Function getAssortmentTypes(Optional personId As Variant) As DAO.Recordset 'personId is integer
    Dim strQuery As String
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    
    If IsMissing(personId) Then
        strQuery = "SELECT assortment_type.type_id, assortment_type.type_name AS qryTest FROM assortment_type"
    Else
        strQuery = "SELECT * FROM get_non_deleted_assortment_types_by_user(" & personId & ")"
    End If
        
    Set qdf = CurrentDb.CreateQueryDef("")
    With qdf
        .SQL = strQuery
        .Connect = getDBConnectionString
        .ReturnsRecords = True
    End With
    
    Set rst = qdf.OpenRecordset
    Debug.Print rst!qryTest
    Set getAssortmentTypes = rst
End Function

In my postgresql db I do have working function and apropriate tables. I've tested sql queries with DBEaver and they works. I'm receiving just one row (should be about 30) when I call function without parameter. With parameter I expected filtered resultset but receive "Error 3131 Syntax error in from clause". Could anyone help me please? TY

CodePudding user response：

Forgo the need for DAO and QueryDefs and use ADO with command parameterization which can then be binded to a recordset:

' SET REFERENCE TO Microsoft ActiveX Data Object #.# Library
Public Function getAssortmentTypes(Optional personId As Variant) As ADODB.Recordset
    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim cmd As ADODB.Command

    Set conn As New ADODB.Connection
    conn.Open getDBConnectionString

    ' PREPARED STATEMENT WITH QMARKS ?
    If IsMissing(personId) Then
        strQuery = "SELECT assortment_type.type_id, assortment_type.type_name AS qryTest FROM assortment_type"
    Else
        strQuery = "SELECT * FROM get_non_deleted_assortment_types_by_user(?)"
    End If

    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = conn
        .CommandText = strQuery
        .CommandType = adCmdText

        '  BIND PARAMETER
        .Parameters.Append .CreateParameter("user_param", adInteger, adParamInput, , personId)

        '  EXECUTE QUERY AND BIND INTO RECORDSET
        Set rst = .Execute
    End With

    Set cmd = Nothing
    Set getAssortmentTypes = rst
End Function

CodePudding user response：

Always set the connection string before setting the SQL.

When you set the SQL, DAO doesn't have a clue this will later become a passthrough query, so it tries to parse it as Access SQL, and obviously fails, since it's not valid Access SQL.

Simply change the order:

With qdf
    .Connect = getDBConnectionString
    .ReturnsRecords = True
    .SQL = strQuery
End With

Do note that you should be

So, like linked tables - put the connection string in that PT query.

Do not put or attempt to place connection strings in the code. Your re-link routines can thus also include to re-link PT queries.

You can now use this code:

Public Function getAssortmentTypes(Optional personId As Variant) As DAO.Recordset 'personId is integer
   
   Dim rst       As DAO.Recordset
   Dim strQuery  As String
   
   If IsMissing(personId) Then
       strQuery = "SELECT assortment_type.type_id, assortment_type.type_name AS qryTest FROM assortment_type"
   Else
       strQuery = "SELECT * FROM get_non_deleted_assortment_types_by_user(" & personId & ")"
   End If
   
   With CurrentDb.QueryDefs("qryPT")
      .SQL = strQuery
      Set rst = .OpenRecordset
   End With
   
   Debug.Print rst!qryTest
   Set getAssortmentTypes = rst

End Function

So, create a PT query called (for this example) qryPT

CodePudding user response：

Try omitting the “DAO.” Prefix in your Recordset and dimension statements. Later versions of Access understand what you want.