pass parameters from Access to SQL (1 Viewer)

[shaomwang]

I created a program which uses form to let user input parameter. I try to use SQL store procedure, and then use pass through query to call this store procedure. The key point is: how to pass the parameter which user input from form to store procedure? Help

 

[kdevitt]

Its not actually possible to pass a parameter in a pass-trhough query. The query is sent as a string direct to the SQL server, so that won't recognise any parameters you've placed in the query.

You have to code the query in VBA to simulate parameters. Basically, create a query definition in your module/function and code a SQL string like MYSQLStr = "EXEC MyStoredProc " & me.MyParameter

Then execute the SQl string you've created on the server. I'm not sure what your level of programming is, so I'm sorry for not going in to loads of details, but if you go to http://support.microsoft.com/support/KB/Articles/Q131/5/34.asp

theres a really good article on it.

hope this helped!
Ken

 

[shaomwang]

Ken,
Thank you for your help. The same question as bellow:
I use a code behind a search form to get sql sentence like below:
strSql = "SELECT *"
strSql = strSql & "FROM [CLAIM]"
strSql = strSql & (" where " & Mid(varMyWhere, 6) & ";")
How to pass entire sql sentence as parapeter to store procedure? Or only where clause to store procedure?

could give me more detail of how to?