ADO Recordset Object Source Property

The source for the data in a Recordset object (Command object, SQL statement, table name, or stored procedure).

Source Property Return Values (ADO Recordset Object)

Sets a String value or Command object reference; returns only a String value.

Source Property Remarks (ADO Recordset Object)

Use the Source property to specify a data source for a Recordset object using one of the following: an ADO Command Object variable, an SQL statement, a stored procedure, or a table name. The Source property is read/write for closed Recordset objects and read-only for open Recordset objects.

If you set the Source property to a Command object, the ActiveConnection property of the Recordset object will inherit the value of the ActiveConnection property for the specified Command object. However, reading the Source property does not return a Command object; instead, it returns the CommandText property of the Command object to which you set the Source property.

If the Source property is an SQL statement, a stored procedure, or a table name, you can optimize performance by passing the appropriate Options argument with the ADO Recordset Object Open Method call.

Source Property Example (ADO Recordset Object)

This Visual Basic example demonstrates the Source property by opening three Recordset objects based on different data sources.

Public Sub SourceX()

Dim cnn1 As ADODB.Connection

Dim rstTitles As ADODB.Recordset

Dim rstPublishers As ADODB.Recordset

Dim rstTitlesPublishers As ADODB.Recordset

Dim cmdSQL As ADODB.Command

Dim strCnn As String

Dim strSQL As String

` Open a connection.

Set cnn1 = New ADODB.Connection

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

cnn1.Open strCnn

` Open a recordset based on a command object.

Set cmdSQL = New ADODB.Command

Set cmdSQL.ActiveConnection = cnn1

cmdSQL.CommandText = "Select title, type, pubdate " & _

"FROM titles ORDER BY title"

Set rstTitles = cmdSQL.Execute()

` Open a recordset based on a table.

Set rstPublishers = New ADODB.Recordset

rstPublishers.Open "publishers", strCnn, , , adCmdTable

` Open a recordset based on an SQL string.

Set rstTitlesPublishers = New ADODB.Recordset

strSQL = "SELECT title_ID AS TitleID, title AS Title, " & _

"publishers.pub_id AS PubID, pub_name AS PubName " & _

"FROM publishers INNER JOIN titles " & _

"ON publishers.pub_id = titles.pub_id " & _

"ORDER BY Title"

rstTitlesPublishers.Open strSQL, strCnn, , , adCmdText

` Use the Source property to display the source of each recordset.

MsgBox "rstTitles source: " & vbCr & _

rstTitles.Source & vbCr & vbCr & _

"rstPublishers source: " & vbCr & _

rstPublishers.Source & vbCr & vbCr & _

"rstTitlesPublishers source: " & vbCr & _

rstTitlesPublishers.Source

rstTitles.Close

rstPublishers.Close

rstTitlesPublishers.Close

cnn1.Close

End Sub