ADO - Source property

In ADO (ActiveX Data Objects), the Source property identifies the origin or command that provides data to a Recordset, or specifies where a Command or Error came from.

Its meaning depends on which ADO object you’re using — most commonly Recordset, Command, or Error.

1. Recordset.Source

The Source property of a Recordset specifies the command, SQL statement, table name, or Command object that supplies the data for that recordset.

You can both read and set this property.

Syntax:

recordset.Source [= value]

Example:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

' Set the Source to an SQL query
rs.Source = "SELECT * FROM Employees WHERE DepartmentID = 2"
rs.ActiveConnection = conn
rs.Open

' Display some data
Debug.Print rs.Fields("FirstName").Value

' Check the source
Debug.Print "Recordset Source: " & rs.Source

 Here:

• rs.Source contains the SQL statement used to open the Recordset.

• It could also be a table name or a Command object.

Example: Using a Command Object as Source

Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * FROM Products WHERE CategoryID = ?"

Set rs = New ADODB.Recordset
Set rs.Source = cmd
rs.Open

✅ Here, rs.Source refers to a Command object, not a string.

 You Can Change It Dynamically

You can change the Source property and re-open the Recordset with a new query:

rs.Close
rs.Source = "SELECT * FROM Departments"
rs.Open

2. Command.Source

For a Command object, the Source property defines the command text or the stored procedure name that will be executed.

Syntax:

command.Source [= value]

Example:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.Source = "SELECT * FROM Customers WHERE Country = 'USA'"

Dim rs As ADODB.Recordset
Set rs = cmd.Execute

Debug.Print "Command Source: " & cmd.Source

Notes:

• Source is equivalent to CommandText (they’re interchangeable).

• Can contain:

  • A SQL statement (e.g., SELECT * FROM Employees)

  • A stored procedure name

  • A table name (if CommandType = adCmdTable)

 3. Error.Source

For an Error object (in the Errors collection), the Source property identifies where the error originated — usually the provider name or object that caused the error.

Example:

On Error GoTo HandleError

Dim conn As ADODB.Connection
conn.Open "Provider=SQLOLEDB;Data Source=.;Initial Catalog=TestDB;User ID=wrong;Password=wrong"

Exit Sub

HandleError:
Dim errObj As ADODB.Error
For Each errObj In conn.Errors
    Debug.Print "Error Source: " & errObj.Source
    Debug.Print "Description: " & errObj.Description
Next

Output Example:

Error Source: Microsoft OLE DB Provider for SQL Server
Description: Login failed for user 'wrong'.

Here, Source identifies which ADO provider or component caused the error.

 Summary by Object

 Key Points

• The meaning of Source depends on the object type:

  • For Recordset → identifies the data origin.

  • For Command → specifies the SQL/stored procedure to run.

  • For Error → shows the provider or object that caused the error.

• You can set or get it for Recordset and Command.

• For Error, it’s read-only.

 Quick Example Recap

'--- Recordset Source ---
rs.Source = "SELECT * FROM Employees"

'--- Command Source ---
cmd.Source = "sp_GetEmployeeDetails"

'--- Error Source ---
Debug.Print errObj.Source   ' → "Microsoft OLE DB Provider for SQL Server"