executing and adodb command

I have the following connect string constant:
Global Const scnn_ado_mcneil = "DSN=jjtpsqlmcneil;Server=pauljmcneil;Database=jjtptab13SQL;Trusted_Connection = Yes;"

I have the following code to use an adodb command to update a table
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command

Set cnn = New ADODB.Connection
cnn.ConnectionString = scnn_ado_mcneil
cnn.Open
ssql = "UPDATE dbo.tblWant SET Bought=0 WHERE CustomerID=1"
Set cmd = New ADODB.Command
cmd.CommandText = ssql
cmd.Execute
 
On the line cmd.Execute I get the error:
"The connection cannot be used to perform this operation. It is either closed or invlaid in this context.
paulmcneilAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
Any reason u are using a ADO command to do it?

Wont

cnn.Execute ssql

do? i.e use ADODB.Connection execute method
0
 
rockiroadsCommented:
If u use the Command object, u need to give it a connection

e.,g.

cmd.ActiveConnection = cnn
0
 
rockiroadsCommented:
Of course, if u dont want to use ADO, there is the simple way of

DoCmd.RunSQL ssql

or even perhaps

CurrentDB.Execute ssql
0
 
Leigh PurvisDatabase DeveloperCommented:
Well - those will work in a linked table set up - but the SQL will need altering (i.e. "dbo." :-)

Oh - and don't forget when setting the connection...

Set  cmd.ActiveConnection = cnn
'^^
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.