4

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
Yots
  • 1,655
  • 6
  • 17
  • 25

2 Answers2

10

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"
ErikEJ
  • 40,951
  • 5
  • 75
  • 115
-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.

Shadow The GPT Wizard
  • 66,030
  • 26
  • 140
  • 208
  • I had no luck with your connection string but if I use `sCon = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=c:\temp\sqlcompact.sdf;"` I can connect now to a 4.0 database but only if the database is not encrypted – Yots Feb 15 '11 at 11:18
  • @Yots that's weird.. I fear I have no experience in this so can't help very much. What about DSN? Gave it a try? – Shadow The GPT Wizard Feb 15 '11 at 12:05