When working with databases, it’s essential to understand the different types of connection strings available and how they function. Let’s explore the differences between an Access external data connection string, an ODBC connection string, and an OLEDB connection string.

Because they all use the same general format where we have a key and a value pair delimited by a semicolon, it can appear to be the same thing when in fact, what might be valid Access external database connection would be an invalid ODBC connection string or worse, a valid ODBC connection string has the appearance of being valid in OLEDB but not working in the way you would think it should. It also does not help that because they are simply strings and not a discrete data type, it’s possible to pass the string around willy-nilly and things “seems” to work, or does it?

Compounding the confusion: the Clippy lurks in ADO

We often see several instances of Access VBA code that will do something similar to the following:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ConnectionString = “DRIVER=ODBC Driver 18 for SQL Server;SERVER=(local);DATABASE=master;Trusted_Connection=yes”
cn.Open

And it works! So, ADO can do ODBC, right? Well…. Not exactly. But to explain this, we need to dig up what makes an ODBC connection string and what makes an OLEDB connection string. But before we do, let’s clarify the Access external data connection string first.

Access External Data Connection String

An Access external data connection string is used by the Access database engine to connect to external data sources. Access database engine supports several different kind of data sources. Here are few possible samples:

  • Text File: Text;DATABASE=C:\Something
  • Excel File: Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Something\Something.xlsx
  • ODBC Data Source: ODBC;DRIVER=ODBC Driver 18 for SQL Server;SERVER=(local);DATABASE=master;

It is important to note that every Access external data connection string has a specifier at the start which communicates to Access database engine which driver it should use; Text for Text files, Excel 12.0 Xml for Excel xlsx spreadsheet (note: there are more than one Excel drivers), and ODBC for ODBC data source.
One key point to note is that the ODBC; at the start of an Access external data connection string is not technically part of the ODBC connection string but embeds the ODBC connection string as a part of Access external data connection string. Therefore using the example above, the actual ODBC connection string is:

DRIVER=ODBC Driver 18 for SQL Server;SERVER=(local);DATABASE=master;

Subtle, isn’t it?

Were we to pass the original Access external data connection string to the ODBC API in some other way, it would be an invalid ODBC connection string because ODBC; is not a valid key-value pair as mandated by the ODBC connection string, which is explained in details here.

ODBC Connection String

ODBC (Open Database Connectivity) connection strings are used to connect to a variety of data sources using a standard interface. ODBC connection strings have a specific format and may include keywords such as DSN, UID, and PWD.
Here are the generic keywords that are recognized by ODBC itself:

  • Driver
  • DSN
  • FileDSN
  • PWD
  • SaveFile
  • UID

…and that’s it! The rest are driver-specific! So yes, even common key-value pairs you use like Server, Database all are not recognized by ODBC itself but rather the specific ODBC driver that you are using and it’s entirely up to whoever wrote the ODBC driver to recognize additional keywords. Therefore, you’d need to consult the documentation of the ODBC driver’s author to determine what is recognized by the driver. For example, you’d look at the list here for SQL Server and the list for MySQL there.

You can give it nonsense and it doesn’t care!

That is important because it is legal to write an ODBC connection string:

DRIVER=ODBC Driver 18 for SQL Server;SERVER=(local);DATABASE=master;Trusted_Connection=yes;GiveMeAPonyOnOpen=yes;

And it’ll work! It opens! But no pony. 🙁

This behavior means that it’s possible to build a seemingly valid connection string that may not have the desired effect, which is why on occasions we run into some Access VBA code that sets an OLEDB keyword in an ODBC connection string and it just “works” in the same way it works to hitching a horse to front of a self-propelled tractor and say to the horse “giddyap!” while driving the tractor.

OLEDB Connection String

OLEDB (Object Linking and Embedding Database) connection strings are used to connect to data sources using the OLEDB interface. It uses a format that looks very similar to the ODBC connection strings but both being a string does not mean they can be used interchangeably. The format for OLEDB Connection string is detailed here. Furthermore, OLEDB connection strings has a different set of keywords that is recognized by OLEDB itself:

  • Provider
  • Cache Authentication
  • Encrypt Password
  • Integrated Security
  • Mask Password
  • Password
  • Persist Encrypted
  • Persist Security Info
  • User ID
  • Asynchronous Processing
  • Bind Flags
  • Initial Catalog
  • Data Source
  • General Timeout
  • Window Handle
  • Impersonation Level
  • Locale Identifier
  • Location
  • Lock Owner
  • Mode
  • OLE DB Services
  • Prompt
  • Protection Level
  • Extended Properties
  • Connect Timeout

