Connect Access to SQL Server

Recall the halcyon days of youth when alphabet soup was your fun meal. Keep these happy thoughts in the back of your mind as we take a few tastes of the database version of alphabet soup. The following sections spell out the basics of getting to a database with connection strings and using a database programming interface in your Access VBA code.

Components of data access

In this Article

Using ODBC driver or OLE DB provider

Programmatically interface to SQL Server from Access

Summary of ODBC driver versions

Summary of OLE DB provider versions

ODBC keyword summary

OLE DB keyword summary

Using ODBC driver or OLE DB provider

Connection strings have been around a long time. You can define a formatted connection string either in the Access user interface or in VBA code. A connection string (whether ODBC or OLE DB) passes information directly to the database, such as server location, database name, type of security, and other useful options. For example:

ODBC;DRIVER=SQL Server;SERVER="MyServer";DATABASE="MyHRdb";TRUSTED_CONNECTION=Yes
Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyHRdb; Integrated Security=SSPI;

At first, there was SQL Server Native Client (SNAC) a stand-alone library that contained ODBC and OLEDB technologies and is still available for SQL Server versions 2005 through 2012. Many legacy applications used SNAC and it is still supported for backward compatibility, but we don't recommend using it for new application development. You should use later individual, downloadable versions of the ODBC drivers.

ODBC drivers

Open Database Connectivity (ODBC) is a protocol that you use to connect an Access database to an external data source such as Microsoft SQL Server. Typically, you use file data sources (also called DSN files) to add a connection string, in which case, the FILEDSN keyword is used on the connection string, or stored in the registry, in which case, the DSN keyword is used. Alternatively, you can use VBA to set these properties using a "DSN-less" connection string.

Over the years, ODBC drivers have shipped in three phases:

  • Prior to 2005, ODBC drivers shipped with Windows Data Access Components (WDAC), which originally was called Microsoft Data Access Components (MDAC). These components still ship with Windows for backward compatibility. For more information, see Microsoft or Windows Data Access Components.

  • ODBC drivers shipped with SNAC for SQL Server 2005 through SQL Server 2012.

  • After SQL Server 2012, ODBC Drivers have shipped individually and contain support for new SQL Server features.

For new development, avoid using ODBC drivers from the first two phases, and use ODBC drivers from the third phase.

OLE DB providers

Object Linking and Embedding, Database (OLE DB) is a more recent protocol that you use to connect an Access database to an external data source such as Microsoft SQL Server. OLE DB does not require a DSN and also provides full access to ODBC data sources and ODBC drivers.

Tip    Typically, you use the Data Link Properties dialog box to add an OLE DB connection string. Although there is no way from Access to open the Data Link Properties dialog box, in Windows Explorer, you can create an empty .txt file, change the file type to .udl, and then double-click the file. After you create a connection string, change the file type back to .txt.

Over the years, OLE DB providers have shipped in three phases:

  • Prior to 2005, OLE DB providers shipped with Windows Data Access Components (WDAC), which originally was called Microsoft Data Access Components (MDAC).

  • OLE DB providers shipped with SQL Server 2005 through SQL Server 2017. It was deprecated in 2011.

  • In 2017, the SQL Server OLE DB provider was un-deprecated.

The currently recommended version for new solution development is OLE DB Driver 18 for SQL Server.

How to optimize performance with an ODBC connection string

To optimize performance, minimize network traffic, and reduce multi-user access to the SQL Server database, use as few connection strings as possible by sharing connection strings over multiple record sets. Although Ace simply passes on a connection string to the server, it does understand and use the following keywords: DSN, DATABASE, UID, PWD, and DRIVER to help minimize client/server communication.

Note    If an ODBC connection to an external data source is lost, Access automatically tries to reconnect to it. If the retry is successful, you can continue working. If the retry fails, you can still work with objects that don’t rely on the connection. To reconnect, close and re-open Access.

Recommendations when using both ODBC and OLE DB

Avoid mixing connection string and database access technologies. Use an ODBC connection string for DAO. Use an OLE DB connection string for ADO. If your application contains VBA code that uses both DAO and ADO, then use the ODBC driver for DAO and the OLE DB provider for ADO. Strive to get the latest feature and supports for both ODBC and OLEDB respectively.

ODBC uses the term driver and OLE DB uses the term provider. The terms describe the same type of software component but are not interchangeable in connection string syntax. Use the correct value as documented.

Top of Page

Programmatically interface to SQL Server from Access

There are two main ways to programmatically interface to an SQL Server database from Access.

DAO

A data access object (DAO) provides an abstract interface to a database. Microsoft Data Access Objects (DAO) is the native programming object model that lets you get at the heart of Access and SQL Server to create, delete, modify, and list objects, tables, fields, indexes, relations, queries, properties, and external databases.

