Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002

    Unanswered: VBA to automaticaly import and replace tables

    I am trying to set up a database using some tables from an external database. I have been linking to the external database with Read Only access but that has limited me to using the DB while on the network.

    Instead I would like to import the tables (which is easy enough) and then update them whenever I feel like it by running a macro.

    Does anyone have code that I could use to replace current tables in my DB with the newly imported files automatically. Added complexity is that when I import the tables I have to declare the keys afterwards and also recreate the relationships...

    Any help would be much appreciated.

  2. #2
    Join Date
    Jun 2002
    Yes, your problem it's pretty. first of all you have to transfer de tables.
    You can do this by a macro.
    The part where you have to rebuild your relations am keys it will be done by VBA. Here is an example for you. I hope it will help:
    Sub CreateRelationX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim tdfNew As TableDef
    Dim idxNew As Index
    Dim relNew As Relation
    Dim idxLoop As Index

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    With dbsNorthwind
    ' Add new field to Employees table.
    Set tdfEmployees = .TableDefs!Employees
    tdfEmployees.Fields.Append _
    tdfEmployees.CreateField("DeptID", dbInteger, 2)

    ' Create new Departments table.

    Set tdfNew = .CreateTableDef("Departments")

    With tdfNew
    ' Create and append Field objects to Fields
    ' collection of the new TableDef object.
    .Fields.Append .CreateField("DeptID", dbInteger, 2)
    .Fields.Append .CreateField("DeptName", dbText, 20)
    ' Create Index object for Departments table.
    Set idxNew = .CreateIndex("DeptIDIndex")
    ' Create and append Field object to Fields
    ' collection of the new Index object.

    idxNew.Fields.Append idxNew.CreateField("DeptID")
    ' The index in the primary table must be Unique in
    ' order to be part of a Relation.
    idxNew.Unique = True
    .Indexes.Append idxNew
    End With

    .TableDefs.Append tdfNew

    ' Create EmployeesDepartments Relation object, using
    ' the names of the two tables in the relation.
    Set relNew = .CreateRelation("EmployeesDepartments", _
    tdfNew.Name, tdfEmployees.Name, _


    ' Create Field object for the Fields collection of the
    ' new Relation object. Set the Name and ForeignName
    ' properties based on the fields to be used for the
    ' relation.
    relNew.Fields.Append relNew.CreateField("DeptID")
    relNew.Fields!DeptID.ForeignName = "DeptID"
    .Relations.Append relNew

    ' Print report.
    Debug.Print "Properties of " & relNew.Name & _
    " Relation"
    Debug.Print " Table = " & relNew.Table

    Debug.Print " ForeignTable = " & _
    Debug.Print "Fields of " & relNew.Name & " Relation"

    With relNew.Fields!DeptID
    Debug.Print " " & .Name
    Debug.Print " Name = " & .Name
    Debug.Print " ForeignName = " & .ForeignName
    End With

    Debug.Print "Indexes in " & tdfEmployees.Name & _
    " TableDef"
    For Each idxLoop In tdfEmployees.Indexes
    Debug.Print " " & idxLoop.Name & _

    ", Foreign = " & idxLoop.Foreign
    Next idxLoop

    ' Delete new objects because this is a demonstration.
    .Relations.Delete relNew.Name
    .TableDefs.Delete tdfNew.Name
    tdfEmployees.Fields.Delete "DeptID"
    End With

    End Sub

    Good luck


Posting Permissions

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