paulmcneil
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=Micros oft Office 2003;" _
& "WSID=PAULJMCNEIL;DATABASE =jjtptab13 SQL;Truste d_Connecti on=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
I have this ccode:
Global Const sconnecttest = "ODBC;DRIVER=SQL Server;SERVER=Pauljmcneil;
& "WSID=PAULJMCNEIL;DATABASE
Dim cmd As New ADODB.Command
Dim cnn As New ADODB.Connection
sql = "DELETE FROM dbo.tblCustomer WHERE dbo.tblCustomer.CustomerID
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
ASKER
jimhorn,
Removing the ";TABLE=dbo.tblCustomer" from the connect string does not eliminate the error
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
sql = "DELETE FROM dbo.tblCustomer WHERE dbo.tblCustomer.CustomerID
Set cmd = New ADODB.Command
cnn.ConnectionString = sconnecttest
cmd.ActiveConnection = cnn
cmd.CommandText = sql
cmd.CommandType = adCmdText
cmd.Execute
AW
ASKER
Arthur Wood,
You suggested change raises the error: "Requested operation requires an OLE DB Session object, which is not supported by the current provider"
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
sql = "DELETE FROM dbo.tblCustomer WHERE CustomerID=23283"
cnn.ConnectionString = sconnecttest
cnn.Execute sql
AW
ASKER
AW
With the code you suggested I get the error: "Operation is not allowed when the object is closed."
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
cnn.ConnectionString = sconnecttest
cnn.Open
cnn.Execute sql
ASKER
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=Micros oft Office 2003;" _
& "WSID=PAULJMCNEIL;DATABASE =jjtptab13 SQL;Truste d_Connecti on=Yes"
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;
& "WSID=PAULJMCNEIL;DATABASE
ASKER
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= pauljmcnei l;Database =jjtptab13 SQL;Truste d_Connecti on = 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
'''''''''''''''''''''''''' '''''''''' ''''''
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=
ssql = "SELECT dbo.tblCustomer.* FROM dbo.tblCustomer WHERE dbo.tblCustomer.CustomerID
Set cnn = New ADODB.Connection
cnn.ConnectionString = scnn
cnn.Open
rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
With rs
.Delete
End With
''''''''''''''''''''''''''
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The ADODB.Connection object is used to connect to a database, not a table, so lose the above line