Ado connection to SQL Server Compact Edition 4.0

Question

I want to connect to SQL Server Compact Edition 4.0 from an old asp-classic site but i always get the error:

"Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. "

I tried

sCon = "Data Source=c:\temp\sqlcompact.sdf;Encrypt Database=True;Password=testtest;Persist Security Info=False;"

and

Update: Error: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done

sCon = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=c:\temp\sqlcompact.sdf;Password=testtest;"

without any success.

Is it generally possible to connect to SQL Server CE 4.0 from ADO?

Update: Example Code Open Connection:

dim sCon

dim gCON : set gCON=CreateObject ("ADODB.Connection")

sCon = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=c:\temp\sqlcompact.sdf;Pwd=testtest;"

gCon.ConnectionString = sCon
gCon.Open 
gCon.Close

Answer 1

Try with the the following provider instead, saw somewhere it's being used with success:

sCon = "Provider=Microsoft.SqlServer.Mobile.OleDb.3.0;Data Source=c:\temp\sqlcompact.sdf;Password=testtest;"

If no luck, can you create System DSN successfully? If so, create one then use it in the ASP code.

Answer 2

Yes, you can connect to SQL CE 4 via ADO.

Set Cnxn = CreateObject("ADODB.Connection") 
Set cmd = CreateObject("ADODB.Command")
strCnxn = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;" & _ 
"Data Source=C:\nw40.sdf;" 
Cnxn.Open strCnxn 
cmd.ActiveConnection = Cnxn 
cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES" 
While Not pRS.EOF 
   WScript.Echo pRS(0) 
   pRS.MoveNext 
wend

For password protected files, use:

strCnxn = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;" & 
 _ "Data Source=C:\nw40.sdf;ssce:database password=secret"