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.

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

22 comments:

Simon said...

Erik,

Do you happen to have any code to make things go the other way? Specifically not using .Net? I am currently trying to export data from Microsoft Access 2003 to a SQL Compact database. I have got the basic insert working, but have been unable to insert images? I keep on getting the error "Not enough storage to complete this operation". Any ideas how this might be accomplished?

Thanks Simon

ErikEJ said...

I will look into it... I don't have code for BLOB inserts just now.

ErikEJ said...

Simon, have a look here:
http://erikej.blogspot.com/2008/06/inserting-images-from-vba.html

Unknown said...

Hey Erik... I know this post has been here a while. It is close to what I am trying to do EXCEPT I'm hoping to connect an SQL CE file (main.dat) to an Access DB so I can run some vba code (extract id3 tag data from an mp3 file and update a field in the CE). Getting: Method 'Open' of object '_Connection' failed at the pconn.open line. Any help is much appreciated.

ErikEJ said...

Could you share your code? - Suggest you post it on the MSDN forum to the benefit of all: http://social.msdn.microsoft.com/Forums/en-US/sqlce/threads (or e-mail me, see contact info)

ErikEJ said...

Information on creating a database in VBA here: http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/2e104cc5-1ae5-4d52-b8fe-482a84c48a76

Nate Coovert said...

Erik, thanks for this, it worked great. I was wondering if this can be used to connect to a .mdf file made from sql server 2008. If so, what reference and connection strings would need to be changed? Thank you.

ErikEJ said...

Nate: There are built-in Data tools in Excel + PowerPivot that can pull data easily from SQL Server, why go down the code road?

Nate Coovert said...

Eric, Since the code that you posted works well with my project, I thought that I'd see if it could be done with sql .mdf, I realize now that the file is locked when in use so the "code road" doesn't appear to be an option for me afterall. I took your advice and set up a local ODBC connection using the SQL Native Client and that appears to do the trick for Excel and Access. I haven't yet been able to get a remote (LAN or WAN) connection to the database to work, and after searching for the error from the server log, it appears that it's an issue that others are having when on Win7 64bit machines. Anyhow, thanks again for your help.
-Nate

steve said...

Erik, when i run the code I get a Run Time Error '3706' Application-defined or object-defined error.

ErikEJ said...

Steve: And you have the required software and references in place? If yes, please send me a repro.

guypb said...

Hi Erik, any idea what I need to do to get this to work with 64-bit Excel? It's worked fine with 32-bit!

ErikEJ said...

Guypb: Do you have the x64 SQL Compact runtime installed?

Unknown said...

Hi Erik,

Is it possible to go the other way, eg. Save As .sdf or equivalent code?

I would like my users to download sdf into Excel (your code here works great), have them modify it and save back into the same or a different .sdf database.

Cheers Col

ErikEJ said...

Colin: Yes, but it would be quite hard from Excel... But it is possible to create a new sdf file from VBA, and then of course you can do CREATE TABLE and INSERTs as needed, but it would require quite a lot of code.

ErikEJ said...
This comment has been removed by the author.
Unknown said...

Thanks Erik. Would Access be any easier? If not, my application that uses the sdf file can import csv files so I would look at doing the edits in Excel, and saving the individual tables as csv files and importing those.

ErikEJ said...

Colin: this is not a good forum for tech support, suggest you ask on the MSDN forum

Unknown said...

Hi,

Great code. I added the following so It would re-size data automatically

Worksheets(ws.Name).Columns("A:Z").AutoFit


Between

Wend
Worksheets(ws.Name).Columns("A:Z").AutoFit
pRS.MoveNext

Unknown said...

Hi,
Do you know of any code to pull data from an SQL CE file into Access? I'm assuming it would be similar to your excel code

ErikEJ said...

@gary: No, I do not, but it would be very similar, yes

Unknown said...

Thanks you! Very much!
Error 3706 - yo need to have Microsoft sql server compact and verify the compatibility with the version on the code (may be update the code with the correct version that you have on machine).
Now I'm looking for any way to update the file with excel.