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...
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:
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")
' Add new field to Employees table.
Set tdfEmployees = .TableDefs!Employees
tdfEmployees.CreateField("DeptID", dbInteger, 2)
' Create new Departments table.
Set tdfNew = .CreateTableDef("Departments")
' 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.
' The index in the primary table must be Unique in
' order to be part of a Relation.
idxNew.Unique = True
' 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
relNew.Fields!DeptID.ForeignName = "DeptID"