CEQuery Tool for SQL Server Compact Edition Databases

Background
If You suspect that I, at present, work a lot with SQL Server Compact Edition (SSCE) databases than You are absolutely right about it. I have developed two larger solutions where SSCE databases are central parts of the respectively solution. In both projects data is exported to SSCE databases and then the data is acquired from these two sources to populate Excel reports with.

In one of the projects the client also wanted a simple tool to query the database with. I had two alternatives to choose between; build a query tool from scratch or look for a free/commercial query tool that could work with SSCE databases. For several reasons the client wanted in the end me to build a new tool. However, at that time I had already made some investigations and among the few tools I have found CEQuery Tool quickly became a favorite.

The CEQuery Tool
Working with the CEQuery Tool is very simple and straightforward. The tool provides us with several tools:

• Create new SSCE databases and tables from scratch.
• Open existing SSCE databases and acquire data from them.
• The acquired data can be exported to Excel, i.e. saved to Excel files.
• Created queries can be saved for later use and are written to Query files.
• Export data from a SQL Server database, either to an existing SSCE database or to a new SSCE database. The main window for the Export/Import tool is showed below after the list.
• A Script Generator with which we can create scripts, for instance to create or drop tables. These scripts can also be saved for later use and are written to Query files. The main window for the Script Generator is also showed below after the list.
• The CEQuery tool is free and the source code is available which allow us to customize and update the tool, if desirable.

The below screen shot shows CEQuery’s main menu, i.e for the available tools:

For some unknown reasons, at least to me, the Script Generator tool is available via CEQuery’s Command bar and via the right click menu in the Database window but not in the main menu.

The Excel Export tool is only available via the right click menu in the Datagrid’s window. Also commands to create and delete tables are only available via the right click menu in the Database’s windows and not via its main menu.

The screen shot of the Export/Import tool’s main window.

The screen shot of the Script Generator’s main window:

The next screen shot below shows CEQuery’s main window where we have opened a SSCE database, created the SQL expression and also acquired the data:

If we right click in the Database window we get access to additional commands which the below screen shot also shows:

After we have executed the Script Generator the Query file is generated. The following screen shot shows how its content can look like:

I have been using the tool for some time now and all its main tools and functions works as expected. The tool feels reliable and stable. Of course, it has some shortcomings like:

• The location of its main tools and commands.
• Issues with the UI, especially when resizing the windows.
• The size and the location of the tool’s main windows are not saved between the sessions.
• The help file is not embedded in the tool. It’s only available as a standalone file.
• The source code is available so if we find anything annoying or less good implemented we can actually change it.

If You work extensively with SSCE databases and are looking for a tool the CEQuery tool should be among the tools that should be evaluated.

Kind regards,
Dennis

Improve Your VBA code with TM VBA Inspector

Recently Thomas Möller (TM) made a blog entry about his free utility, TM VBA Inspector, at Microsoft Access Team’s blog. Since it looked interesting I downloaded it and tested it with Access 2010. I than made a comment at the blog about porting it to other tools in the Office suite, especially to Excel, as I find the add-in to be useful.

Thomas agreed on it and I helped him out by testing the add-in with Excel. Now it works with most of the tools in the Office suite and it can be downloaded from here: TM VBA Inspector

Because I find it to be useful I decided to write this article to spread the word about it. Once the tool is installed we can switch to the VBA IDE and select the command Add-ins from the main menu where we find a new entry as the below screen shot also shows:

To run the tool is quite easy; open the workbook in which You have code module to be evaluated and open the code module in the VBA IDE, select the command Add-ins > TM VBA-Inspector from the menu. When its main windows open You just select the command Refresh and then the code evaluation process starts. When done it shows something similar like the following screen shot:

By double-clicking an entry in the result list You switch directly to the appropriate place in the VBA code. There You can start update the identified error(s) or to improve the present code.

Before we start the code evaluation process we can do some changes in its settings by selecting the command Options from the tool’s main windows and then from the dialog we can uncheck/check a great number of various settings which also the screen shot below shows:

Make You selections and next click on the OK button to get back to its main windows. If wanted we can export the output of an evaluation process to a text file by selecting the Export command from its main windows. All in all, the add-in does its job very well and the outcome can also be quite learning.

Edit note (10/28/2010): In the latest version a new function has been implemented and that is to exclude modules from being reviewed by the tool if wanted.

Of course, I have a wish list that at present is a short list but I expect it to grow the more I use it:

• The tool use the Tool Window Form Border Style which act as the built-in forms do in the VBA IDE. Instead I would see that one of the Fixed Border Styles would have been used.
• After executing the evaluation process we get a nice list of recommendations in the main windows. It would have been great if we had access to a function that allowed us to easily replace the evaluated code with the recommendations. But I can understand if this will not be implemented as it requires a lot of time to implement it correctly.

Anyway, I suggest that You download the tool and test it out Yourself!

Kind regards,
Dennis

Populate TreeView Controls with Tables from SQL Server & Access Databases by using ADOX

This is the third article on how to populate a TreeView Control with Tables, Views and Columns from a SQL Server database respectively from an Access database. The previously blog entries on this subject are:

