I have an SQL query which takes up a parameter stored in the db. I want to invoke the same from VBA code which is running on the same db. I am using MS Access for my work.
So for example consider, I have an SQL query 'Q' which takes a parameter 'p', I intend to invoke this SQL query from my VBA code 'C' , which also naturally involves passing this parameter 'p' to the query.
Help much appreciated Soham
There are a few possibilities here.
Let us say it is a SELECT query built on a form that holds the parameters to be used and that the input is safe:
s = "SELECT * FROM MyTable WHERE AText ='" & Me.MyText & "'"
This can be used like so:
Forms!SomeForm.RecordSource = s
Or
Set qdf = CurrentDb.CreateQueryDef("NewQuery", s)
However, the above can be done in other, better ways.
Let us say it is a ACTION query run from a form that holds the parameters to be used and that the input is safe:
s = "UPDATE MyTable Set AText ='" & Me.MyText & "'"
Then
Set db = CurrentDB
db.Execute s, dbFailOnError
Or you can use a temporary query, which can be safer:
'Temporary query
s = "UPDATE MyTable Set AText = MyRext"
Set qdf = db.CreateQueryDef("", s)
qdf.Parameters!MyText = Me.MyText
qdf.ReturnsRecords = False
qdf.Execute dbFailOnError
Something similar to the above would also be suitable for an existing query.
You can also pass the parameter to a procedure, in which case Me.MyText becomes a variable, or you can use Inputbox, which is rarely a good idea.
After that, there is the whole world of ADO.
