Parameterized Pass-thru query to SQL Server

M

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
 
D

Duane Hookom

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

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

Max Yaffe

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top