I am trying to "exec" a macro from MS Access as a pass-through query. However, if the macro has insert/update/delete then a select, no data will be returned with an error of "Pass-through query with ReturnsRecords property set to True did not return any records. (Error 3325)". Only when the macro is the select will data be returned. It appears as though the macro is "being expanded" by MS Access as multiple lines - which it won't process, only a single line. BTW, it will work with other DBMS stprocs. Could this be a driver problem? or the correct behavior?
A Teradata macro is a "multi-statement request" that can return multiple result sets. That requires special handling / workarounds within MS Access or any other client application. In particular, INSERT/UPDATE/DELETE will return "empty" result sets to allow passing back status information.
The same issue would surface with a SP in some other database that attempted to return multiple result sets - but you typically would handle INSERT/UPDATE/DELETE status within the SP and not return a result set to the caller for those.
Could you expand as to how to work around? Again, we can execute a stproc (MS Access or Sybase) that has multiple statements, but only 1 select (at the end of the stproc) and this works OK as a pass-through query in MS Access.
It depends on what you are doing in Access. If you are using VBA with ADO, you can use the nextResultSet method. You can also build a "MakeTable Query" on top of the pass-through query. Search MDSN, Google, etc. for more information on Access and "multiple result sets"