Using SQL Server Compact Edition Database with Excel

Introduction
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.

Kind regards,
Dennis

I have also published a post about how to create a SSCE database with VBA.

Working with Folders

Introduction

It’s quite often that we need to work with folders in various Excel solutions. It involves creating, deleting, moving, copying and update folders with or without any user interactions.

In this article I will discuss and show the code in order to work with folders. In the first part I will cover the basic processes to handle folders and in the second part of the article I will discuss folders and some user interactions.

Before we start up we should make sure that we have a reference to the System.IO namespace in the project and that we have made an import statement at the top of the class module. We will be using classes and methods that reside in that namespace.

Basic work with Folders
In the first example we will create a new folder named “Code” within a user’s folder system. That is done as the following code snippet shows:

Const NewFolder As String = "c:\Users\Dennis Wallentin\Document\Code"

If Not Directory.Exists(NewFolder) Then
'If it not already exist we create the folder.
Directory.CreateDirectory(NewFolder)
End If

To delete an empty folder is also quite easy to do which the below code snippet also shows:

Const DeleteFolder As String = "c:\Users\Dennis Wallentin\Document\Code"

If Directory.Exists(DeleteFolder) Then
'If it exist we delete the folder.
Directory.Delete(DeleteFolder)
End If

But what if we want to delete folders that contain files? The above code will throw an exception if the folder is not empty. By setting the second argument explicit to “True” in the Delete method a recursive deleting of all files is executed. That includes also all the subfolders in the targeting folder. The following code snippet shows it:

Const DeleteFolder As String = "c:\Users\Dennis Wallentin\Document\Code"

If Directory.Exists(DeleteFolder) Then
'If it exist we delete all the files and the folder.
Directory.Delete(DeleteFolder, True)
End If

To move files and subfolders from one location to another is also an easy task to achieve. What should be noted is that the old folder is always deleted and that the new folder cannot exist when the code is being executed. If the folder already exists the code will throw an exception. The code snippet below shows the details.

Const Folder As String = "c:\Users\Dennis Wallentin\Document\Code"
Const Target As String = "c:\Test\Dennis"

If Directory.Exists(Folder) Then
'The new folder cannot exist.
If Not Directory.Exists(Target) Then
'Move file and subdirectories to
'the new created folder and delete
'the old folder.
Directory.Move(Folder, Target)
End If
End If

To copy a folder’s content to a new location requires another strategy then what we so far have been used. Instead we must use the method My.Computer.FileSystem.CopyDirectory which also the following code shows. The last parameter controls if any existing files should be overwritten or not.

Const Folder As String = "c:\Users\Dennis Wallentin\Document\Dennis"
Const Target As String = "c:\Test\Backup"

If Directory.Exists(Folder) Then
'If the destination folder does not exist it
'will automatically be created.
My.Computer.FileSystem.CopyDirectory(sourceDirectoryName:=Folder,
destinationDirectoryName:=Target, overwrite:=True)
End If

The CopyDirectory method has two additional parameters that we can use whereof one can be rather useful here. If we know that we will be handling large files or a great number of files that will take some time to copy then we can let the users know about it. To do so we add the parameter ShowUI to the code as the below code also shows.

'On top of the CLass module
Imports System.IO
Imports Microsoft.VisualBasic.FileIO
'...

Const Folder As String = "c:\Users\Dennis Wallentin\Document\Dennis"
Const Target As String = "c:\Test\Backup"

If Directory.Exists(Folder) Then
'If the destination folder does not exist it
'will automatically be created.
My.Computer.FileSystem.CopyDirectory(sourceDirectoryName:=Folder,
destinationDirectoryName:=Target, showUI:=UIOption.AllDialogs)
End If

When executing the code the following Progress Dialog in Windows 7 is showed (the option UIOption.AllDialog makes it possible):

And as we also can see from the screen shot the user can cancel the operation if necessary. Since the other methods of My.Computer.FileSystem are focused on single items they are of little interest in this context.

If You are used to use the SHFileOperation Function then it has been replaced with the IFileOperation Interface. For more information about the later please see IFileOperation Interface.

Some words about Special Folders
It’s beyond the scope for this article to discuss SpecialFolders but I would like to show a basic example on how we can work with Special Folders.

In the following example we will retrieve the path to My Documents folder for the current user:

Dim MyDocuments As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

MessageBox.Show(MyDocuments.ToString)

As we can see, to get access to the special folders we use the Environment.SpecialFolder.MyDocuments to access to MyDocuments Special Folder. When executing the above code we get the following information:

Folders & User Interactions
The UI design for the two cases, with some user interactions involved, is showed in the below screen shot.

In the first case the user click on a button in order to populate the ListBox control with names of Excel files. To get the file names from the pre-decided location we use the following code in the Click event for the button:

Private Sub btnFixedFolder_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles btnFixedFolder.Click

