Copy Tables with Relationships?

[Psycholicon]

Hello, skilled Access technicians. I have a DB that keeps track of drawings. There is one master table (Drawing Log) and then another table (Drawing Link) that shows what drawings are involved in individual products. These tables are related by the field Drawing Number. Both tables are also related to other tables on the same field and others. The main purpose of the DB is to allow the users to compose and issue Change Notices that would add, delete, or revise the drawings on the Drawing Log. That would be easy enough, but the monkey wrench is that a Notice could be composed and go through all the steps, but then be deleted at the last second. To deal with this, I made it so that the Notices all do their edits on a unique copy table, bearing their number (ex. 08-05 Log). If the Notice is released, then Drawing Log is deleted, the copy log is copied as the new Drawing Log, and then deleted. Nice and tidy. This all works fine, but not I'm moving on to the Drawing Link. I want to know if there's a way to do the same trick, copy BOTH tables and have them worked on, then they take the place of the originals when the notice is released. This would, however, require that when they are copied, the relationship between them stays, but not their relationship to other tables. Then, when the notice is released, the other relationships stay in place. The users will have very limited knowledge of Access, so I wouldn't want to force them to learn how to establish those relationships and keep it all going. I really hope that there's some way through VB to deal with the relationships, but I can't figure out any.

 

[Psycholicon]

Ok, I have found a way to work around the problem. After much searching, I found the code to establish relationships, but it turns out that the version of Visual Basic that we are operating with doesn't recognize the type 'Relation' or 'Database', as in Dim newrelationship As Relation. Instead, I'll preserve the original tables and when the notice is released, delete all the records in the Log and Insert Into from the copy. Unfortunately adding the new drawings to products will have to be done individually to the drawings after the release is final, but there was already a form to do this anyway. You can't make everything as user-friendly as you'd like, especially when operating old software on a guess-and-check learning curve.