ADO Connection Strings

ActiveX Data Objects (ADO) is the standard data access library for classic ASP and VBScript. This reference covers connection strings for SQL Server, Access, and other data sources.

SQL Server via SQLOLEDB

' SQL Server connection using SQLOLEDB provider
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")

' Windows Authentication (trusted connection)
conn.Open "Provider=SQLOLEDB;" & _
    "Data Source=SQLSERVER01;" & _
    "Initial Catalog=FlamenetDB;" & _
    "Integrated Security=SSPI;"

' SQL Server Authentication
conn.Open "Provider=SQLOLEDB;" & _
    "Data Source=SQLSERVER01;" & _
    "Initial Catalog=FlamenetDB;" & _
    "User ID=appuser;" & _
    "Password=Str0ngP@ss!;"

' Named instance
conn.Open "Provider=SQLOLEDB;" & _
    "Data Source=SQLSERVER01\DEVINSTANCE;" & _
    "Initial Catalog=FlamenetDB;" & _
    "Integrated Security=SSPI;"

SQL Server via ODBC DSN-less

' ODBC driver connection (DSN-less)
conn.Open "Driver={SQL Server};" & _
    "Server=SQLSERVER01;" & _
    "Database=FlamenetDB;" & _
    "Uid=appuser;" & _
    "Pwd=Str0ngP@ss!;"

Microsoft Access (Jet 4.0)

' Access database via Jet provider
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Data\flamenet.mdb;"

' Password-protected Access database
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Data\flamenet.mdb;" & _
    "Jet OLEDB:Database Password=secret;"

Common ADO Pattern (Open, Query, Close)

<%
Dim conn, rs, strSQL
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=SQLSERVER01;" & _
    "Initial Catalog=FlamenetDB;Integrated Security=SSPI;"

strSQL = "SELECT EmployeeID, FirstName, LastName, Email " & _
    "FROM Employees WHERE DeptID = 3 ORDER BY LastName"
Set rs = conn.Execute(strSQL)

If Not rs.EOF Then
%>
<table border="1">
  <tr><th>ID</th><th>Name</th><th>Email</th></tr>
  <% Do While Not rs.EOF %>
  <tr>
    <td><%= rs("EmployeeID") %></td>
    <td><%= rs("FirstName") & " " & rs("LastName") %></td>
    <td><%= rs("Email") %></td>
  </tr>
  <% rs.MoveNext : Loop %>
</table>
<%
End If
rs.Close : Set rs = Nothing
conn.Close : Set conn = Nothing
%>

Connection Pooling

ADO/OLEDB connections are pooled automatically by the OLEDB provider. To maximize pooling efficiency:

• Use identical connection strings across your application
• Close connections as soon as possible (they return to the pool)
• Do not change connection properties between Open and Close calls