Const Folder As String = "C:\Users\Dennis Wallentin\Documents\Fixed Folder"

Dim dirInfo As New DirectoryInfo(Folder)
Dim ArrayFiles As FileInfo() = dirInfo.GetFiles("*.xlsx")

Me.lbFixedFolder.DataSource = ArrayFiles
End Sub

When executing the code the outcome is what the below screen shot shows:

If we need to iterate through the collection of files in the array it’s also quite easy to do. The following code shows an example where we extract the files names and add them to the ListBox:

For Each item In ArrayFiles
strBuilder = New StringBuilder(item.Name)
strBuilder.Replace(".xlsx", String.Empty)
Me.lbFixedFolder.Items.Add(strBuilder)
Next

In the next case the users must select a source folder. To let the users choose folder we add the FolderBrowserDialog control to the project. The following code makes the Folder Browser Dialog available to the users and populates the ListBox control with the Excel files from the selected folder:

Private Sub btnUserFolder_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles btnUserFolder.Click

Const Title As String = "Select a folder for the documents:"
Me.FolderBrowserDialog1.Description = Title

If Me.FolderBrowserDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

Dim Folder As String = Me.FolderBrowserDialog1.SelectedPath
Dim dirInfo As New DirectoryInfo(Folder)
Dim ArrayFiles As FileInfo() = dirInfo.GetFiles("*.xlsx")

Me.lbUserFolder.DataSource = ArrayFiles

End If

End Sub

When the code is executed the Folder Browser Dialog is launched as the following screen shot also shows:

After the selection of a source folder the Listbox gets populated as the following and final screen shot also shows:

I hope that the above has given some insight on how to work with folders in the VB.NET language.

Kind regards,
Dennis

Creating XLLs with Add-In Express.NET – Part 2

This is the second article about creating XLLs with Add-In Express.NET and it will focus on creating a Setup package for our solution. The first article about it can be viewed here.

Creating a Setup package is also very easy and straightforward with the Add-in Express.NET tool; we add a Setup project to the solutions. Select the command Project from the main menu and next select the command Create Setup Project as the following screen shot shows:

The above command will start the Setup Project Wizard as the below screen shot shows:

In the next step in the wizard we customize the Setup package additionally which the following screen shot also shows:

In the next step the Setup project is created and added to the Solution Explorer. In the Solution Explorer we can find out which files that have been added to the Setup project which also the following screen shot shows:

The file adxloader.dll.manifest includes additional settings that can be manipulated if required:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
 <assemblyIdentity name="XLDennisXLLs, PublicKeyToken=a51ebb7add3c8218" xllClass="XLDennisXLLs.XLLModule" />
 <loaderSettings generateLogFile="true" shadowCopyEnabled="true" privileges="user" configFileName="app.config" />
</configuration>

The next file, adxloader.XLDennisXLLs.dll, is the loader on the x86 platform while the file thereafter is the loader on the x64 platform. In other words we can with the Add-in Express.NET tool target both platforms with only one solution. The last added file, adregistrator.exe,  creates all the required entries in the Windows Registry and also remove them when uninstalling the solution.

The next screen shot shows the updated properties of the Setup project, it’s accessible by selecting the Setup project in the Solution Explorer and then hit the F4-button:

By reviewing the File System (on the Target Machine) in the Setup project we can see that the following files will be added to the targeting computers:

Keep in mind that we, upon creating the solution, decided to include version-neutral IAs to the Setup package. That’s why the Interop files are included. The Extensibility.dll file includes the IDTExtensibility2 Interface so the file is vital for solutions that make use of it.

Next, when reviewing the Custom Actions in the Setup project it shows that the file adregistrator.exe is used in all the actions which the below screen shot also shows:

When reviewing the basic Launch Conditions it only includes the requirement of having .NET Framework 4.0 installed on the targeting computers which the below screen shot shows. In addition, we can add our own launch conditions to the Setup project which makes it very flexible.

In order to create the Setup package we can select the command Build on the main menu and then select the command Build Solution as the following screen shot also shows:

After running the build command the Setup package has been created and it includes two files; a setup.exe file and a MSI file. The following screen shot shows it as well:

Now we have a Setup package that can be deployed in order to install our solution.

When we install the solution by executing the setup.exe file the installation process starts and shows the following screen shots to the end users:

If we look in the list of installed softwares on the targeting computers we will find our solution among them as the below screen shot reviews:

Based on the stepwise walk-through we can conclude the following:

• It’s very easy and simple to create a Setup package with the Add-In Express.NET tool.
• It’s also flexible and time saving and it requires little effort to get a professional Setup package by using the Add-In Express.NET tool.

In the next article, which is also the last one on the subject, I will discuss the pros and cons with using Add-in Express.NET for creating XLLs solutions.

Kind regards,
Dennis