For more information, see Microsoft Data Access Objects reference.

ADO

ActiveX Data Objects (ADO) provides a high-level programming model and is available in Access by a reference to a third party library. ADO is straightforward to learn and enables client applications to access and manipulate data from a variety of sources, including Access and SQL Server. Its primary benefits are ease of use, high speed, low memory overhead, and a small disk footprint. ADO also supports key features for building and Web-based applications.

For more information, see Microsoft ActiveX Data Objects reference and Microsoft ActiveX Data Objects (ADO).

Which one should you use?

In an Access solution that uses VBA code, you can use DAO, ADO or both as your database interface technology. DAO continues to be the default in Access. For example, all forms and reports and Access queries use DAO. But when you migrate to SQL Server, consider using ADO to make your solution more efficient. Here are general guidelines to help you decide when to use DAO or ADO.

Use DAO when you want to:

  • Create a read/write, bound form without using VBA.

  • Query local tables.

  • Download data into temporary tables.

  • Use pass-through queries as data sources for reports or forms in read-only mode.

  • Define and use a TableDef or Querydef object in VBA.

Use ADO when you want to:

  • Leverage extra ways to optimize, such as performing asynchronous operations.

  • Run DDL and DML pass-through queries.

  • Get to SQL Server data directly through recordsets in VBA.

  • Write simpler code for certain tasks, such as streaming of Blobs.

  • Call a stored procedure directly, with parameters, using a command object in VBA.

Top of Page

Summary of ODBC driver versions

The following table summarize important information about ODBC driver versions, download locations, and feature support. Make sure you use the correct bit version (64-bit or 32-bit) of the driver based on Windows and not Office. If you are running 32-bit Access on 64-bit Windows, install 64-bit drivers, which includes the 32-bit components needed for Access.

For more information, see Using Connection String Keywords with SQL Server Native Client, Release Notes for ODBC to SQL Server on Windows (V17), and Features of the Microsoft ODBC Driver for SQL Server on Windows (V13, 11).

ODBC Drivers

Version

Download

New features

ODBC Drivers 17.0 to 17.3

SQL Server 2017

Download

ODBC Driver 17.3

Using Azure Active Directory with the ODBC Driver

Limitations of the ODBC driver when using Always Encrypted

Using XA Transactions

ODBC Driver 17.2

Using Always Encrypted with the ODBC Driver for SQL Server

Data Classification

UTF-8 server encoding Collation and Unicode Support

ODBC Driver 17.1

Using Always Encrypted with the ODBC Driver for SQL Server

ODBC Driver 17.0

Always Encrypted

UseFMTONLY    To use legacy metadata in special cases requiring temp tables. See Release Notes for ODBC to SQL Server on Windows

Differences when using Managed Instance (ODBC version 17)

ODBC Driver 13.1

SQL Server 2016 SP1, SQL Azure

Download

Always Encrypted

Azure Active Directory

AlwaysOn Availability Groups

Driver Aware Connection Pooling in the ODBC Driver for SQL Server

ODBC Driver 13.0

SQL Server 2016

Download

Internationalized Domain Name (IDN)

ODBC Driver 11.0

SQL Server 2005 to 2012

Download

Driver-Aware Connection Pooling

Connection Resiliency in the Windows ODBC Driver

Asynchronous Execution

Service Principal Names (SPNs) in Client Connections (ODBC)

Features of the Microsoft ODBC Driver for SQL Server on Windows

Top of Page

Summary of OLE DB provider versions

The following table summarize important information about OLE DB providers versions, download locations, and feature support. Make sure you use the correct bit version (64-bit or 32-bit) of the driver based on Windows and not Office. If you are running 32-bit Access on 64-bit Windows, install 64-bit drivers, which includes the 32-bit components needed for Access.

For more information, see Using Connection String Keywords with SQL Server Native Client.

OLE DB Provider

Version

Download

New features

OLE DB Driver 18.2.1

(MSOLEDBSQL)

SQL Server 2017

Download

See OLE DB Driver for SQL Server Feature and Release notes for the Microsoft OLE DB Driver, for SQL Server

SQL Server Native Client (SQLNCLI)

SQL Server 2005 to 2012

Deprecated, do not use

OLE DB Driver (SQLOLEDB)

Deprecated, do not use

Top of Page

ODBC keyword summary

The following table summarizes the ODBC keywords recognized by SQL Server and their purpose. Only a subset are recognized by Access.

Keyword

Description

Addr

The network address of the server running an instance of SQL Server.

AnsiNPW

Specifies usage of ANSI-defined behaviors for handling NULL comparisons, character data padding, warnings, and NULL concatenation (Yes or No).

APP

Name of the application calling SQLDriverConnect.

ApplicationIntent

Declares the application workload type when connecting to a server (ReadOnly or ReadWrite).

AttachDBFileName

