Results 1 to 3 of 3
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Create a linked table in ADO (2000 SP2)

    In DAO it was easy to create a Tabledef sets its connect property to another database and then append it to the tables collection
    In ADO this seems rather more of a chore.
    Is there an easy way to do this, or do I have to set up the table
    and then define all the properties and add the values.

    A code example of creating a linked table would be great.

    I need to be able to temporarily link to a SQL server table.
    Yes I could do this with an ADO recordset or a Direct query
    BUT just from an academic point of view I'd like to know the answer.
    Andrew

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create a linked table in ADO (2000 SP2)

    This is the example from the Programmer's Reference that comes with the developer's edition.

    <pre>Sub CreateLinkedAccessTable(strDBLinkFrom As String, _
    strDBLinkTo As String, _
    strLinkTbl As String, _
    strLinkTblAs As String)

    Dim catDB As ADOX.Catalog
    Dim tblLink As ADOX.Table

    Set catDB = New ADOX.Catalog
    ' Open a Catalog on the database in which to create the link.
    catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & strDBLinkFrom

    Set tblLink = New ADOX.Table
    With tblLink
    ' Name the new Table and set its ParentCatalog property to the
    ' open Catalog to allow access to the Properties collection.
    .Name = strLinkTblAs
    Set .ParentCatalog = catDB

    ' Set the properties to create the link.
    .Properties("Jet OLEDB:Create Link") = True
    .Properties("Jet OLEDB:Link Datasource") = strDBLinkTo
    .Properties("Jet OLEDB:Remote Table Name") = strLinkTbl
    End With

    ' Append the table to the Tables collection.
    catDB.Tables.Append tblLink

    Set catDB = Nothing
    End Sub</pre>

    Charlotte

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Create a linked table in ADO (2000 SP2)

    Spot on!
    I haven't got the Developers edition, but this is exactly what I needed.

    Thanks.
    Andrew
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •