ADODB.ActiveConnection error

In Access 2003 VBA, I'm trying to code the deleting of a record using an adodb connection.
I have this ccode:

Global Const sconnecttest = "ODBC;DRIVER=SQL Server;SERVER=Pauljmcneil;APP=Microsoft Office 2003;" _
  & "WSID=PAULJMCNEIL;DATABASE=jjtptab13SQL;Trusted_Connection=Yes"

Dim cmd As New ADODB.Command
Dim cnn As New ADODB.Connection
 
  sql = "DELETE FROM dbo.tblCustomer WHERE dbo.tblCustomer.CustomerID=23283"
  Set cmd = New ADODB.Command
  cnn.ConnectionString = sconnecttest & ";TABLE=dbo.tblCustomer"
  cmd.ActiveConnection = cnn.ConnectionString
  cmd.CommandText = sql
  cmd.CommandType = adCmdText
  cmd.Execute

I get the following error on the line:
cmd.ActiveConnection = cnn.ConnectionString

"Data source name not found and no default driver specified"
Thanks
paulmcneilAsked:
Who is Participating?
 
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>  cnn.ConnectionString = sconnecttest & ";TABLE=dbo.tblCustomer"
The ADODB.Connection object is used to connect to a database, not a table, so lose the above line
0
 
paulmcneilAuthor Commented:
jimhorn,
Removing the ";TABLE=dbo.tblCustomer" from the connect string does not eliminate the error
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Arthur_WoodCommented:
Try this:  

sql = "DELETE FROM dbo.tblCustomer WHERE dbo.tblCustomer.CustomerID=23283"
  Set cmd = New ADODB.Command
  cnn.ConnectionString = sconnecttest
  cmd.ActiveConnection = cnn
  cmd.CommandText = sql
  cmd.CommandType = adCmdText
  cmd.Execute

AW
0
 
paulmcneilAuthor Commented:
Arthur Wood,
You suggested change raises the error: "Requested operation requires an OLE DB Session object, which is not supported by the current provider"
0
 
Arthur_WoodCommented:
ok, then you should do it this way:

sql = "DELETE FROM dbo.tblCustomer WHERE CustomerID=23283"
cnn.ConnectionString = sconnecttest
cnn.Execute sql

AW
0
 
paulmcneilAuthor Commented:
AW
With the code you suggested I get the error: "Operation is not allowed when the object is closed."
0
 
Arthur_WoodCommented:
sql = "DELETE FROM dbo.tblCustomer WHERE CustomerID=23283"
cnn.ConnectionString = sconnecttest
cnn.Open
cnn.Execute sql

0
 
paulmcneilAuthor Commented:
AW
That sequence of code raises the error:"Data source name not found and no default driver specified"
Again, the constant sconnecttest is set with:
Global Const sconnecttest = "ODBC;DRIVER=SQL Server;SERVER=Pauljmcneil;APP=Microsoft Office 2003;" _
  & "WSID=PAULJMCNEIL;DATABASE=jjtptab13SQL;Trusted_Connection=Yes"
0
 
paulmcneilAuthor Commented:
AW
The following code worked for deleting a single record with an ADO connection:


''''''''''''''''''''''''''''''''''''''''''
'Example of ADO code for deleting a record
''''''''''''''''''''''''''''''''''''''''''

Dim scnn As String
Dim ssql As String
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset

scnn = "DSN=jjtpsqlmcneil;Server=pauljmcneil;Database=jjtptab13SQL;Trusted_Connection = Yes;"
ssql = "SELECT  dbo.tblCustomer.* FROM dbo.tblCustomer WHERE dbo.tblCustomer.CustomerID=23283"
Set cnn = New ADODB.Connection
cnn.ConnectionString = scnn
cnn.Open
rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
With rs
  .Delete
End With
 
''''''''''''''''''''''''''''''''''''''''''
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.