Hi,

I am converting an old application to use SQL Compact database (it works ok with SQ Server 2005 and 2008) and using the following code gives an error when attempting 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 have referenced Microsoft ActiveX Data Access Object 6.0 Library in the project.

The error I get is:

Run-Time error -2147217887 (80040e21)

Multipe-Step operation generated errors. Check each status value

Just wondering if anyone has any suggestions?

Thanks