Creating relationships for imported files

[TracySHC]

I am importing two files into my Access database. One file is imported from another Access database, and the other file is an Excel spreadsheet. I would like to create/maintain relationships with these two files. I am aware of two approaches to this problem: 1) delete the records in each file, and then import the records, or 2) delete the files, import the files, and create the relationships in VBA code. Which approach is best, and how should I proceed?
Thank you for any help and advice,
TracySHC

 

[Craig0201]

To create a relationship.....

ALTER TABLE Table2 ADD CONSTRAINT Relation1 FOREIGN KEY ([Id]) _
REFERENCES Table1 ([Id])

 

[TracySHC]

Craig0201,
Thanks for your response. I am new to Access, so is this VBA code? Also, what if there is more than one relation between the two files? Would the code look like this:

ALTER TABLE Table2 ADD CONSTRAINT Relation1 FOREIGN KEY ([Id]) REFERENCES Table1 ([Id])
ALTER TABLE Table2 ADD CONSTRAINT Relation2 FOREIGN KEY ([Group]) REFERENCES Table2 ([Group])

Thanks again,
TracySHC

 

[Craig0201]

It's SQL. I suggest that you look up ALTER TABLE in Help.

 

[OU18]

Craig,
I have a similar situation where I want to have the relationship between two tables link automagically. This code looks as though it would work for me. But where do you place the lines of code. In the Sql of a query, in a module. Any help is greatly appreciated.

Thanks

 

[PHV]

In VBA you can play with the DoCmd.RunSQL method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886