Ms Access frontend, PostgreSQL backend efficient INSERT INTO ... RETURNING ... statement

Question

HI,

I'm using MS Access as a frontend to PostgreSQL database. And I have run into dead end:

How would I get return value of passthrough SQL INSERT statement?

I tried something like this:

strSQL = "INSERT INTO someTable (col1, col2) VALUES ('val1', 'val2') RETURNING col0"
'Create passtrhough query dynamically (another function)
CreateSPT ("some_temp_query", strSQL)
idvalue = DLookup("col0", "some_temp_query")

Apparenty this way INSERT statement will be called 2 times :) Does anybody know what is the correct way? I have run out of ideas

Edit: I'm using latest psqlODBC drivers

Answer 1

Seems that function below does the trick... Not sure about performance though :)

'Insert-SQL passthroug
'Pass here all the insert statements
Function ISPT(SQLString)
Dim strODBCString As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim result As Long

    strODBCString = _
      "DRIVER={PostgreSQL Unicode};DATABASE=" & settings_getDBName() & _
      ";SERVER=" & settings_getServerAddress & _
      ";PORT=" & settings_getServerPort & _
      settings_getConnStrParams

      cn.Open strODBCString

      rs.Open SQLString, cn


      'return value
      result = rs.Fields(0).Value



    'Cleanup
    If rs.state <> adStateClosed Then rs.Close
    Set rs = Nothing
    If cn.state <> adStateClosed Then cn.Close
    Set cn = Nothing

    ISPT = result

End Function

Answer 2

Assuming you have a saved pass-though query, then you can modify the sql on the fly, and return values that way. The following code runs without you even having to declare any variables.

With CurrentDb.QueryDefs("qryPassReturn")
   .SQL = "INSERT INTO someTable (col1, col2) VALUES ('val1', 'val2') RETURNING col0"
   Debug.Print .OpenRecordset()(0)
End With

I suppose you could declare a reocrdset to receive the return value, and go:

Dim rstReturn  As DAO.Recordset

With CurrentDb.QueryDefs("qryPassReturn")
   .SQL = "INSERT INTO someTable (col1, col2) VALUES ('val1', 'val2') RETURNING col0"
   Set rstReturn = .OpenRecordset
End With

Debug.Print rstReturn(0)

So, you can just pass the sql, and the values returned will appear as a standard reocrdset. So, the first example should run fine and note how I not even declared one varialbe to make this work. You as mentioned simply need a saved pass-though query.