• Populate TreeView Control with Tables and Columns from MS Access Database
• Populate TreeView Control with Tables, Views and Columns from a SQL Server Database

The last article on the subject will be about how to do it by using LINQ to SQL. I have already used ADOX in previously blog posts, for instance in the article Create SQL Server Compact Edition Database with VBA. And because the library allows us to work with the framework of databases we can, for instance, retrieve all system tables from an Access database. The first code case below shows exactly how to do it. In the second case I demonstrate how to retrieve all tables from a SQL Server database, including views and system tables.

The code to retrieve all system tables in an Access database and to populate a TreeView with the tables:

'A reference to the following COM Library must be made in order
'to establish a connection to the database:
'Microsoft ActiveX Data Objects x.x Library

'To access tables and columns in the database a reference
'must be made to the following COM Library:
'Microsoft ADO Ext. x.x for DDL and Security

'x.x above refers to version like 2.8.

Imports System.Runtime.InteropServices
Imports System.Windows.Forms

Public Class frmMain

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

'Connection string for an Access 2010 Database.
Const Con As String = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=c:\NorthWind 2010.accdb;" & _
"Persist Security Info=False"

Const Database As String = "NorthWind"

Dim ADODBConnection As New ADODB.Connection
Dim ADOXCat As New ADOX.Catalog
Dim ADOXTbl As New ADOX.Table
Dim ADOXCol As New ADOX.Column

'Open the connection to the database.
ADODBConnection.Open(Con)

Dim Rootnode As TreeNode = Nothing
Dim Mainnode As TreeNode = Nothing
Dim Childnode As TreeNode = Nothing

Dim MainName As String = String.Empty

ADOXCat.ActiveConnection = ADODBConnection

TVDatabase.Nodes.Clear()

Rootnode = TVDatabase.Nodes.Add(key:="Root", text:=Database, _
imageIndex:=0, selectedImageIndex:=0)

'Populate the TreeView with tables' names and their columns' names.
For Each ADOXTbl In ADOXCat.Tables
'To retrieve only system tables.
If ADOXTbl.Name Like "MSys*" Then
'Add the tables' names.
Mainnode = Rootnode.Nodes.Add(key:="Table", text:=ADOXTbl.Name.ToString, _
imageIndex:=1, selectedImageIndex:=1)
'Add the columns' names.
For Each ADOXCol In ADOXTbl.Columns
Childnode = Mainnode.Nodes.Add(key:="Column", text:=ADOXCol.Name.ToString, _
imageIndex:=2, selectedImageIndex:=2)
Next ADOXCol
End If

Next ADOXTbl

TVDatabase.Nodes(0).EnsureVisible()

'Clean up.
ADODBConnection.Close()
Marshal.ReleaseComObject(ADODBConnection)
ADODBConnection = Nothing
Marshal.ReleaseComObject(ADOXTbl)
ADOXTbl = Nothing
Marshal.ReleaseComObject(ADOXCat)
ADOXCat = Nothing

End Sub

Next, below shows the code to retrieve all tables from a SQL Server Database and to populate the TreeView with the tables:

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

Const Con As String = _
"Provider=SQLOLEDB;Data Source=DENNIS-DATOR;" & _
"Password=street;User ID=DenWal;" & _
"Initial Catalog=ContosoRetailDW"

Const Database As String = "Contoso Retail"

Dim ADODBConnection As New ADODB.Connection
Dim ADOXCat As New ADOX.Catalog
Dim ADOXTbl As New ADOX.Table
Dim ADOXCol As New ADOX.Column

'Open the connection to the database.
ADODBConnection.Open(Con)

Dim Rootnode As TreeNode = Nothing
Dim Mainnode As TreeNode = Nothing
Dim Childnode As TreeNode = Nothing

Dim MainName As String = String.Empty

ADOXCat.ActiveConnection = ADODBConnection

TVSQLServer.Nodes.Clear()

Rootnode = TVSQLServer.Nodes.Add(key:="Root", text:=Database, _
imageIndex:=0, selectedImageIndex:=0)

'Populate the TreeView with tables' names and their columns' names.
For Each ADOXTbl In ADOXCat.Tables
'Add the tables' names.
Mainnode = Rootnode.Nodes.Add(key:="Table", text:=ADOXTbl.Name.ToString, _
imageIndex:=1, selectedImageIndex:=1)
'Add the columns' names.
For Each ADOXCol In ADOXTbl.Columns
Childnode = Mainnode.Nodes.Add(key:="Column", text:=ADOXCol.Name.ToString, _
imageIndex:=2, selectedImageIndex:=2)
Next ADOXCol
Next ADOXTbl

With TVSQLServer
.Sort()
.Nodes(0).EnsureVisible()
End With

'Clean up.
ADODBConnection.Close()
Marshal.ReleaseComObject(ADODBConnection)
ADODBConnection = Nothing
Marshal.ReleaseComObject(ADOXTbl)
ADOXTbl = Nothing
Marshal.ReleaseComObject(ADOXCat)
ADOXCat = Nothing

End Sub

The following screen shot shows the two TreeViews after we have populated them with the tables from the two databases.

That’s all for now. The next article will finalize the series about creating XLLs with Add-in Express.

Kind regards,
Dennis