Pass-through query Error

[wubb]

I'm using Access VBA to run Pass-through query to INSERT into an SQL Server table and received this error: (error no: 3325)
"Pass-through query with ReturnsRecords property set to True did not return any records."

The Access app had been working for sometime now so I don't know why on this particular INSERT it gave me this error.

Below is the fragment of the code that ran the Pass-through query.

============================================================
InsertNewRecord:

strSQL = "INSERT INTO InspectionReport " _
& "(PortCall_ID, IR_Type, XLSFileName, " _
& Left$(gstrInspectionReportField, Len(gstrInspectionReportField) - 2) _
& ", Created_By) " _
& "VALUES (" & glngPortCallID & ", " & "'" & "L" & "'" & ", " _
& "'" & gstrXLFileName & "'" & ", " _
& Left$(gstrInspectionReportValue, Len(gstrInspectionReportValue) - 2) _
& ", " & "'" & gstrCreatedBy & "'" & ")"

Set qdf = CurrentDb.CreateQueryDef("")

qdf.Connect = gstrServerConnect
qdf.SQL = "SET QUOTED_IDENTIFIER OFF " & strSQL
qdf.ReturnsRecords = True

Set rs = qdf.OpenRecordset()
============================================================

It jumped to the error routine right at the last line.

I checked the table after it ran and gave me the error and it seems the record had been inserted, so now I don't know what the problem is.

Does anyone know any possible cause of this? Any help would be greatly appreciated.

Thanks,
Wubb

 

[fredericofonseca]

Well a INSERT record statement never returns any record, so qdf.ReturnsRecords should be set to FALSE as far as I know.

For same reason you should not be using a recordset object to execute that SQL.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[wubb]

Thanks for the tip Frederico.

That's what I thought about the INSERT too.
I don't think it should return any record and as far as I know there isn't any trigger that handles INSERT on this table.
However, this has been done before me and it had been working fine for a while now, and that's also one of the thing that puzzles me, as to why it worked when it doesn't seem to be returning any records. So I'm reluctant to change it since it might work for this particular INSERT record but might have errors in the future.

"For same reason you should not be using a recordset object to execute that SQL."
Could you please elaborate on this?

Thanks,
Wubb