July 25, 2017 at 6:22 am
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
newbee
July 25, 2017 at 9:38 am
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.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
July 26, 2017 at 9:44 am
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.
March 2, 2021 at 8:46 am
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