ADO - Connection String in ADO

Connection String in ADO

A Connection String is a single string of settings that tells ADO how to connect to a data source.
It contains information like:

  • Provider (OLE DB provider or driver)

  • Server or Data Source (where the database lives)

  • Database name (initial catalog)

  • Authentication details (username/password or integrated security)

  • Optional settings (timeout, encryption, pooling, etc.)

Basic Syntax

Provider=ProviderName;Data Source=ServerName;Initial Catalog=DatabaseName;User ID=Username;Password=Password;

Then you use it in ADO like this:

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDB;User Id=sa;Password=pass;"

Key Components

  1. Provider – The OLE DB provider.

    • For SQL Server: SQLOLEDB (older), SQLNCLI (SQL Native Client), or MSOLEDBSQL (newer).

    • For Access: Microsoft.Jet.OLEDB.4.0 (MDB) or Microsoft.ACE.OLEDB.12.0 (ACCDB).

  2. Data Source – The server name, network name, or file path.

    • Example: localhost, MyServer\SQLEXPRESS, or C:\Data\MyDB.accdb.

  3. Initial Catalog – The database name (SQL Server only).

  4. User ID / Password – Credentials for database authentication.

  5. Integrated Security – SSPI or True to use Windows Authentication (no username/password).

  6. Other options – Timeout, pooling, encryption, etc.

Examples by Database

SQL Server (SQL Authentication)

Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=SalesDB;User Id=sa;Password=MyPass;

SQL Server (Windows Authentication)

Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=SalesDB;Integrated Security=SSPI;

Access (MDB file, older)

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyData\MyDB.mdb;

Access (ACCDB file, newer Office)

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyData\MyDB.accdb;

Oracle

Provider=OraOLEDB.Oracle;Data Source=ORCL;User Id=myUser;Password=myPass;

MySQL (via OLE DB / ODBC)

Provider=MSDASQL;Driver={MySQL ODBC 8.0 Driver};Server=localhost;Database=MyDB;User=myUser;Password=myPass;

Advanced Options

  • Connection Timeout=30 → Seconds before giving up on connection.

  • Persist Security Info=False → Doesn’t return credentials after connection opens.

  • Pooling=True/False → Enables/disables connection pooling.

  • Encrypt=True → Encrypts communication (if supported).

Example:

Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=SalesDB;User Id=sa;Password=MyPass;Connection Timeout=15;Encrypt=True;

Usage in ADO

Dim conn, rs
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=SalesDB;User Id=sa;Password=MyPass;"
conn.Open

Set rs = conn.Execute("SELECT * FROM Customers")
Do Until rs.EOF
    Response.Write rs("CustomerName") & "<br>"
    rs.MoveNext
Loop

rs.Close
conn.Close

summary:

  • A connection string is the recipe that tells ADO how to reach the database.

  • The exact syntax depends on the database and provider.

  • You can tweak it with security, pooling, and performance settings.