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
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:
OLEDB;"Provider=C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\sqlceoledb40.dll";"Data Source=C:\SQLServerCE\MyDatabase.sdf";
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.