Stored Parameter Query in Access

[Kurzo]

I read that I can used a stored query in Access with:

Dim cn As New ADO.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDB.mdb"
cn.Execute "YourQueryName"

What if my query needs a variable from a vb form to run the query?

thanks all

[gauravmarwaha]

Just append all parameters to the query by using + or & with a space in between, and try. Else use the parameters collection built in VB and ASP both

hope U got that

[Kurzo]

I'm not sure what you mean......
If it were an Access project, I would write a parameter such as: 'Forms!frmMaintenance.txtNumber' as a parameter. If I call this query from a vb form, how would I write the parameter in the Access query?
Would I just write: 'frmMaintenance.txtNumber' ?

sorry, I don't understand

[Serge]

If you mean How you can pass parameters to Access query, then you can do it in 2 ways:

1st way is to use Parameter object with Command object

Dim cn As New ADODB.Connection
Dim cm As New ADODB.Command
Dim par As New ADODB.Parameter

cn.Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
cn.Open "C:\MyDB.mdb"

'Assuiming that you have to pass a numeric type
'parameter with the value of 5
With cm
    Set .ActiveConnection = cn
    .CommandText = "YourQueryName"
    .CommandType = adCmdStoredProc
    
    Set par = .CreateParameter("ParamName", adInteger, adParamInput, , 5)
    .Parameters.Append par
    .Execute
End With

2nd way is to use Connection object and use ODBC call

Dim cn As New ADODB.Connection
Dim intYourVariable As Integer

'Parameter value
intYourVariable = 5

cn.Provider = "Provider=Microsoft.Jet.OLEDB.4.0"
cn.Open "C:\MyDB.mdb"

cn.Execute "{ call YourQueryName(" & intYourVariable & ")}"

[Kurzo]

ok, that sounds good.

Thanks for your help, guys!!!

[Kurzo]

Sorry, one more question.......

what do I write as my parameter in the Access query?
Say, if the parameter is the value of txtUserID on the frmMain form.

[Serge]

First, you have to be sure that a parameter is create within your query in Access, then you can use the name that you gave to your parameter in query.