Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Thanked 0 Times in 0 Posts

    From DAO to ADO (Access 2000)

    I want to to modify a function of linking all tables from DAO into ADO.Can someone help me?
    And also,my second question how can i import instead of linking all the tables? I am very interested in solving this question.
    The function i have copied from the book called Programming for Dummies is the following
    Sub LinkAllTables(DBName)
    'link all tables in source database
    Dim FrontDB As DAO.Database,BackDB AsDAODatabase
    Dim Tbl As DAO.TableDef, Lnk As DAO.TableDef
    Set FrontDB = CurrentDB
    SetBackDB =OpenDatabase(DBName)
    For each Tbl In BackDB.TableDefs
    If Tbl.Attributes = 0 then
    Set Lnk = FrontDB.CreateTableDef(Name:=Tbl.Name)
    Lnk.SourceTableName = TblName
    Lnk.Connect = " ; Database = "& BackDB.Name
    FrontDB.TableDefs.Append Lnk
    End If
    End If
    End Sub

    I tried to build the code with ADO myself but it didnt work.I will be grateful for any help
    Note the DataSource of the databases is :
    = C: BE/FrontDB, and =CBEBackDB

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: From DAO to ADO (Access 2000)

    Here's a routine to link a table using ADO. Perhaps you can use it as a jumping off point, since it points up the differences. However, I haven't found a way to identify system tables, except by their names, since I haven't found the equivalent of a systemobject attribute, and you don't want to link all the tables, only the non-system tables and probably the non-hidden tables as well. You would loop through the Tables collection of the Catalog object instead of the DAO Tablesdefs collection of the Database object.
    <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
    <font color=448800>' Open a Catalog on the database in which to create the link. </font color=448800>
    catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & strDBLinkFrom

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

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

    <font color=448800> ' Append the table to the Tables collection.</font color=448800>
    catDB.Tables.Append tblLink

    Set catDB = Nothing
    End Sub <font color=448800> 'CreateLinkedAccessTable(strDBLinkFrom As String, _
    strDBLinkTo As String, _
    strLinkTbl As String, _
    strLinkTblAs As String)
    </font color=448800>


Posting Permissions

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