SQL Server Passthrough query as basis for a DAO recordset in Access

You need to use a QueryDef object to create a Pass-Through query, then open the Recordset via the .OpenRecordset method of the QueryDef. The following code works for me:

Dim qdf As DAO.QueryDef, rst As DAO.RecordsetSet qdf = CurrentDb.CreateQueryDef("")qdf.Connect = "ODBC;Driver=SQL Server;Server=.\SQLEXPRESS;Trusted_Connection=Yes;"qdf.SQL = "SELECT GetDate() AS qryTest"qdf.ReturnsRecords = TrueSet rst = qdf.OpenRecordsetDebug.Print rst!qryTestrst.CloseSet rst = NothingSet qdf = Nothing

Gord, could you provide the mods for the code to execute a proc with passed parameters and an insert statement? I've been working with it, but haven't been able to beat it. I have the base code working, so I'm talking to the server correctly, I'm just not getting the OpenRecordset correct.

Basically, I'm doing a three-step process: log the start of executing the SP, execute the SP, log the end. These are my SQL strings:

    beginsql = " INSERT INTO [dbo_zSprocExecuteLog]" _        & " values (sysdatetime(), 'uspGradePromotionDistrict', " _        & [SeekerUserID] & ", 'B', '(" & [ReportDistrict] & ")';"    sqlstring = " EXEC uspGradePromotionDistrict" _        & " @District = '" & [ReportDistrict] & "';"    endsql = " INSERT INTO [dbo_zSprocExecuteLog]" _        & " values (sysdatetime(), 'uspGradePromotionDistrict', " _        & [SeekerUserID] & ", 'E', '(" & [ReportDistrict] & ")';"

I'd appreciate your input, this has been driving me nuts. And can you recommend a good book on Access/SQL programming? I've got gobs of experience on the SQL Server side, my Access Fu is a little weak.

You cannot do an INSERT INTO a local table with a passthrough query. Use the resultant query (that you generated with Querydef) as the source for the INSERT.