Often we see questions about acquiring data from various data sources like SQL Server Databases, MDBs, Excel and text/xml-files in webbased Q&A forums and in blogs. But what I see very rarely is any discussions on how to retrieve data from Microsoft SQL Server Compact Edition (SSCE) databases and to populate Excel worksheets with the acquired data. This article will introduce how to use SCEE databases as data sources and dump the retrieved data into a pre-defined range in a worksheet.
SSCE is a lightweight version of the SQL Server database engine. It requires a minimal installation and configuration to use. Unlike its bigger “brother” it’s a server less database engine.
For an introduction to SSCE databases please see the following sites:
- Microsoft’s start page for SSCE and from where You can download the latest free version of it: Microsoft SQL Server Compact 3.5
- The Microsoft SSCE Team blog
- Wikipedia’s page about SSCE: SQL Server Compact
This article describes how to proceed to get data into an Excel solution from a SSCE database by using VBA. But before diving into it let us first take a look on how to create a SSCE database and its limitations.
Creating SSCE Databases
As with many other targets to achieve; creating SSCE databases can be done in some ways. Here I will show how simple it is by using the SQL Server Manager Studio which I regular use (as opposite to my use of the standalone SSCE database engine which I never use.)
Actually, it’s very simple to create a SSCE database from scratch:
- Open the SQL Server Manger Studio and in the Connect to Server Dialog we first select the Server type SQL Server Compact Edition and select <New Database…> in the Database File’s field as the following screen shot shows:
- In the next step we actually create the SSCE database as the following screen shot also shows:
When the database has been created we need to add the wanted tables and their contents inside the SQL Server Manager Studio.
But what if we have a SQL Server database and would like to convert it to a SSCE database? The easiest way I have found out is simple to use a third-party tool to do it for us. In my opinion the best tool for the job is the inexpensive Data Port Wizard tool from Primeworks. In addition to convert SQL Server databases it can also convert in a smooth way MS Access databases to SSCE databases. In other words, it shouldn’t be difficult to port these database types to SSCE database type.
Limitations of SSCE Databases
SSCE is on its face very attractive but before we get carried away we need to take a closer look into its limitations.
- The most important limitation, in my opinion, is that the SSCE does not support the use of views, stored procedures or functions. In other words, its main job is to store larger sets of data and make the data available for mobiles and desktop applications and nothing else.
- The maximum database size is 4 GB which is in my world is a rather large database size. But by default the data size is only 256 MB and to change the size change we need to add the parameter Max Database Size=xxxx MB to the connection string.
- The maximum rowsize is 8060 bytes but since this exclude text and blob fields there exist rarely a practical limitation, at least not in my experience.
- It does not support ASP/ASP.NET which does not affect me at all.
- Edit: SSCE targets individuals only, i.e. it does not allow multiple users to access a database simultaneously which SQL Server and the Express Edition allow (see Mathias comment below).
Populate a Worksheet by using VBA, ADO and a SSCE Database
Before we start to take a closer look on the code solution let me first mention that if You want more knowledge about ADO than You can download MDAC 2.8 which includes a superb help file (ado28.hlp) on the subject: MDAC 2.8 SDK
The next thing I feel I must point out is the driver to use with SSCE databases and the required system files for running them. When distributing solutions that involve SSCE databases we must also distribute theses files. Actually, in my experience this is a must in all cases. To do so we first need to get the redistributing MSI file which we do by downloading the package Microsoft SQL Server Compact 3.5 Service Pack 2 for Windows Desktop. When running the EXE file we get the required MSI file for x86 as well as the MSI file for the x64 platform. Actually included in the package is also the MSI file that includes both these two files.
What is also notable is that although we know it’s available the driver does not show up in the Data Link Wizard or in the .NET Wizard. Because of its anonymous character we can only get the correct connection string by trying. But a good start for the efforts is SQL Server Compact Edition Connection Strings Samples.
After installing the required files we can execute, for instance, the following code in order to populate a worksheet with data from a SSCE database:
Option Explicit 'Make sure You have a reference set to Microsoft 'ActiveX Data Objects 2.5 Library or later. Sub Retrieve_Data_From_SSCE_In_VBA() 'Name of the SSCE database. Const C_stDBName = "XLDennis.sdf" 'The SQL Expression to be used. Const C_stSQL = "SELECT CompanyName AS Company, City FROM Customers;" Dim stPath As String Dim stConnection As String Dim ADODBcnt As ADODB.Connection Dim ADODBrst As ADODB.Recordset Dim ADODBfld As ADODB.Field Dim wbBook As Workbook Dim wsSheet As Worksheet Dim rnstart As Range Dim lnFieldCounter As Long Set wbBook = ThisWorkbook Set wsSheet = wbBook.Worksheets(1) Set rnstart = wsSheet.Range("A2") 'Path to the SSCE database. stPath = "C:\Users\Dennis Wallentin\SSCE\" 'Connection string. stConnection = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" & _ "Data Source=" & stPath & C_stDBName & ";" 'Instantiate the ADODB objects. Set ADODBcnt = New ADODB.Connection Set ADODBrst = New ADODB.Recordset 'Open the connection to the databas. ADODBcnt.Open stConnection 'Create a disconnected recordset. With ADODBrst .CursorLocation = CursorLocationEnum.adUseClient .Open C_stSQL, _ ADODBcnt, _ ADODB.CursorTypeEnum.adOpenForwardOnly, _ ADODB.LockTypeEnum.adLockReadOnly, _ ADODB.CommandTypeEnum.adCmdText .ActiveConnection = Nothing End With 'Close down the connection. ADODBcnt.Close Application.ScreenUpdating = False 'Populate the worksheet with the field names. For Each ADODBfld In ADODBrst.Fields rnstart.Offset(-1, lnFieldCounter).Value = _ ADODBrst.Fields(lnFieldCounter).Name lnFieldCounter = lnFieldCounter + 1 Next ADODBfld 'Dump the data into the worksheet. rnstart.CopyFromRecordset ADODBrst 'Close the recordset. ADODBrst.Close 'Release objects from memory. Set ADODBrst = Nothing Set ADODBcnt = Nothing End Sub
Because of the limited differences between VBA and VB.NET I decided to exclude the sample when using VB.NET. I believe it should not be a problem for You.
I have also published a post about how to create a SSCE database with VBA.