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
