Any way to set parameters in Pass-through queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am working on Pass-through queries with SQL,

My query need date filter so I want to set date paramenter on that query but
from Access97 (in Pass-through query)

Regards,
AA
 
In that case I create an empty PassThrough query and using code I assign the
text to this query and then run it

e.g
CurrentDb.QueryDefs("QueryName").SQL="Select * From TableName Where
FieldName =" & MyVariable

Or, to run StoredProcedure
CurrentDb.QueryDefs("QueryName").SQL="Exec SPName " & MyVariable & " , " &
Variable2
 
Thnaks for reply and advise!

I am trying please see below I think I am thru but just a small error on
execution
('error message "ODBC – call failed. (Error 3146))

IF I PUT VALUE INSTEAD OF PARAMETER THEN IT WORKS

'''''''''''''''''''''''''''''' Coding
Dim db As Database, qdf As QueryDef, sqltxt As String
Dim parm1 As String
Set db = DBEngine.Workspaces(0).Databases(0)

Set qdf = db.CreateQueryDef("")

qdf.ReturnsRecords = False

qdf.Connect = "ODBC;DSN=Sql_CADC;Description=CADC SQL
;UID=am\aayub;DATABASE=HistoryData;LANGUAGE=us_english;Trusted_Connection=Yes"

parm1 = "Me!MyTextBox" 'user changes the parameter date
parm1 = "M4MF500001"

sqltxt = "insert into skua (sku,SumOfQtyNum) select sku,sumofqtynum from sku
where sku =" & parm1

qdf.SQL = sqltxt
qdf.Execute 'error message "ODBC – call failed. (Error 3146)", "
qdf.Close

'------------------
 
Why do you assign value to Parm1 twice?
If you want to assign a value from the form remove the double quote
parm1 = Me!MyTextBox 'user changes the parameter date
parm1 = "M4MF500001"

*************************************
For a string critera you need to add single quote

sqltxt = "insert into skua (sku,SumOfQtyNum) select sku,sumofqtynum from sku
where sku ='" & parm1 & "'"
*************************************
 
Back
Top