none
How to Create a Working DSNless SQL Server 2012 Connection String?

    Question

  • I wrote sample code to change SQL Server instance names and DSN to DSNless connection strings that worked with Microsoft Access versions since 2003. I recently changed the code to specify SQL Native Client 11.0 for a local SQL Server 2012 SQL Express instance with an Access 2010 front end.

    Creating operational ODBC DSNs with SQL Native Client 11.0 and this connection string has been no problem:

    ODBC;DSN=PacificParkPlaza;Description=HOADatabase;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=PacificParkPlaza;;TABLE=dbo.Owners

    However, executing what I believe to be the proper DSNless connection string:

    ODBC;DRIVER={SQL Native Client 11.0};SERVER=OL-WIN7PRO23\SQLEXPRESS;DATABASE=PacificParkPlaza;TABLE=dbo.Owners;Trusted_Connection=YES

    fails with the following malformed error message when invoking the TableDefs.Append tdfNew method:

    Any ideas why this problem occurs? The Access front end and SQL Express instance are running on the same Win7Pro client.

    Thanks in advance,

    --rj


    Microsoft Access 2010 In Depth (QUE Publishing)
    OakLeaf Blog
    Access 2010 Blog
    Amazon Author Blog





    Sunday, March 10, 2013 11:26 PM

Answers

  • Hi, Dan,

    Thanks for the tip. Earlier sqlncli versions accept {SQL Native Client ##.#} the requirement for "Server" appears to have arrived (unannounced) with v11.0.

    I missed the change in the ConnectionStrings site's entry for v11.0.

    After that fix, I discovered that adding TABLE=dbo.TableName; to the connection string throws a "missing field" error. You must use tdfToAppend.SourceTableName = "dbo.TableName".

    I intend to post a description of these issues on my Access In Depth blog shortly.

    Cheers and thanks again,

    --rj


    Microsoft Access 2010 In Depth (QUE Publishing)
    OakLeaf Blog
    Access 2010 Blog
    Amazon Author Blog


    Monday, March 11, 2013 8:04 PM

All replies

  • I believe the driver name should be "SQL Server Native Client 11.0" (missing "Server" in the driver name).


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, March 10, 2013 11:55 PM
    Moderator
  • Hi, Dan,

    Thanks for the tip. Earlier sqlncli versions accept {SQL Native Client ##.#} the requirement for "Server" appears to have arrived (unannounced) with v11.0.

    I missed the change in the ConnectionStrings site's entry for v11.0.

    After that fix, I discovered that adding TABLE=dbo.TableName; to the connection string throws a "missing field" error. You must use tdfToAppend.SourceTableName = "dbo.TableName".

    I intend to post a description of these issues on my Access In Depth blog shortly.

    Cheers and thanks again,

    --rj


    Microsoft Access 2010 In Depth (QUE Publishing)
    OakLeaf Blog
    Access 2010 Blog
    Amazon Author Blog


    Monday, March 11, 2013 8:04 PM
  • I have not seen your "missing field" error when linking.  I know I have seen SQL tables with primary keys that for some reason Access will not recognize, when manually linking the screen pops up to select a primary key.  My solution, was to manually create the index on the TableDef.

    Here are all the current MS SQL Server Driver version connection strings:
    SQL 2012 Server - {SQL Server Native Client 11.0}
    SQL 2008 Server - {SQL Server Native Client 10.0}
    SQL 2005 Server - {SQL Native Client}
    SQL 2000/7 Server - {SQL Server}


    Jack Stockton

    Monday, March 18, 2013 9:30 PM