I'm transforming a classic application to make use of SQL Compact database (it really works comfortable with SQ Server 2005 and 2008) and taking advantage of the next code gives a mistake when trying to carry out a simple choose 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 & _

    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've recommended Microsoft ActiveX Data Access Object 6. Library within the project.

The mistake I recieve is:

Run-Time error -2147217887 (80040e21)

Multipe-Step operation produced errors. Check each status value

Just wondering if anybody has any suggestions?


Got this working now:


mDbConnection.CursorLocation = adUseClient 


mDbConnection.CursorLocation = adUseServer worked!  

I am not 100% sure the mistake comes from this code instead of something after it which is the "last known good" type position. Most typical reasons for the mistake:

  1. You're unconditionally transforming data in to the wrong datatype (e.g. An alpha string value has been placed right into a number area. )

  2. You're placing something having a wrong format (has a tendency to happen on dates most often)

  3. A null value has been placed right into a area that doesn't allow nulls.

  4. You've exceeded the max entire area having a value. (e.g. adhering a string 50 figures lengthy inside a 10 character area)

Also, there's nothing catching a mistake when the connection isn't open.

This is much like both of these:

It will likely be among three things:

  • The bond string (what driver are you currently using?)
  • Security (using Integrated Security rather than a username/password)
  • or as Belliez stated, the Cursor Location