Search code examples
sql-serverms-accesspass-through

MS Access Generic Pass-Through Query to SQL Server

I successfully set up a pass-through query in MS Access. It is generic in the sense that I can set the SQL on the fly via the DAO.QueryDef ".SQL" property.

Problem is, I cannot seem to set the ".ReturnsRecords" property on the fly. Whatever is in the query's Property Sheet seems to be stuck there.

An example might be helpful:

Dim qdef As DAO.QueryDef 
Set qdef = CurrentDb.QueryDefs(gPassThru)
qdef.ReturnsRecords = False  ' or True
qdef.SQL = "exec dbo.sp_whatever"

To reiterate,

  1. qdef.SQL replaces whatever SQL was in the pass-through query

  2. qdef.ReturnsRecords does not replace the property sheet definition of the pass-through

Is there any way of making the query completely generic, or do I need to create a separate queries based on whether records are returned?

Solution

  • This seems to do the trick -- at least in the case where I don't need any records back. Thanks to C Perkins for steering me in this direction.

    Dim strSQL As String
Dim qdef As DAO.QueryDef

Set qdef = CurrentDb.CreateQueryDef("") 'temporary pass-through
strSQL = "exec dbo.sp_whatever"

With qdef
  .Connect = gConnect
  .SQL = strSQL
  .ReturnsRecords = False
  .Execute
End With

Set qdef = Nothing