[SOLVED] DAO passthrough query using VBA: "Error 3131 Syntax error in from clause"

DAO passthrough query using VBA: "Error 3131 Syntax error in from clause"

I copied a solution found in Stack Overflow, adapted it to my needs.

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 a working function and appropriate tables. I've tested sql queries with DBEaver and they work.

I'm receiving just one row (should be about 30) when I call the function without a parameter.

With a parameter I expect filtered resultset but receive

"Error 3131 Syntax error in from clause".

Solution

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 using parameters, and generally, use ADO instead of DAO when working with external data sources. DAO is great with Access, but offers less features with external data sources. ADO won't try parsing the SQL string before it actually needs to, for example.