VB6 ADODB does not work with SQL Compact: errors caused by several steps

Question

I am converting an old application to use the SQL Compact database (it works fine with SQ Server 2005 and 2008), and using the following code gives an error when trying to execute a simple select command:

Private Const mSqlProvider          As String = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;"
Private Const mSqlHost              As String = "Data Source=C:\database.sdf;"
Private mCmd                        As ADODB.Command   ' For executing SQL'
Private mDbConnection               As ADODB.Connection


Private Sub Command1_Click()

    Dim DbConnectionString As String

    DbConnectionString = mSqlProvider & _
                            mSqlHost


    Set mDbConnection = New ADODB.Connection
    mDbConnection.CursorLocation = adUseClient

    Call mDbConnection.Open(DbConnectionString)

    If mDbConnection.State = adStateOpen Then
        Debug.Print (" Database is open")
        ' Initialise the command object'
        Set mCmd = New ADODB.Command
        mCmd.ActiveConnection = mDbConnection

    End If


    mCmd.CommandText = "select * from myTable"
    mCmd.CommandType = adCmdText
    mCmd.Execute  ' FAILS HERE! '


End Sub

I refer to the Microsoft ActiveX Data Access Object 6.0 Library in the project.

The error I am getting is:

Runtime Error -2147217887 (80040e21)

Actions with several steps caused errors. Check each status value

Just wondering if anyone has any suggestions?

thank

Answer 1

Got it now:

Change

mDbConnection.CursorLocation = adUseClient 

to

mDbConnection.CursorLocation = adUseServer worked!  

Answer 2

100%, , - , " ". :

• (, - .)

• ( )

• , null.

• . (, 50 10 )

, , .

Answer 3

:

• What is this OLE DB multiple step error?
• OLE DB multi-step operations generated errors

This will be one of three things:

• Connection string (which driver are you using?)
• Security (using built-in security instead of username / password)
• or, as Belliz said, cursor location