Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Expand Minimize

Source Property Example (VB)

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


    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

Public Sub Main()
    On Error GoTo ErrorHandler

    Dim Cnxn As ADODB.Connection
    Dim rstTitles As ADODB.Recordset
    Dim rstPublishers As ADODB.Recordset
    Dim rstPublishersDirect As ADODB.Recordset
    Dim rstTitlesPublishers As ADODB.Recordset
    Dim cmdSQL As ADODB.Command
    Dim strCnxn As String
    Dim strSQL As String
    ' Open a connection
    Set Cnxn = New ADODB.Connection
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    Cnxn.Open strCnxn
    ' Open a recordset based on a command object
    Set cmdSQL = New ADODB.Command
    Set cmdSQL.ActiveConnection = Cnxn
    strSQL = "Select title, type, pubdate FROM Titles ORDER BY title"
    cmdSQL.CommandText = strSQL
    Set rstTitles = cmdSQL.Execute()
    ' Open a recordset based on a table
    Set rstPublishers = New ADODB.Recordset
    strSQL = "Publishers"
    rstPublishers.Open strSQL, Cnxn, adOpenStatic, adLockReadOnly, adCmdTable
    'rstPublishers.Open strSQL, Cnxn, , , adCmdTable
     ' the above two lines of code are identical
    ' Open a recordset based on a table
    Set rstPublishersDirect = New ADODB.Recordset
    rstPublishersDirect.Open strSQL, strCnxn, , , adCmdTableDirect
    ' 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, strCnxn, , , 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 & _
        "rstPublishersDirect source: " & vbCr & _
        rstPublishersDirect.Source & vbCr & vbCr & _
        "rstTitlesPublishers source: " & vbCr & _

    ' clean up
    Set rstTitles = Nothing
    Set rstPublishers = Nothing
    Set rstTitlesPublishers = Nothing
    Set Cnxn = Nothing
    Exit Sub
    ' clean up
    If Not rstTitles Is Nothing Then
        If rstTitles.State = adStateOpen Then rstTitles.Close
    End If
    Set rstTitles = Nothing
    If Not rstPublishers Is Nothing Then
        If rstPublishers.State = adStateOpen Then rstPublishers.Close
    End If
    Set rstPublishers = Nothing
    If Not rstTitlesPublishers Is Nothing Then
        If rstTitlesPublishers.State = adStateOpen Then rstTitlesPublishers.Close
    End If
    Set rstTitlesPublishers = Nothing
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
© 2015 Microsoft