MySQL query error (ODBC 3.51)

Question

I'm trying to execute query in a VB6 app.

Here is my code:

Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.ConnectionString = "Driver={MySQL ODBC 3.51 Driver}; Server=***; Database=***; Username=***; Password=***; Option=3"
con.Open

Dim cmd As New ADODB.Command
With cmd
    .ActiveConnection = con
    .CommandText = "SELECT COD_CONFIG FROM FDT_CONFIG"
    .CommandType = adCmdText
End With

Dim rs As New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenStatic, adLockOptimistic

I've hidden some information in the connection string but in my app I'm using the correct values.

I can successfully open the connection, but when I try to execute the query I get:

"Execution error '2147467259 (800004005)' : unspecified error"

Can someone tell me where I'm going wrong?

Answer 1

If you want to assign a Command object's ActiveConnection to an existing Connection object, you must use Set:

With cmd
    Set .ActiveConnection = con
    ....
End With

It's a little confusing because you can also assign a string to the ActiveConnection property and ADO will create an ad-hoc connection for you. In that case, you wouldn't use Set because you're just assigning a value to an intrinsic type (string):

With cmd
    .ActiveConnection = "Driver={MySQL ODBC 3.51 Driver}; Server=***; Database=***; Username=***; Password=***; Option=3"
    ...
End With

So the property can be used multiple ways. In your scenario, however, since you're assigning an object reference, you'll need to use the Set keyword.