Connecting to Microsoft SQL Server Compact

The SaveToDB add-in allows connecting to Microsoft SQL Server Compact databases.

The add-in ships the Microsoft SQL Compact Data Provider 4.0. So, you can use it from the box.

Using the .NET data provider is the only option.

You can download the latest version here:

• Microsoft SQL Server Compact 4.0 SP1 at https://www.microsoft.com/en-us/download/details.aspx?id=30709
• Microsoft SQL Server Compact 3.5 SP2 at https://www.microsoft.com/en-us/download/details.aspx?id=5783.

Here is a sample of the Database Connection Wizard page where you can select the data provider:

Connecting to Microsoft SQL Server Compact Databases

You can use an absolute path or a path relative to the active workbook.

The SaveToDB add-in allows using environment variables like %LOCALAPPDATA% in the paths also.

You can encrypt a database with a password.

Here is a sample of the connection:

Creating Microsoft SQL Server Compact Database

You can create a Microsoft SQL Server Compact database during connection.

This feature is useful, for example, for the Data Publishing Wizard and Application Installer.

Below is a dialog box sample:

Check Overwrite an existing database file to recreate a database file.

You can specify a password to encrypt the database file.

Encryption modes:

• Platform Default: The algorithms used in this mode are AES128_SHA256, where AES128 is the encryption algorithm with a 128-bit key and SHA256 is the hash algorithm with a 256-bit key. This is the default encryption mode option on all SQL Server Compact 4.0 supported platforms.
• Engine Default: In this mode, the database is encrypted using AES256_SHA512, where AES256 is the encryption algorithm and SHA512 is the secure hash algorithm. The default key length is used to maintain backward compatibility with SQL Server Compact 3.5.