Tips and tricks for using SQL Server Compact with VB/VBA/VBScript/ASP

This may sound a bit old-school, and it probably is, but some developers of Visual Basic still find that SQL Server Compact is a compelling local database solution.

To use SQL Server Compact with VB, you must have the SQL Server Compact runtime MSI installed on the computer, as the only way to access SQL Server Compact from VB is via the OLEDB provider, that must be registered on the machine by the MSI installer.

To use the OLEDB provider, you need the provider name, and for SQL Server Compact, this is:

Version 3.0/3.1:  Microsoft.SQLSERVER.MOBILE.OLEDB.3.0

Version 3.5: Microsoft.SQLSERVER.CE.OLEDB.3.5

Version 4.0: Microsoft.SQLSERVER.CE.OLEDB.4.0

It my blog post here I describe how you can access and iterate a table from VBA.

XL Dennis has also blogged about the same subject.

Notice that you cannot access columns of type “image” using the OLEDB provider, let alone INSERT into these columns – I describe a workaround for the INSERT case in my blog post here.

As the database access from takes place via the OLEDB provider, for some connection string properties, make sure to use the ssce: variant, if available. So for example to access a password protected database file from VB/VBA, use this connection string format:

Data Source=mydb.sdf;ssce:database password=123

XL Dennis has also blogged about creating a SQL Server Compact database from VB/VBA.

In order to do UPDATE, INSERT and DELETE, you must construct the required SQL statements as strings, and simply execute them, using ADO.Connection .Execute method.

Finally, in my blog post here, I demonstrate that it is possible to use SQL Server Compact 4.0 from Classic ASP.

Hopefully, this collection of tips and tricks is enough to get you started with VBA/VB and SQL Server Compact.

Access SQL Server Compact 4 with ASP Classic and VbScript

Now that you can use SQL Server Compact 4 with ASP.NET, some developers wonder if you can also use it with ASP Classic. To access with ASP Classic, the SQL Server Compact 4.0 OLEDB provider must be installed on the system, so the 4.0 MSI must be installed by an administrator – no private deployment.

But YES, you can access a SQL Server Compact 4 database file from ASP Classic:

 image

(The url is: http://localhost/aspclassic/default.asp)

Of course the required read/write permissions must be given the the relevant process user to the folder where the database file is located.

Here is the code to do it:

<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=C:inetpubwwwrootAspClassicApp_Datanw40.sdf;"

conn.Open strCnxn

set rs = Server.CreateObject("ADODB.recordset")
rs.Open "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES", 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
%>

</body>
</html>