Connection String to access a SQL Server Compact Edition database

Topic

New Reply

I’m trying to retrieve data from a SQL Server CE database (version 4.0). I know how to do it with a regular SQL Server database, but am having a hard time modifying the connection string to get it to work with SQL Server CE.
What I have is an Excel reporting application, so it’s read-only access where the users make some filtering choices and I build the SQL Select clause in VBA.

What I’ve found is that the quickest way to retrieve the data is via a Query Table and using OLEDB with code such as the following:
With wksDest.QueryTables.Add(Connection:=sConnect, Destination:=rngDest, sql:=sSql)
.Name = “tmpQ”
.FieldNames = False
.AdjustColumnWidth = False
.PreserveFormatting = True
.Refresh BackgroundQuery:=False
End With

where the sConnect variable has been set to:
OLEDB;Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDatabase;User ID=MyName;Password=MyPassword

My latest (sad!) attempt is as follows:
[TABLE=”width: 215″]
[TR]
[TD]OLEDB;”Provider=C:Program Files (x86)Microsoft SQL Server Compact Editionv4.0sqlceoledb40.dll”;”Data Source=C:SQLServerCEMyDatabase.sdf”;[/TD]
[/TR]
[/TABLE]

but that seems to prompt me for an ODBC datasource. Until I fully qualified the .DLL it just gave me an error.

So the main question is, can anyone help me with the connection string? Also, though, if anybody has any thoughts/comments regarding retrieving the data via a QueryTable, then that would be appreciated too.

TIA

Reply | Quote

Replies

Maybe the info here will help: http://www.connectionstrings.com/sql-server-2005-ce

Never used SQL Server Compact Edition, so I am afraid I can’t reply to the question on QueryTables.

Reply | Quote

Thanks. From all my googling, that’s the site I’ve looked at the most, but it hasn’t helped I’m afraid.
The QueryTable question transcends the SQL Server version. In my initial attempts (using grown up SQL Server), I tried using ADODB (I think that was it), but found it slower than pulling the data back into a QueryTable. I was just curious if others had found the same thing.
Thanks for the reply.

Reply | Quote

You are talking about getting data from an external database to Excel, is that it? I must say I never did it, if that is the case.
I have used ADO and ADO.NET with other Microsoft data sources. Probably Query Tables rely on ADO on a lower level, ADO having been an almost universal data access layer to all things Microsoft at a time, but I can say for sure.

About the connection string, I have seen others describe usage of strings similar to those available there. Probably you also need to make sure the drivers are installed, which they probably are, but never having used the thing, I am just trying to cover all bases. Here is an example of someone using ADO to get the data from SQL Server Compact: http://xldennis.wordpress.com/2010/08/30/using-sql-server-compact-edition-database-with-excel/

I am sorry if this doesn’t help.

Reply | Quote