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, it's recommended to use SqlCe over 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. Unfortunately, Access is still widely used. An Access database needs a lot of maintenance and needs to be compressed often. Because of Access being so old and no longer supported, I've removed those posts.
Now there is was 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 web hosting provider 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.
ASP-script to connect with SQL Server Compact database sdf file
Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0
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.
<html>
<head>
<title>Test SQL Compact 4 and ASP Classic + ADO</title>
</head>
<body>
<%
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")
' list all tables in an SqlCe database: https://stackoverflow.com/a/4017559/1297898
rs.Open "select table_name from information_schema.tables where TABLE_TYPE <> 'VIEW'", conn
do until rs.EOF
for each x in rs.Fields
Response.Write(x.name)
Response.Write(" = ")
Response.Write(x.value & "<br />")
next
Response.Write("<br />")
rs.MoveNext
loop
rs.close
conn.close
Set conn = Nothing
%>
</body>
</html>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 SQL Server. Update: Also don't forget to convert your SqlCe database to SQL Server now.
Use of SqlCe (SQL Server Compact) database is deprecated
Please see the following information about SqlCe being deprecated:
SQL Server compact edition is in deprecation mode with no new releases planned near future. Last release SQL CE 4.0SP1 (and earlier releases that are still in the support cycle) will continue to be supported through its lifecycle and Microsoft is committed to fix any major, production blocking issues found in these releases. At this point, we don't consider this issue to be in that category and hence we are closing this issue.
Microsoft @ SQL Server Connect
On the desktop/laptop deployments, migrating to SQL Server LocalDB/SQL Express is a possible option for many of the current users (http://msdn.microsoft.com/en-us/library/hh510202.aspx)
Some additional information is also in the SQL Server Compact forums, at https://social.msdn.microsoft.com/Forums/sqlserver/en-US/95797446-0f9d-4e75-9007-adcd6f6a9d1e/is-sql-server-compact-dead-?forum=sqlce and on WikiPedia: https://en.wikipedia.org/wiki/SQL_Server_Compact#Deprecation
Upgrade to MySQL or SQL Server is my advice.
Thank you very much! <3 ❤️
Hi, my name is Jan. I am not a hacker, coder, developer or guru. I am merely an application manager / systems administrator, doing my daily thing at Embrace – The Human Cloud. In the past I worked at CLDIN (CLouDINfra) and Vevida. With over 20 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, websites & optimization. I blog at https://www.saotn.org.