CODEWITHSUNDEEP
vbams-accessdaopass-through
Smok
Smok

Reputation: 117

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".

Upvotes: 0

Views: 383

Answers (4)

Albert D. Kallal
Albert D. Kallal

Reputation: 49039

I don't recommend the introduction of ADO.

The issue looks to be that your first SQL query would (and does) work as a linked table, and thus works because it not a pass-through query.

The 2nd sql fails, since it still try to use "access" sql, and not postgresSQL syntax.

I recommend that you create a PT query (using Access UI). In the designer, make sure you select pass-though:

enter image description here

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

Upvotes: 1

Parfait
Parfait

Reputation: 107587

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

Upvotes: 4

Dave Smith
Dave Smith

Reputation: 9

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

Upvotes: -1

Erik A
Erik A

Reputation: 32642

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.

Upvotes: 4

Related Questions