Sunday, March 17, 2013

Creating a Working DSNless ODBC Connection String with SQL Server Native Client v11.0 for SQL Server 2012

OakLeafLogoMVP100pxWhen completing a Microsoft Access 2010 Resident Information Management System (RIMS) project for the Home Owners Association of an East Bay high-rise condominium, the last step in preparing the runtime installation package was generating a DSNLess connection to SQL Server 2012 Express running under SQL Server 2012 Standard Edition. I used a System DSN with the SQL Native Client v11 driver during testing on my client’s new Active Directory network.

imageThe SQL Server chapters of my Special Edition Using Microsoft Access (since the Access 2005 edition) and Microsoft Access In Depth books for QUE Publishing have included VBA source code for a ChangeServer class module, which generates DSNLess connection strings from System Data Source Names (DSNs) for the classic SQL Server Driver (SQLSVR32.dll, v6.01.7601.17154 for SQL Server 2012). SQL Server Native Client (SQLNCLI11.dll, v2011.110.3000.0 for SQL Server 2012) is now the preferred ODBC driver for all client applications, including Microsoft Access.

imageI had customized the ChangeServer module for the SQL Native Client (SQLNCli) for applications using Access 2007 and 2010 front ends with SQL Server 2005, 2008 and 2008 R2. When I wrote code to change the SERVER clause of the connection string to DRIVER={SQL Native Client 11.0};, and ran the Client.accdr file with the new ODBC;DRIVER={SQL Native Client 11.0};SERVER=OL-WIN7PRO23\SQLEXPRESS;DATABASE=RIMS;TABLE=dbo.Owners; Trusted_Connection=YES connection string, I received the following malformed error message:

image

Previous SQL Server versions hadn’t objected to similar connection strings, so after unsuccessfully searching for the issue with Bing and Google, I started a How to Create a Working DSNless SQL Server 2012 Connection String? thread in the SQL Server Data Access forum.

Dan Guzman, SQL Server MVP, http://www.dbdelta.com offered the following suggestion:

image

Dan was right, but changing the the connection string to ODBC;DRIVER={SQL Native Client 11.0};SERVER=OL-WIN7PRO23\SQLEXPRESS;DATABASE=RIMS; TABLE=dbo.Owners;Trusted_Connection=YES threw a “missing field” error, which indicated the table was missing a primary key field. (It wasn’t.)

It turns out that specifying the table name with a TABLE= clause no longer works. You must execute a tdfToAppend.SourceTableName = "dbo.TableName" instruction.

imageYou can download the updated ChangeServer class module from my SkyDrive account by clicking here.