A connection string is a single line of text that tells your Access application how to find and connect to an external database. It specifies which driver to use, where the server is, which database to access, and how to authenticate.

If you are setting up linked tables for the first time, connection strings can look like gibberish. This guide breaks down each piece in plain language.

The Structure

Every ODBC connection string is a series of key=value pairs separated by semicolons:

DRIVER={driver name};SERVER=server address;DATABASE=database name;UID=username;PWD=password;

Each piece tells the ODBC layer one specific thing. Let’s go through them.

SQL Server Connection Strings

DRIVER={ODBC Driver 17 for SQL Server};SERVER=DBSERVER01;DATABASE=CompanyDB;Trusted_Connection=yes;
Part Meaning
DRIVER={ODBC Driver 17 for SQL Server} Use this specific ODBC driver. The curly braces are required. The driver name must match exactly what is installed.
SERVER=DBSERVER01 Connect to this server. Can be a server name, IP address, or servername\instancename for named instances.
DATABASE=CompanyDB Open this database on the server.
Trusted_Connection=yes Use the current Windows user’s credentials. No username or password needed.

SQL Server Authentication (Username/Password)

DRIVER={ODBC Driver 17 for SQL Server};SERVER=192.168.1.50;DATABASE=CompanyDB;UID=appuser;PWD=MyP@ssw0rd;
Part Meaning
UID=appuser Log in with this username.
PWD=MyP@ssw0rd Use this password. If the password contains ; or }, wrap the entire PWD value in curly braces: PWD={my;password}

Common Variations

Named instance: SERVER=DBSERVER01\SQLEXPRESS — connects to the SQLEXPRESS instance on DBSERVER01.

Non-standard port: SERVER=DBSERVER01,1434 — connects to port 1434 instead of the default 1433. Note: SQL Server uses a comma, not a colon.

Azure SQL: SERVER=myserver.database.windows.net;DATABASE=CompanyDB;UID=appuser@myserver;PWD=MyP@ssw0rd;Encrypt=yes;

MySQL Connection Strings

DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=192.168.1.50;DATABASE=companydb;UID=appuser;PWD=MyP@ssw0rd;PORT=3306;
Part Meaning
DRIVER={MySQL ODBC 8.0 Unicode Driver} The MySQL ODBC driver. “Unicode” vs “ANSI” — always use Unicode unless you have a specific reason not to.
PORT=3306 MySQL’s default port. Only needed if using a non-standard port.

Common Options

Character set: Add CHARSET=utf8mb4; to ensure proper Unicode support, including emoji and special characters.

Read timeout: Add READTIMEOUT=30; to set a 30-second timeout for queries. Prevents the application from hanging on slow queries.

MariaDB Connection Strings

MariaDB works with both the MySQL ODBC driver and its own MariaDB ODBC driver:

Using MySQL driver (most common):

DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=192.168.1.50;DATABASE=companydb;UID=appuser;PWD=MyP@ssw0rd;PORT=3306;

Using MariaDB driver:

DRIVER={MariaDB ODBC 3.1 Driver};SERVER=192.168.1.50;DATABASE=companydb;UID=appuser;PWD=MyP@ssw0rd;PORT=3306;

Both work. The MySQL driver is more widely used and tested with Access linked tables.

PostgreSQL Connection Strings

DRIVER={PostgreSQL Unicode};SERVER=192.168.1.50;DATABASE=companydb;UID=appuser;PWD=MyP@ssw0rd;PORT=5432;
Part Meaning
DRIVER={PostgreSQL Unicode} The psqlODBC driver. Must be installed separately.
PORT=5432 PostgreSQL’s default port.

Common Options

SSL mode: Add SSLMode=require; for encrypted connections.

Schema: PostgreSQL uses schemas within databases. If your tables are not in the default public schema, you may need to set the search path in the database configuration rather than in the connection string.

DSN vs. DSN-Less Connections

There are two ways to use a connection string in Access:

DSN (Data Source Name)

A DSN is a saved connection configuration stored in the Windows ODBC Data Source Administrator. You give it a name, and Access references that name:

ODBC;DSN=CompanyDatabase;

Advantage: Easy to set up through the GUI. Connection details are stored in one place.

Disadvantage: The DSN must be configured on every workstation that uses the Access front-end. If you have 20 users, you need to configure 20 DSNs. If the server address changes, you need to update 20 machines.

DSN-Less

The full connection string is stored in the linked table definition inside the Access file:

ODBC;DRIVER={ODBC Driver 17 for SQL Server};SERVER=DBSERVER01;DATABASE=CompanyDB;Trusted_Connection=yes;

Advantage: No per-workstation configuration needed. The connection information travels with the Access front-end file. Distribute the file and it works.

Disadvantage: If the server address or credentials change, you need to re-link the tables in the Access file.

Recommendation: Use DSN-less connections. They are easier to deploy and maintain.

Setting Up Linked Tables with a Connection String

In VBA, you can programmatically link a table using a DSN-less connection string:

Dim td As DAO.TableDef
Set td = CurrentDb.CreateTableDef("Customers")
td.Connect = "ODBC;DRIVER={ODBC Driver 17 for SQL Server};" & _
             "SERVER=DBSERVER01;DATABASE=CompanyDB;" & _
             "Trusted_Connection=yes;"
td.SourceTableName = "dbo.Customers"
CurrentDb.TableDefs.Append td

This creates a linked table called “Customers” in Access that points to the dbo.Customers table on SQL Server.

Troubleshooting Connection Strings

“Data source name not found”

The driver name in your connection string does not match any installed driver. Open the ODBC Data Source Administrator (search for “ODBC” in the Start menu) and check the Drivers tab for the exact installed driver name. Copy it exactly — spelling, spacing, and version number all matter.

Important: If you are running 32-bit Access, you need the 32-bit ODBC driver, configured in the 32-bit ODBC Administrator (C:\Windows\SysWOW64\odbcad32.exe). The 64-bit ODBC Administrator shows different drivers.

“Login failed”

Your UID/PWD is wrong, the user does not exist, or the user does not have permission to access the specified database. Test the credentials by connecting directly with the database’s management tool.

“Cannot open database”

The DATABASE value does not match an existing database on the server. Database names are usually case-sensitive on Linux-based servers (MySQL, PostgreSQL) even if they are not on Windows.

“TCP Provider: No connection could be made”

The server is not reachable. Check: Is the server running? Is the port open in the firewall? Is the SERVER value correct? Can you ping the server from the workstation?

Storing Connection Strings Securely

Connection strings with embedded passwords are a security concern. Anyone who opens the Access VBA editor or examines the linked table properties can see the password.

Options to reduce this risk: - Use Windows Authentication for SQL Server — no password in the connection string - Store the connection string in a local config file that is not shared, and read it with VBA at startup - Use a stored procedure for sensitive operations rather than direct table access - Limit the database user’s permissions so even if the password is exposed, the damage is limited

None of these is perfect. This is an inherent limitation of Access as a client application. Server-based applications handle credential management more securely.

Quick Reference

Database Default Port Recommended Driver
SQL Server 1433 ODBC Driver 17 for SQL Server
MySQL 3306 MySQL ODBC 8.0 Unicode Driver
MariaDB 3306 MySQL ODBC 8.0 Unicode Driver
PostgreSQL 5432 PostgreSQL Unicode