Can I access an encrypted SQL Server Compact database in Excel VBA?

Question

I want to access an encrypted SQL Server Compact Edition database via VBA. I can access the database fine when it is not encrypted, but the code breaks when I try to use a password:

pConn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Password=[my_password];Data Source=" & SdfPath

I've been following the connection string example provided here for SQL Server Compact with a password:

Encryption enabled

Use this connection string to enable encryption on the database.

Data Source=MyData.sdf;Encrypt Database=True;Password=myPassword; File Mode=shared read;Persist Security Info=False;

The Encrypt Database="True" pair is really not necessary as the presence of the Password-parameter itself turns on encryption for the connection.

But why doesn't this work in Excel VBA 2010?

Answer 1

Apparently, the connection string example in the site I was using is incorrect. I found the correct connection string example from Microsoft:

Using Microsoft ActiveX® Data Objects for Windows CE (ADOCE), Microsoft ActiveX Data Objects Extensions for Data Definition Language (DDL) and Security (ADOXCE), or the Replication object To create a password-protected database using the either the ADOCE or ADOXCE Catalog object, or the AddSubscription method of the SQL Server CE Replication ActiveX object, you must specify the provider-specific SSCE:Database Password connection property in the connection string. For example:

"Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0; data source=\NorthWind.sdf; SSCE:Database Password=[myPassword]"

And so now my code is:

pConn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;SSCE:Database Password=[my_password];Data Source=" & SdfPath

This worked perfectly for me.

Answer 2

If you have SQL Server Compact 4.0 installed and use "Microsoft.SQLSERVER.CE.OLEDB.2.0" or "Microsoft.SQLSERVER.CE.OLEDB.3.5" as provider in connection string, you will get an error mentioning provider not found. Change connection string to reflect to "Microsoft.SQLSERVER.CE.OLEDB.4.0".