Error 430 Class doesn't support automation

[Guest_imported]

I've have created a com object in vb to access and retrieve datas from a sql server 7 . I've put some string message into my code in order to can debug the component which running on MTS . So following my debuging messages the error occured when I'm trying to instantiate the ADODB.Recordset by the instruction set rst=new adodb.connection. According to the sql server profiler the mts component connect to the database but failled to instantiated the recordset ?
The class properties which contains that function are
Instancing : MultiUse
MTSTransactionMode : NoTransaction
Below you can find the function where the error is generate .Is there someone who can understand what is the cause of the error ?
Public Function RetrieveDataFromSelect(ByVal strSql As String, ByVal strConnectionString As String) As Variant
Dim rst As ADODB.Recordset
Dim blnRstOpen As Boolean
Dim cnNewConnection As ADODB.Connection
Dim ctxObject As ObjectContext
Dim vntData As Variant
Dim strAdvancedProcess As String
On Error GoTo Err_Management:
Set ctxObject = GetObjectContext()
Set cnNewConnection = New ADODB.Connection
strAdvancedProcess = "Trying to open database connection...."
cnNewConnection.Open (strConnectionString)
strAdvancedProcess = strAdvancedProcess & vbCrLf & "Database connection open setting dateformat ...."
cnNewConnection.Execute "Set Dateformat dmY "
strAdvancedProcess = strAdvancedProcess & vbCrLf & "Date format set.Trying to launch application role ...."
cnNewConnection.Execute "sp_setapprole 'Talent_App_Role', {ENCRYPT N 'ijfdw'},'Odbc'"
cnNewConnection.CursorLocation = adUseClient
strAdvancedProcess = strAdvancedProcess & vbCrLf & "Database Connection succeed !....Trying to instantiate recordset..."
Set rst = New ADODB.Recordset
strAdvancedProcess = strAdvancedProcess & vbCrLf & "Recordset instantiated !...."
rst.Open strSql, cnNewConnection, adOpenDynamic
strAdvancedProcess = strAdvancedProcess & vbCrLf & "Recordset open !...."
blnRstOpen = True
If Not rst.BOF Then
rst.MoveFirst
vntData = rst.GetRows(rst.RecordCount())
'Debug.Print strSql
'Debug.Print rst.RecordCount()
Else
vntData = Empty
End If
rst.Close
Set rst = Nothing
cnNewConnection.Close
Set cnNewConnection = Nothing
If Not ctxObject Is Nothing Then
ctxObject.SetComplete
End If
RetrieveDataFromSelect = vntData
Exit Function
Err_Management:
If Not ctxObject Is Nothing Then
ctxObject.SetAbort
End If
If blnRstOpen Then rst.Close
Set rst = Nothing
Err.Raise 10002, "Method: RetrieveDataFromSelectClass: clsDbConnection ", "Error description :" & Err.Description & vbCrLf & "Error number : " & CStr(Err.Number) & vbCrLf & "Specific description : An error has occured during the try to execute the query :" & strSql & vbCrLf & strAdvancedProcess
End Function


Thanks by advance

 

[palbano]

David,

> Set ctxObject = GetObjectContext()
> Set cnNewConnection = New ADODB.Connection

Do this instead

Set cnNewConnection = ctxObject.CreateInstance("ADODB.Connection&quot

Good luck
-pete

 

[Guest_imported]

I have finaly find a solution to that problem. It can be solve by the following code :

dim rst (instead of dim rst as adodb.recordset)
set rst=cnNewConnection.execute(strsql) (instead of set rst=new adodb.recordset , rst.open strsql,cnNewconnection,adopendynamic)

 

[BigLar]

Palbano,

ADO is NOT an MTS component and therefore should NOT be instantiated with CreateInstance. Use

Dim x as ADODB.Connection
Set x = CreateObject("ADODB.Connection&quot

or

Dim x as ADODB.Connection
Set x = New ADODB.Connection

Both of which will instantiate the connection using COM methods and early binding, which is exactly what is needed.

Larry
Larryh@ecn.ab.ca