Connect and SourceTableName Properties Example

This example uses the Connect and SourceTableName properties to link various external tables to a Microsoft Jet database. The ConnectOutput procedure is required for this procedure to run.

Sub ConnectX()

    Dim dbsTemp As Database
    Dim strMenu As String
    Dim strInput As String

    ' Open a Microsoft Jet database to which you will link
    ' a table.
    Set dbsTemp = OpenDatabase("DB1.mdb")

    ' Build menu text.
    strMenu = "Enter number for data source:" & vbCr
    strMenu = strMenu & _
        " 1. Microsoft Jet database" & vbCr
    strMenu = strMenu & _

        " 2. dBASE table" & vbCr
    strMenu = strMenu & _
        " 3. Paradox table" & vbCr
    strMenu = strMenu & _
        " M. (see choices 5-9)"

    ' Get user's choice.
    strInput = InputBox(strMenu)

    If UCase(strInput) = "M" Then

        ' Build menu text.
        strMenu = "Enter number for data source:" & vbCr
        strMenu = strMenu & _
            " 4. Microsoft Excel spreadsheet" & vbCr
        strMenu = strMenu & _
            " 5. Lotus spreadsheet" & vbCr
        strMenu = strMenu & _
            " 6. Comma-delimited text (CSV)" & vbCr
        strMenu = strMenu & _
            " 7. HTML table" & vbCr
        strMenu = strMenu & _
            " 8. Microsoft Exchange folder"

        ' Get user's choice.
        strInput = InputBox(strMenu)

    End If

    ' Call the ConnectOutput procedure. The third argument
    ' will be used as the Connect string, and the fourth
    ' argument will be used as the SourceTableName.
    Select Case Val(strInput)
        Case 1
            ConnectOutput dbsTemp, _
                "JetTable", _
                ";DATABASE=C:\My Documents\Northwind.mdb", _
                "Employees"

        Case 2
            ConnectOutput dbsTemp, _
                "dBASETable", _
                "dBase IV;DATABASE=C:\dBASE\Samples", _
                "Accounts"
        Case 3
            ConnectOutput dbsTemp, _
                "ParadoxTable", _
                "Paradox 3.X;DATABASE=C:\Paradox\Samples", _
                "Accounts"
        Case 4
            ConnectOutput dbsTemp, _
                "ExcelTable", _
                "Excel 5.0;" & _
                    "DATABASE=C:\Excel\Samples\Q1Sales.xls", _
                "January Sales"
        Case 5
            ConnectOutput dbsTemp, _
                "LotusTable", _
                "Lotus WK3;" & _
                    "DATABASE=C:\Lotus\Samples\Sales.xls", _
                "THIRDQTR"
        Case 6
            ConnectOutput dbsTemp, _
                "CSVTable", _
                "Text;DATABASE=C:\Samples", _
                "Sample.txt"
        Case 7
            ConnectOutput dbsTemp, _
                "HTMLTable", _
                "HTML Import;DATABASE=http://" & _
                    "www.server1.com/samples/page1.html", _
                "Q1SalesData"
        Case 8
            ConnectOutput dbsTemp, _
                "ExchangeTable", _
                "Exchange 4.0;MAPILEVEL=" & _
                    "Mailbox - Michelle Wortman (Exchange)" & _
                    "|People\Important;", _
                "Jerry Wheeler"
    End Select

    dbsTemp.Close

End Sub

Sub ConnectOutput(dbsTemp As Database, _
    strTable As String, strConnect As String, _
    strSourceTable As String)

    Dim tdfLinked As TableDef
    Dim rstLinked As Recordset
    Dim intTemp As Integer

    ' Create a new TableDef, set its Connect and
    ' SourceTableName properties based on the passed
    ' arguments, and append it to the TableDefs collection.
    Set tdfLinked = dbsTemp.CreateTableDef(strTable)

    tdfLinked.Connect = strConnect
    tdfLinked.SourceTableName = strSourceTable
    dbsTemp.TableDefs.Append tdfLinked

    Set rstLinked = dbsTemp.OpenRecordset(strTable)

    Debug.Print "Data from linked table:"

    ' Display the first three records of the linked table.
    intTemp = 1
    With rstLinked
        Do While Not .EOF And intTemp <= 3
            Debug.Print , .Fields(0), .Fields(1)
            intTemp = intTemp + 1
            .MoveNext
        Loop
        If Not .EOF Then Debug.Print , "[additional records]"
        .Close
    End With

    ' Delete the linked table because this is a demonstration.
    dbsTemp.TableDefs.Delete strTable

End Sub