Wow, lot of keywords and odds are that we’ve never bothered to use most of them. Generally, just using Provider, Data Source, Initial Catalog, Persist Security Info and maybe User ID, Password or Integrated Security. This looks nothing like what we’d see in an ODBC connection string.

If we go and look at the SQL Server’s keywords used for OLEDB driver here and compare it with the ODBC keywords, we’ll see several keywords overlapping in both. Microsoft reasoned that it was easier to use the same keywords in both ODBC and OLEDB, which is why it can appear to work in both contexts, which is why this is a valid OLEDB connection string:

Provider=MSOLEDBSQL;Server=(local);Database=master;Trusted_Connection=yes;

Note that the only difference here is we swapped out the Driver parameter with the Provider parameter. But since the OLEDB driver accepts the Server, Database, and Trusted_Connection as used by ODBC driver it just works™. However, this is a more OLEDB-y connection string:

Provider=MSOLEDBSQL;Data Source=(local);Initial Catalog=master;Integrated Security=SSPI;

Both are functionally equivalent, but only for this specific provider. If you were to use different providers, the Server, Database or more likely, Trusted_Connection keywords might not be recognized by the other OLEDB providers. Using Data Source and Initial Catalog parameters would avoid the need to customize the connection string when you want to keep things generic in the scenarios where you want the user to be able to define a different data source and provide only the minimal settings.

Ferreting out the Clippy in ADO

With the difference among connection strings clarified, let’s go back to the original ADO code:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ConnectionString = “DRIVER=ODBC Driver 18 for SQL Server;SERVER=(local);DATABASE=master;Trusted_Connection=yes”
cn.Open

First, let’s have a quick look at the Provider property of the ADODB.Connection object:

If no provider is specified, the property will default to MSDASQL (Microsoft OLE DB Provider for ODBC).

The connection string above didn’t specify the Provider parameter nor did we set the Provider property on the cn object. Therefore, it defaulted to the OLEDB Provider for ODBC.

Note: I should note that MS-OLEDBSTR seems to agree except that it adds that it behaves differently on 64-bit systems but my testing does not bear this out; it’s still defaulting to MSDASQL.

But how would we control the OLEDB connection string that we provide to the OLEDB provider for ODBC? If we start adding OLEDB keywords, it will be used by the OLEDB layer, not by the ODBC layer. Let’s do some sleuthing. Using the cn object, we’ll open it and ask it what the connection string is:

?cn.ConnectionString
Provider=MSDASQL.1;

Hmm… the rest of connection string simply disappeared! Maybe because I didn’t specify Persist Security Info. Let’s modify:

cn.ConnectionString = “Persist Security Info=true;DRIVER=ODBC Driver 18 for SQL Server;SERVER=(local);DATABASE=master;Trusted_Connection=yes”

Did you see what I’m doing here? I’m now putting in OLEDB keywords in an ODBC connection string? Is that kosher? Let’s open it and see what the output is.

?cn.ConnectionString
Provider=MSDASQL.1;Persist Security Info=True;Extended Properties="DRIVER=ODBC Driver 17 for SQL Server;SERVER=(local);UID=;Trusted_Connection=yes;APP=Microsoft Office;WSID=MyComputer;DATABASE=master;"

Sooooo…. The cake is a lie. Clippy ate it and had it. Bad, bad, bad Clippy!
Just one more test. Let’s remove the Persist Security Info=true; from our connection string and open it and do an extra query:

?cn.ConnectionString
Provider=MSDASQL.1;
?cn.Properties("Extended Properties").Value
DRIVER=ODBC Driver 17 for SQL Server;SERVER=(local);UID=;Trusted_Connection=yes;APP=Microsoft Office;WSID=MyComputer;DATABASE=master;

Thus, this demonstrates that when we stuff an ODBC connection string in an ADODB.Connection object’s ConnectionString property and try to open it, it will just default to using the OLEDB Provider for ODBC which in turn stuff it in the Extended Properties property which is what is ultimately passed to the ODBC.

I hope that helps demystifies the magic that is going on with the ADO and clears up why keywords for ODBC seems to work in OLEDB (it’s just a feature of the specific provider, not something intrinsic to the technology) and why the ODBC; specifier seems to stick out like a sore thumb when it is not an Access external data connection.