Link to home
Start Free TrialLog in
Avatar of paulmcneil
paulmcneilFlag for United States of America

asked on

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
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>  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
Avatar of paulmcneil

ASKER

jimhorn,
Removing the ";TABLE=dbo.tblCustomer" from the connect string does not eliminate the error
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
Arthur Wood,
You suggested change raises the error: "Requested operation requires an OLE DB Session object, which is not supported by the current provider"
ok, then you should do it this way:

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

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

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"
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
 
''''''''''''''''''''''''''''''''''''''''''
ASKER CERTIFIED SOLUTION
Avatar of Computer101
Computer101
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial