Create table relationships using code

[MaggieLeatherman]

Hi I've created a routine that archives 3 tables (Area, Materials, Surveillance) after a Project is complete. I copy the tables and rename them with the project appended onto the end (AreaGA1777)... I then have a screen where a user can pick from archived Projects and view the data. That's where I'm running into problems. The query that is the recordsource for the Archived Data screen contains the renamed tables (AreaGA1777) but they have no relationship built (ex. 1 to many), there for not allowing any changes to the data.
My question.... Can you use code to create these relationships? The table names will always be changing.

Thanks, maggie

 

[CautionMP]

Maggie,
You could use the [tt]ALTER TABLE[/tt] statement in SQL to create the relationships. Here is a sample:

Dim strSQL As String
strSQL = "ALTER TABLE AreaGA1777 " & _
               "ADD CONSTRAINT [i]Name_of_Foreign_Key[/i] " & _
               "FOREIGN KEY [i]Name_of_column_that_is_Foreign_Key[/i] " & _
               "REFERENCES [i]Table_the_Foreign_Key_is_in[/i]([i]Name_of_column_that_is_referenced[/i]);"
DoCmd.RunSQL strSQL

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[TheAceMan1]

How are ya MaggieLeatherman . . .

Have a look at the [blue]Relations Collection[/blue] and the [blue]Relation Object[/blue] . . .

See Ya! . . . . . .