Error connecting to a SQL Compact (CE) database

[xavierh]

The database file exists, and I can connect to it and read its data from C#.

But I also need to access the data from autoit. I have tried variants of the provider string below, but the connection just fails to open.

Dim $sqlCon = ObjCreate("ADODB.Connection")

$sqlCon.Open("Provider=Microsoft.SQLSERVER.OLEDB.CE.3.5;Data Source=C:\Auto\tests.sdf;Password=test")

Has anyone managed to connect to a sql ce database?

Thanks,

Xavier

[PsaltyDS]

Isn't that provider a .NET library? AutoIt can't use that. Do you have any examples of connection strings for SQL CE that work with ADODB in VBScript or some other non-.NET source?

[xavierh]

I finally got it to work as long as the db is not encrypted (ie it was not created with a password). If you add a password to the db when created from C#, both vbs and autoIT fail to connect.

Here are working samples

vbscript:

set oCon = createobject("ADODB.Connection")

set oRS = createobject("ADODB.Recordset")

oCon.Open "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=C:\Auto\PPDTM\PPDTM\PPDTM\bin\Debug\tests.sdf"

oRS.CursorLocation = 3 'adUseClient

oRS.Open "select * from PSMWinPrinters", oCon, 3, 3 'adOpenStatic, adLockOptimistic

msgbox oRS.recordcount & " records"

msgbox oRS.Fields(1).Value

oRS.Close

oCon.Close

AutoIT script:

Dim $sqlCon = ObjCreate("ADODB.Connection")

$sqlCon.Open("Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=C:\Auto\PPDTM\PPDTM\PPDTM\bin\Debug\tests.sdf")

Dim $oRS = ObjCreate ( "ADODB.Recordset" )

$oRS.open ( "select * from PSMWinPrinters", $sqlCon, 1, 1, 1 ) ;$adOpenKeyset, $adLockOptimistic=3, $adCmdText )

msgbox(0,"", $oRS.recordcount & " records")

[PsaltyDS]

Thanks for reporting your resolution.

Cheers!