Pass-Through Query Syntax HELP! 2

[HiBoo]

I'm trying to create a pass-through query in Access2000 that will allow me to EXEC a stored procedure in SQL7.0 that accepts a parameter from a txt field on one of my forms.

In the pass-through query, I know that the syntax must be like this...

EXEC sp_GetData 'Variable'

The problem is I'd like to do something like this...

EXEC sp_GetData forms!frmSearch!txtCriteria.value

The variable is a VARCHAR(100) but I don't know how to reference it in the query!

Can this be done? If so, how???

 

[Kubilus1]

Your trying to run a stored procedure from a parameter on a form. You must use some VB to drop and re-create a pass-through query based on the parameter at the time. There are simpler ways to do this for simple action queries, but the following way allows for the return of records:

''If query exists, drop it

If ObjectExists("Queries", QueryNameP) = True Then
MyDatabase.QueryDefs.Delete QueryNameP
MyDatabase.QueryDefs.Refresh
End If

''Connect to sql server

Set MyQueryDef = MyDatabase.CreateQueryDef(QueryNameP)

SourceConnectStr = "ODBC;DRIVER={SQL Server};" & _
"SERVER=NTSERVER;" & _
"DATABASE=TrainingSQL;"

MyQueryDef.connect = SourceConnectStr

''Set permissions so users can re-drop/create query

Set Con = MyDatabase.Containers("Tables&quot
Set Doc = Con.Documents(QueryNameP)

Doc.UserName = "Users"
Doc.Permissions = dbSecFullAccess

''Set Sql code for query

MyQueryDef.SQL = "EXEC sp_GetData " & _
forms!frmSearch!txtCriteria.value

MyQueryDef.ReturnsRecords = True

MyQueryDef.Close
MyDatabase.Close

You will have to run this code every time the parameter changes. This works great for Access 97, should be fine with 2000. Hope this is some help.

Matt