ODBC Drivers and OLEDB Providers for connections from Access to SQL Server

support@ascassociates.biz , Sales Enquiries

ODBC Drivers and OLEDB Providers for connections from Access to SQL Server

This article provides a brief introduction to making choices about the drivers to use when connecting Access to SQL Server.

There are two methods by which you can connect Access to SQL Server, using ODBC and using ADO.

ODBC

ODBC is used for creating linked tables between Access and SQL Server, and can also be used in program code using “pass-through” queries. To get started linking Access to SQL Server, see our article Choosing between System and File DSN’s

When you make a connection using ODBC you need to choose a Driver. There are three possible choices of driver at the time of writing this article, and more will become available in the future with new versions of SQL Server.

SQL Server Version	Driver Name
2000	SQL Server
2005	SQL Server or SQL Native Client
2008	SQL Server or SQL Native Client or SQL Server Native Client 10.0

The latest driver is the last one in each list of choices, and each newer driver is backwards compatible with connecting to previous versions of SQL Server.

Example ODBC connection strings are shown below.

ODBC;DRIVER=SQL Server;SERVER=VISTAULTRA64\VISTAULTRA64_08;DATABASE=master;Trusted_Connection=Yes

ODBC;DRIVER=SQL Native Client;SERVER=VISTAULTRA64\VISTAULTRA64_08;DATABASE=master;Trusted_Connection=Yes

ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=VISTAULTRA64\VISTAULTRA64_08;DATABASE=master;Trusted_Connection=Yes

For an excellent resource on connection strings we recommend you look at: http://www.connectionstrings.com

Factors to consider when selecting a driver

If you want to use new features, such as the ability with SQL 2005 to return multiple record sets, then you need the newer driver.
If you are having problems with speed or other issues (some people have had performance issues connecting to SQL Server 2005 using the SQL Server driver) then switching to using a newer driver such as SQL Native Client can solve the problems.
Whilst all your desktops will have SQL Server driver installed, you will need to install the newer driver package on any machines which need to connect to the SQL Server (unless the machine has already had a product like SQL 2008 Express installed locally (see below).

Native Client Problems with Memo Fields

An Access Memo field can either be mapped to a SQL Server TEXT (older data type) or VARCHAR(MAX) field. Either of which can be used with the SQL Server driver. If you use either of the Native Client drivers then you will find that this does not work with VARCHAR(MAX). You will get the following error:-

[Microsoft][SQL Native Client] String Data, right truncation (#0)

So if you wish to use the native client drivers, you need to map your memo fields to TEXT data types (MUST has the option to set this mapping in the options: Server version and fields data types menu before you start migrating your data).

The disadvantages of using the data type TEXT are as follows:-

If using a .net application TEXT data types can not be passed in the VIEWSTATE, but VARCHAR(MAX) can.
You can not have variables of TEXT data type in SQL Server, which also means you can not pass this data type as a parameter to a stored procedure.

The SQL Server Native Client Redistributable Package

The link below contains links for downloading the FREE installation packages to install the newer SQL Server drivers on a workstation.

http://msdn.microsoft.com/en-us/data/aa937733.aspx

ADO and OLEDB providers

ADO is an alternative mechanism for connecting to SQL Server and is used in an Access ADP, it can also be used in a standard Access database. The question of using ODBC or ADO comes down to personal preferences; there are advantages and disadvantages with both technologies.

There are also different providers available for ADO.

SQL Server Version	Provider Name
2000	SQLOLEDB
2005	SQLOLEDB or SQLNCLI
2008	SQLOLEDB or SQLNCLI or SQLNCLI10

Examples of connection strings are shown below.

Provider=SQLOLEDB;Data Source=VISTAULTRA64\VISTAULTRA64_08;Initial Catalog=master;Integrated Security=SSPI;

Provider=SQLNCLI;Data Source=VISTAULTRA64\VISTAULTRA64_08;Initial Catalog=master;Integrated Security=SSPI;

Provider=SQLNCLI10;Data Source=VISTAULTRA64\VISTAULTRA64_08;Initial Catalog=master;Integrated Security=SSPI;

Testing a Server Connection

If you need to test a connection to SQL Server, then using a section of ADO code for the test has advantages over using ODBC code.

If you use ODBC and the connection fails then you will get a popup as shown below:-

In our tests there is a delay of around 30 seconds to display this (even when the connection timeout is set to 10 seconds), and it is not possible to stop the popup being displayed.

The advantage of using ADO for this test is that you do not get the popup, and in our tests when the connection timeout was set to 10 seconds it returned after 13 seconds.

So if you have an application that needs to test different connections then using ADO for the tests allows you to create a better user experience.

Sample ODBC code for testing a connection.

Dim db As Database
Dim rst As DAO.Recordset
Dim qdef As QueryDef
Set db = CurrentDb
Set qdef = db.CreateQueryDef("")
qdef.Connect = Me.txtConnectionString
qdef.ReturnsRecords = True
qdef.ODBCTimeout = 10
qdef.SQL = "SELECT @@VERSION"
Set rst = qdef.OpenRecordset()
Dim ver As String
ver = Nz(rst(0))
MsgBox ver, vbInformation, "Server Version"
rst.Close
qdef.Close
db.Close

Sample ADO code for testing a connection.

Dim conn As New ADODB.Connection
conn.ConnectionString = Me.txtConnectionString
conn.ConnectionTimeout = 10
conn.Open
Dim rst As New ADODB.Recordset
rst.ActiveConnection = conn
rst.Open ("SELECT @@VERSION")
Dim ver As String
ver = Nz(rst(0))
MsgBox ver, vbInformation, "Server Version"
rst.Close
conn.Close
Set rst = Nothing
Set conn = Nothing

Downloading Sample Code

The following link (click here) can be used to download sample code for testing connections using ADO/ODBC and different Drivers. The database contains a table called tblServer into which you can enter your server names, and a table called tblDriver containing details for the different drivers.

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz

support@ascassociates.biz , Sales Enquiries

sales@ascassociates.biz