Unanswered: passthru query to sql server with parameters
Since Access doesn't seem to support passthru queries to SQL server providing parameters. For example to passthru a sql statement to sql server to call a stored procedure requiring input parameters. What is a workaround to do this?
This doesn't work, but it is my attempt to explain what I want to do:
DoCmd.RunSQL "select * from [ODBC;DSN=Inftemp;Description=Inftemp;DATABASE=Infi nity-temp;TABLE=Shrinkage;Trusted_Connection=Yes]"
Something similar to this (which actually works) would allow me to dynamically build the statement in access while providing the parameters from a dropdown box or some other source.
How can I build a dynamic statement and get it to SQL server?
You should open an ADODB connection to the server.
Dim cnn as adodb.connection
Set cnn = new adodb.connection
cnn.connectionstring = <whatever appropriate>
Then you have at least two options:
1. using the adodb.command object
Dim cmd as adodb.command
set cmd = new adodb.command
set cmd.connection = cnn
cmd.commandtype = accmdstoredproc
cmd.commandtext = "<name of sp>"
cmd.parameters(1) = <first param>
cmd.parameters(2) = <second param>
cmd.execute '(in case of update/insert/delete) or
dim rs as adodb.recordset
set rs = cmd.execute '(in case of select)