OLEDB Connection String to access a SQL Server Compact Edition (v4.0) database


  • I'm trying to retrieve data from a SQL Server Compact Edition 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.

    I've found that the quickest way to retrieve the data is using a Query Table and 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:

    OLEDB;"Provider=C:\Program Files\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.


    2012年5月30日 下午 11:26