Pass Parameter to SQL Procedure

  • Hi,
    I have a Access application that uses SQL 2012 as the backed database. I'm was using pass through query so far but now I'm in a situation to pass few parameters to SQL and do the processing. I couldn't figure out a way to pass parameters in pass-through query to a SQL View or procedure. Also I have to write the output from SQL proc to an excel, may be using docmd.output 

    Is there a way to accomplish this?

    Kind Regards

  • So are you trying to execute a stored procedure from Access?  That would typically be the case where you pass parameters to SQL Server.  We typically define an ADODB connection, and define the ADODB.Command and then in VBA we define the command text as something like "EXEC StoredProcedureName Parameter1 Parameter2 ..."  In that appraoch you get an ADO recordset returned which you must then process.  If you aren't comfortable with ADO, then the pass-through query is an easier method as long as you aren't trying to return large recordsets.  Hope this helps a bit.

    Colorful Colorado
    You can't see the view if you don't climb the mountain!

  • If you are using a saved query for your pass-through, you could do something like this:
    CurrentDb.QueryDefs("my_query").SQL = "EXEC StoredProcedureName Parameter1 Parameter2 ..."

    Since you also want to export the results, the saved query is probably the easier approach, like this:
    DoCmd.OutputTo acOutputQuery, "my_query", acFormatXLSX
    ..which will prompt for the location to save the result.

  • This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply