INFO: Cannot Create a SQL Server CE Database on Connection.Execute (271592)

SUMMARY

If you create a SQL Server CE database by using the Microsoft ActiveX Data Objects (ADO) for Windows CE 3.1 (ADOCE) Connection.Execute method, you may receive the following error message when you try to open it:

An Error was encountered while running this program: Unspecified Error

You can use ADOXCE Catalog object to create a SQL Server CE database.

MORE INFORMATION

The following Visual Basic program demonstrates the problem and resolution:

Create three command buttons and paste the sample code in a Visual Basic project.
Add references to Microsoft CE SQL Server Control 1.0, Microsoft CE ADO Control 3.1, and Microsoft CE ADO Ext. 3.1 for DDL.

Sample Code

Option Explicit

Private Sub Command1_Click()

' Incorrect
    Dim cn As ADOCE.Connection
    Set cn = CreateObject("ADOCE.Connection.3.1")
    cn.Open
    cn.Execute "CREATE DATABASE ""\ssce.SDF"""
    cn.Close
    
End Sub

Private Sub Command2_Click()

' Open it; gives unspecified error after the above, works for the following.
    Dim cn As ADOCE.Connection
    Set cn = CreateObject("ADOCE.Connection.3.1")
    cn.ConnectionString = "Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0; data source=\ssce.SDF"
    cn.Open
    cn.Close
    
End Sub

Private Sub Command3_Click()

' Correct
    Dim cat As ADOXCE.Catalog
    Set cat = CreateObject("adoxce.catalog.3.1")
    cat.Create "Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0;data source=\ssce.SDF"

End Sub
-----------------