Connect to SqlCe (SQL Server Compact) database from ASP

Connect to SQL Server Compacy (SqlCe) database with ASP. Here is how to install the Microsoft.SQLSERVER.CE.OLEDB.4.0 Provider and use classic ASP to connect to an SqlCe (SQL Server Compact) database sdf file, as a Microsoft Access database replacement. For Windows Server IIS. SQL Server Compact (SqlCe), the embedded database engine, is the default database for Visual Studio 2010 SP1 and WebMatrix 3. This means, you can better make use of SqlCe than Access nowadays.


Replace MS Access database with SQL Server Compact

Not too long ago I wrote two articles about MS Access as a database back-end (in Dutch). Access is still widely used. An Access database needs a lot of maintenance and needs to be compressed often.

But there is a new kid in town: SQL Compact Server 4.0, or SqlCe for short.

SqlCe database of choice with WebMatrix 3

SQL Server Compact, the embedded database engine, is the default database for Visual Studio 2010 SP1 and WebMatrix 3. This means, you can better make use of SqlCe than Access nowadays. Practically every hostingprovider should support the use of SqlCe databases, We at Vevida do.

Microsoft SQL Server Compact 4.0 is a free, embedded database that software developers can use for building ASP.NET websites and Windows desktop applications. SQL Server Compact 4.0 has a small footprint and supports private deployment of its binaries within the application folder, easy application development in Visual Studio and WebMatrix, and seamless migration of schema and data to SQL Server.

Download and install SQL Server Compact 4.0

If you want to be able to connect to an SQL Server Compact 4.0 file from a classic ASP script, you need to download and install the appropriate version of the drivers.

You may also like:  Send email with SMTP authentication and TLS using ASP

ASP-script to connect with SQL Server Compact database sdf file

This piece of ASP-code connects to an SqlCe database, performs a query and list all fields. You need to set the correct path to your database file.

    <title>Test SQL Compact 4 and ASP Classic + ADO</title> 

set conn = Server.CreateObject("ADODB.Connection")
strCnxn = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;" & _ 
    "Data Source=D:\www\path\to\database\SqlCe_file.sdf;"

conn.Open strCnxn
set rs = Server.CreateObject("ADODB.recordset") 
rs.Open "SELECT * FROM table", conn
do until rs.EOF 
    for each x in rs.Fields 
       Response.Write(" = ") 
       Response.Write(x.value & "<br />") 
    Response.Write("<br />") 

Set conn = Nothing

SqlCe conclusion

The SQL Server Compact database engine has great performance and is a really great replacement for your old Microsoft Access databases. If you still use MS Access, convert and migrate Access to SqlCe. You won’t regret it.


Did you like this post? Buy Me a Cup of Coffee

Did you find this article useful? Has it helped you solve a problem? Or has it saved you time?

Support and buy me a coffee (we sysadmins thrive on coffee :P ). A small, one-time, donation of USD $2.50 is more than enough and helps me with the research time, growth and hosting costs. Or use this link to enter your own donation amount.

Fast and secure through Paypal this'll support me in my research time and hosting costs, thank you!


Do you have anything interesting to add, or have an opinion? Found an error or typo? Found something to your liking? Let me know and leave a comment! As always, don't forget to share this post with your friends, family and co-workers!