登入
 
首頁文件庫學習園地下載支援社群論壇
IT 專業人員的技術資源 >
> 
> 
OLEDB Connection String to access a SQL Server Compact Edition (v4.0) database

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

  • 2012年5月30日 下午 11:26
     
     
    登入以投票
    0

    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.

    TIA

     

所有回覆

  • 2012年5月31日 上午 10:52
    版主
     
     
    登入以投票
    0
    use: Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0 (Not promising it will work from Excel)

    Please mark as answer, if this was it. Visit my SQL Server Compact blog

     
  • 2012年5月31日 下午 12:19
     
     
    登入以投票
    0

    Thanks for the reply, Erik.  That's actually one of the many(!) different things I've tried, but to no avail.

    But I'm realising now that the error message is "Initialization of the data source failed.", so maybe my problem is not with the Provider parameter(?).  Elsewhere I've seen people use double rather than single backslashes when specifying the Data Source, but I tried that and it made no difference.

     
  • 2012年5月31日 下午 12:31
    版主
     
     已答覆
    登入以投票
    0
    I think you will be better off with som VBA code: http://erikej.blogspot.com/2008/04/import-sql-compact-data-to-excel.html

    Please mark as answer, if this was it. Visit my SQL Server Compact blog