Ado Connectivity with 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 an 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: OLE DB multi-step operation generated errors. Check each OLE DB status value, if available. There was no work.

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

without any success.

Can I connect to SQL Server CE 4.0 from ADO at all?

Update: Sample 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

Yes, you can connect to SQL CE 4 through 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" 

Answer 2

: - :

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

, DSN? , , ASP.