• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

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.
0
paulmcneil
Asked:
paulmcneil
  • 3
1 Solution
 
rockiroadsCommented:
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now