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

Question

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

You're using DAO to query a PostgreSQL database from within a VBA macro in MS Access? _wow_ — Dai, Dec 22 '21 at 21:10
`(" & personId & ")"` <-- DO NOT DO THIS - this is how you get SQL injection vulnerabilities, as well as having your program break if you pass a string with an Irish person's name in it. — Dai, Dec 22 '21 at 21:12
that sql injection issue only applies to user input - if personID is user input, then good advice - but we have no reason to suspect or even imply that personID is the result of a user input. It may well be that personID is a PK record ID from other code - not based on user input but a PK row of a record - and thus that code is fine. — Albert D. Kallal, Dec 23 '21 at 02:20
And by the way, using DAO which is ODBC is the recommend technology stack to use. OLEdb is a windows only technology - and ODBC and DAO is the path forward, since other platforms don't support ADO anymore. In fact EVEN Microsoft recommend this 10 years ago. However, while in 2018, they reversed this decision? Using the ODBC stack is the recommended choice - as that is a industry standard - and ADO is not. (not to be confused with ado.net). — Albert D. Kallal, Dec 23 '21 at 02:28
@Dai - yes, you're right. Well, MS Access is... not security focused at all, and as Albert mentioned I'm using TempVars stored var that is not provided by user input. DAO can be used in this example. Maybe in some more complicated scenario it could not work, but here it's ok. — Smok, Jan 09 '22 at 17:18

score 4 · Accepted Answer · answered Dec 22 '21 at 21:22

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.

Parfait · Answer 2 · 2021-12-23T01:45:55.347

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

score 1 · Answer 3 · answered Dec 23 '21 at 02:09

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:

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

This is a very "MS Access" specific approach ;) I dont like creating unnecesary objects in my program and I'm stubborn so I decided to go the "code only" solution. Please see Eric's answer below, it solved for my problem. — Smok, Jan 09 '22 at 17:24
I did not create the object in code - so to not create objects in code would suggest the solution I posted then right? As you can see, I don't even have to touch or write or setup a connection in code. Eric's fine solution creates both a querydef object in code (which you said you don't like), and also you have to setup a connection in code - again more things created in code. Strange that you suggest you don't want to create objects in code, and then turn around and adopt a solution in which you create both the query object, and setup a connection in code, when my solution has neither? — Albert D. Kallal, Jan 09 '22 at 18:22

score -1 · Answer 4 · answered Dec 23 '21 at 01:26

-1

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

answered Dec 23 '21 at 01:26

Dave Smith

9
4

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

4 Answers4