ADO - ADO connection

1. What is an ADO Connection?

An ADO Connection is an object used to establish a link between your application and a database.
It uses a connection string that specifies the database provider, server name, database name, username, and password.

2. Syntax for ADO Connection

Connection.Open ConnectionString, UserID, Password, Options

Parameters:

  • ConnectionString → Specifies the database, provider, and other properties.

  • UserID → Database username (optional if in connection string).

  • Password → Database password (optional if in connection string).

  • Options → Optional flags for how the connection opens.

3. Common ADO Connection String Examples

(a) Connecting to SQL Server

Provider=SQLOLEDB;
Data Source=ServerName;
Initial Catalog=DatabaseName;
User ID=YourUsername;
Password=YourPassword;

(b) Connecting to Microsoft Access

Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\Database\MyDB.accdb;
Persist Security Info=False;

(c) Connecting to Oracle

Provider=OraOLEDB.Oracle;
Data Source=OracleDB;
User ID=YourUsername;
Password=YourPassword;

4. Example: ADO Connection in VB

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

' Open connection to SQL Server
conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

If conn.State = 1 Then
    MsgBox "Connection Successful!"
End If

' Close connection
conn.Close
Set conn = Nothing

5. Example: ADO Connection in ASP (Classic)

<%
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

Response.Write "Connected Successfully!"

conn.Close
Set conn = Nothing
%>

6. Example: ADO Connection in VBScript

Dim conn
Set conn = CreateObject("ADODB.Connection")

conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

If conn.State = 1 Then
    WScript.Echo "Connection Established"
End If

conn.Close
Set conn = Nothing

7. Best Practices

  • Always close connections after use.

  • Use Try...Catch or On Error Resume Next for error handling.

  • Avoid hardcoding usernames and passwords — store them securely.

  • Prefer trusted connections where possible:

    Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;Integrated Security=SSPI;