Parameterized Pass-thru query to SQL Server

[Max Yaffe]

Dear Group,

I'm using SQL server 2000 with an Access 2003 application utiliting a
DAO/VBA based design.

I want to change my embedded, text based queries to queries in the
query list but I read somewhere that pass-through queries couldn't be
parameterized.

Usually, my query code looks something like this:

(in form MyForm
...
Dim RS As Recordset
Dim strSQL As String
strSQL = _
"SELECT * " & _
" FROM TblQuoteDetail" & _
" WHERE Quote = '" & Me.Quote & "'"
Set RS = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset,
dbSeeChanges, dbOptimistic)

...'Do something

Set RS = Nothing

Me.Quote is a field on the current form. It is a string.

So I want to create a passthru query which pulls the query template
from the query list, patches in the parameter, Me.Quote, and submits
the thing to the server.

What is the right/best/optimum/most maintainable way to code this?

Thanks,
Max

 

[Duane Hookom]

If I understand correctly, you can save any pass-through query. Then use you
code like:

Currentdb.QueryDefs("qsptMyPT").SQL = strSQL

 

[Max Yaffe]

I want to just create the query in the query design view but how do I
programatically set the parameter?

 

[Duane Hookom]

I only know of the method that I suggested. You start with a saved P-T query
and modify the SQL property of the P-T to include your parameter values.