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:

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