Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Connection String to access a SQL Server Compact Edition database

    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:

    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

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,208
    Thanks
    129
    Thanked 1,145 Times in 1,054 Posts
    Maybe the info here will help: http://www.connectionstrings.com/sql-server-2005-ce

    N
    ever used SQL Server Compact Edition, so I am afraid I can't reply to the question on QueryTables.

  4. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post
    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.

  5. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,208
    Thanks
    129
    Thanked 1,145 Times in 1,054 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •