Access ADO parameter query

[michael971]

I have a form with a text box and button and want to run an Access
query that takes an input parameter.
I have the following code for the button:
I get an error at the line where the arrow is pointed
requested operation requires an OLE DB Session object, which is not
supported by the current provider


Private Sub Command1_Click()
Dim prm0 As ADODB.Parameter
Dim cnn As New ADODB.Connection
Dim cmd1 As New ADODB.Command
Dim rst1 As ADODB.Recordset
Dim s1 As String
Text1.SetFocus
s1 = Text1.Text


cnn = Application.CurrentProject.Connection
cmd1.CommandType = adCmdText
cmd1.CommandText = "Select * from qryOrderInfoByCountry Where country
=?"

Set prm0 = cmd1.CreateParameter("prmCountry", adVarChar,
adParamInput, 15)
prm0.Value = s1

cmd1.Parameters.Append prm0
Set cmd1.ActiveConnection = cnn <------

Set rst1 = cmd1.Execute
rst1.MoveFirst
MsgBox rst1!city
End Sub

 

[Sylvain Lafontaine]

Try replacing:

Dim cnn As New ADODB.Connection
cnn = Application.CurrentProject.Connection

with:

Dim cnn As ADODB.Connection
Set cnn = Application.CurrentProject.Connection

It's also better to write:

Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command

instead of:

Dim cmd1 As New ADODB.Command

 

[Dirk Goldgar]

I have a form with a text box and button and want to run an Access
query that takes an input parameter.
I have the following code for the button:
I get an error at the line where the arrow is pointed
requested operation requires an OLE DB Session object, which is not
supported by the current provider


Private Sub Command1_Click()
Dim prm0 As ADODB.Parameter
Dim cnn As New ADODB.Connection
Dim cmd1 As New ADODB.Command
Dim rst1 As ADODB.Recordset
Dim s1 As String
Text1.SetFocus
s1 = Text1.Text


cnn = Application.CurrentProject.Connection
cmd1.CommandType = adCmdText
cmd1.CommandText = "Select * from qryOrderInfoByCountry Where country
=?"

Set prm0 = cmd1.CreateParameter("prmCountry", adVarChar,
adParamInput, 15)
prm0.Value = s1

cmd1.Parameters.Append prm0
Set cmd1.ActiveConnection = cnn <------

Set rst1 = cmd1.Execute
rst1.MoveFirst
MsgBox rst1!city
End Sub

Change this:

cnn = Application.CurrentProject.Connection

to this:

Set cnn = Application.CurrentProject.Connection

 

[Dirk Goldgar]

I have a form with a text box and button and want to run an Access
query that takes an input parameter.
I have the following code for the button:
I get an error at the line where the arrow is pointed
requested operation requires an OLE DB Session object, which is not
supported by the current provider


Private Sub Command1_Click()
Dim prm0 As ADODB.Parameter
Dim cnn As New ADODB.Connection
Dim cmd1 As New ADODB.Command
Dim rst1 As ADODB.Recordset
Dim s1 As String
Text1.SetFocus
s1 = Text1.Text

By the way, you don't need to SetFocus to the text box to get its value,
only its Text property. You would do better to replace these lines:

Text1.SetFocus
s1 = Text1.Text

with this one:

s1 = Me!Text1