Tuesday, April 15, 2008

Import SQL Compact data to Excel

I have made the small VBA sample below, to demonstrate how you can easily import data from a SQL Compact database file using the OLEDB provider included with SQL Compact and some good old VBA code.

Before using this sample, add a reference to ADO (Microsoft ActiveX Data Objects) in your Excel VBA project, as shown below:

image

Hope you find it useful.

UPDATE: To use a password, use the following connection string:

pConn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;ssce:Database Password=xyz;Data Source=" & SdfPath

Option Explicit

Const SdfPath = "C:\Northwind.sdf"

Sub ImportSqlCeData()
Dim pConn As ADODB.Connection
Dim pRS As ADODB.Recordset
Dim rRS As ADODB.Recordset

Set pConn = New ADODB.Connection
Dim cmd As New ADODB.Command
Set pRS = New ADODB.Recordset

Set rRS = New ADODB.Recordset
Dim rcmd As New ADODB.Command

' For 3.0 use PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0
pConn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=" & SdfPath
pConn.Open

rcmd.ActiveConnection = pConn
cmd.ActiveConnection = pConn
cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"
Set pRS = cmd.Execute
' Open the recordset
Dim ws As Worksheet
Dim rng As Range

Dim i As Long
Dim row As Long

While Not pRS.EOF
row = 1
Set ws = Application.ActiveWorkbook.Worksheets.Add()
ws.Name = pRS(0).Value
rcmd.CommandText = "SELECT * FROM [" & pRS(0).Value & "]"
Set rRS = rcmd.Execute
' Headers
For i = 0 To rRS.Fields.Count - 1
Set rng = ws.Cells(row, i + 1)
rng.Value = rRS.Fields(i).Name
Next i
' Data rows
row = 2
While Not rRS.EOF
For i = 0 To rRS.Fields.Count - 1
Set rng = ws.Cells(row, i + 1)
If (rRS.Fields(i).Type = adLongVarBinary) Then
rng.Value = "<Binary - not supported>"
Else
rng.Value = rRS(i).Value
End If
Next i
rRS.MoveNext
row = row + 1
Wend

pRS.MoveNext
Wend

End Sub

Import SQL Compact data to Excel

I have made the small VBA sample below, to demonstrate how you can easily import data from a SQL Compact database file using the OLEDB provider included with SQL Compact and some good old VBA code.

Before using this sample, add a reference to ADO (Microsoft ActiveX Data Objects) in your Excel VBA project, as shown below:

image

Hope you find it useful.

Option Explicit

Const SdfPath = "C:\Northwind.sdf"

Sub ImportSqlCeData()
Dim pConn As ADODB.Connection
Dim pRS As ADODB.Recordset
Dim rRS As ADODB.Recordset

Set pConn = New ADODB.Connection
Dim cmd As New ADODB.Command
Set pRS = New ADODB.Recordset

Set rRS = New ADODB.Recordset
Dim rcmd As New ADODB.Command

' For 3.0 use PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0
pConn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=" & SdfPath
pConn.Open

rcmd.ActiveConnection = pConn
cmd.ActiveConnection = pConn
cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"
Set pRS = cmd.Execute
' Open the recordset
Dim ws As Worksheet
Dim rng As Range

Dim i As Long
Dim row As Long

While Not pRS.EOF
row = 1
Set ws = Application.ActiveWorkbook.Worksheets.Add()
ws.Name = pRS(0).Value
rcmd.CommandText = "SELECT * FROM [" & pRS(0).Value & "]"
Set rRS = rcmd.Execute
' Headers
For i = 0 To rRS.Fields.Count - 1
Set rng = ws.Cells(row, i + 1)
rng.Value = rRS.Fields(i).Name
Next i
' Data rows
row = 2
While Not rRS.EOF
For i = 0 To rRS.Fields.Count - 1
Set rng = ws.Cells(row, i + 1)
If (rRS.Fields(i).Type = adLongVarBinary) Then
rng.Value = "<Binary - not supported>"
Else
rng.Value = rRS(i).Value
End If
Next i
rRS.MoveNext
row = row + 1
Wend

pRS.MoveNext
Wend

End Sub

Testing an sdf file for version info

Christ Tacke from opennetcf.org has put together a nice little piece of code to test the SQL Compact database version by looking at the binary contents of the file, thus avoiding program flow based on catching exceptions.

Sunday, April 13, 2008

Mobile sample using SQL Compact

Microsoft has recently released the excellent sample called Windows Mobile Line of Business Solution Accelerator 2008, which includes a sample using SQL Compact 3.5 for local device data storage, and ADO.NET Sync Services for Devices for replication. The data access layer in the sample includes a way (in code), to get two much asked for "features" with SQL Compact, stored procedures and triggers. Highly recommended!

In addition, Rabi Satter, one of the developers of the sample, has been blogging about the design decisions behind the "managed stored procedures" feature.

Rob Tiffany has a detailed walk through with plenty of screen shots.