Name of the primary file of an attachable database.

AutoTranslate

Specifies whether ANSI character strings are sent between the client or server or translated to Unicode (Yes or No).

Database

The database name. Description The purpose of the connection. Driver Name of the driver as returned by SQLDrivers.

DSN

Name of an existing ODBC user or system data source. Encrypt Specifies whether data should be encrypted before sending it over the network (Yes or No).

Failover_Partner

Name of the failover partner server to be used if a connection cannot be made to the primary server.

FailoverPartnerSPN

The SPN for the failover partner.

Fallback

Deprecated keyword.

FileDSN

Name of an existing ODBC file data source. Language The SQL Server language.

MARS_Connection

Specifies multiple active result sets (MARS) on the connection for SQL Server 2005 (9.x) or later (Yes or No).

MultiSubnetFailover

Specifies whether to connect to the availability group listener of a SQL Server availability group or a Failover Cluster Instance (Yes or No).

Net

dbnmpntw indicates named pipes and dbmssocn indicates TCP/IP.

PWD

The SQL Server login password.

QueryLog_On

Specifies the logging of long-running queries (Yes or No).

QueryLogFile

Full path and file name of a file to use to log data on long-running queries.

QueryLogTime

Digit character string specifying the threshold (in milliseconds) for logging long-running queries.

QuotedId

Specifies whether SQL Server uses the ISO rules regarding the use of quotation marks in SQL statements (Yes or No).

Regional

Specifies whether the SQL Server Native Client ODBC driver uses client settings when converting currency, date, or time data to character data (Yes or No).

SaveFile

Name of an ODBC data source file into which the attributes of the current connection are saved if the connection is successful.

Server

The name of a SQL Server instance: Server on the network, an IP address, or Configuration Manager alias.

ServerSPN

The SPN for the server.

StatsLog_On

Enables the capture of SQL Server Native Client ODBC driver performance data.

StatsLogFile

Full path and file name of a file used to record SQL Server Native Client ODBC driver performance statistics.

Trusted_Connection

Specifies whether Windows Authentication Mode or SQL Server username or password is used for login validation (Yes or No).

TrustServerCertificate

When used with Encrypt, enables encryption using a self-signed server certificate.

UID

The SQL Server login name.

UseProcForPrepare

Deprecated keyword.

WSID

The workstation identifier, the network name of the computer on which the application resides.

Top of Page

OLE DB keyword summary

The following table summarizes OLE DB keywords recognized by SQL Server and their purpose. Only a subset are recognized by Access.

Keyword

Description

Addr

The network address of the server running an instance of SQL Server.

APP

The string identifying the application.

ApplicationIntent

Declares the application workload type when connecting to a server (ReadOnly or ReadWrite).

AttachDBFileName

Name of the primary file of an attachable database.

AutoTranslate

Configures OEM/ANSI character translation (True or False).

Connect Timeout

The amount of time (in seconds) to wait for data source initialization to complete.

Current Language

The SQL Server language name.

Data Source

The name of an instance of SQL Server in the organization.

Database

The database name.

DataTypeCompatibility

A number indicating the mode of data type handling that will be used.

Encrypt

Specifies whether data should be encrypted before sending it over the network (Yes or No).

FailoverPartner

The name of the failover server used for database mirroring.

FailoverPartnerSPN

The SPN for the failover partner.

Initial Catalog

The database name.

Initial File Name

The name of the primary file (include the full path name) of an attachable database.

Integrated Security

Used for Windows Authentication (SSPI).

Language

The SQL Server language.

MarsConn

Specifies multiple active result sets (MARS) on the connection for SQL Server 2005 (9.x) or later (Yes or No).

Net

The network library used to establish a connection to an instance of SQL Server in the organization.

Network Address

The network address of an instance of SQL Server in the organization.

PacketSize

Network packet size. The default is 4096.

Persist Security Info

Specifies whether persist security is enabled (True or False).

PersistSensitive

Specifies whether persist sensitive is enabled (True or False).

Provider

For SQL Server Native Client, this should be SQLNCLI11.

PWD

The SQL Server login password.

Server

The name of a SQL Server instance: Server on the network, an IP address, or Configuration Manager alias.

ServerSPN

The SPN for the server.

Timeout

The amount of time (in seconds) to wait for data source initialization to complete.

Trusted_Connection

Specifies whether Windows Authentication Mode or SQL Server username or password is used for login validation (Yes or No).

TrustServerCertificate

Specifies whether a server certificate is validated (True or False).

UID

The SQL Server login name.

Use Encryption for Data

Specifies whether data should be encrypted before sending it over the network (True or False).

UseProcForPrepare

Deprecated keyword.

WSID

The workstation identifier, the network name of the computer on which the application resides.

Top of Page

See Also

Administer ODBC data sources

Manage linked tables

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×