Automate Import of Tables and Relationship

  • Thread starter Thread starter AimeeK via AccessMonster.com
  • Start date Start date
A

AimeeK via AccessMonster.com

Hi, I've developed a database that has been distributed to other branches of
my company within our region, and I'm just planning ahead for the future in
case this database will need to be upgraded. If an upgrade does need to
happen, I will need the users to import four tables and their relationships
from the old database to the newly upgraded one (it would be blank when they
receive it). I would like to automate this process using either a macro or
VBA, but I need to prompt the user to "browse" for the old database, and
select the tables from there, since I don't know where each user has saved
their database. I would like this to happen using a button on the
switchboard. Can this be done? The four table names are as follows and are
all related through a field called Contract ID:

tblContractUpdateMaster - Contract ID is the primary key here
tblSPLInfo - Contract ID is the foreign key here
tblCategoryExceptions - Contract ID is the foreign key here
tblProdExceptions - Contract ID is the foreign key here

Thank you in advance for your help.
 
Hi Aimee,

A far better solution is to split the database, so the data stays in the
back end file and distributing upgrades becomes merely a matter of
distributing a new front end that links to the existing back end. See
http://allenbrowne.com/ser-01.html for more.

To answer your specific question, don't try and import tables and
relationships. Instead, supply the new database complete with tables and
relationships, but without the data. Then you can use the ordinary data
import facilities to import the data from the old database into the new
one: you just have to import stuff in the right sequence (e.g. import
data into tblContractUpdateMaster before you try importing into any of
the tables that refer to it).
 
Thanks, John, I'll have to give splitting a try, and probably just have to
include instructions on how to use the File...Get External Data...Import
commands...thanks again.

John said:
Hi Aimee,

A far better solution is to split the database, so the data stays in the
back end file and distributing upgrades becomes merely a matter of
distributing a new front end that links to the existing back end. See
http://allenbrowne.com/ser-01.html for more.

To answer your specific question, don't try and import tables and
relationships. Instead, supply the new database complete with tables and
relationships, but without the data. Then you can use the ordinary data
import facilities to import the data from the old database into the new
one: you just have to import stuff in the right sequence (e.g. import
data into tblContractUpdateMaster before you try importing into any of
the tables that refer to it).
Hi, I've developed a database that has been distributed to other branches of
my company within our region, and I'm just planning ahead for the future in
Click to expand...
[quoted text clipped - 14 lines]
Thank you in advance for your help.
Click to expand...
Click to expand...
 
Back
Top