Edit relationships using a macro (1 Viewer)

munkeyroot

Registered User.
Local time
Today, 15:53
Joined
Jan 13, 2011
Messages
76
Hi Everyone
hope you are all well
i'm not new to access but new to macros, just wondered if is was possible to edit a relationship through a macro?

Background:
Everyday i have to import a new table from a third party DB.
rename the old one ....old ...:) ..and rename the new.
at this point the "main" table relationship still points to the old table and i have to manually tell the main table to look at the new table, then i can delete the old.

hope this makes sence

Cheers Munk:banghead:
 

isladogs

MVP / VIP
Local time
Today, 15:53
Joined
Jan 14, 2017
Messages
18,186
Suggest you instead LINK to the table in the external database.
It will then stay up to date.

If you want to keep the previous day's table for some reason, copy it to a backup

However, in answer to your original question, it is possible to break and make relationships using VBA.
See http://allenbrowne.com/func-DAO.html
 
Last edited:

munkeyroot

Registered User.
Local time
Today, 15:53
Joined
Jan 13, 2011
Messages
76
Hi ridders

i know fella, having an open link would make life easier :), however my IT manager is a little nervous about it, as we have a lot of other things that link to the third party db already

thank you for the info i shall give it a shot my friend.

Munk
 

isladogs

MVP / VIP
Local time
Today, 15:53
Joined
Jan 14, 2017
Messages
18,186
If your IT manager is worried about the tables being updated when linked then you could use the following approach:

Have a separate database to manage the update with links to both the table in your external DB & relevant tables in your main DB.
This is only used by program admins. End users have no access to it
Then create a procedure which updates a table in your main DB based on the latest version in the external DB
Create a scheduled task to run at a quiet time e.g. 03:00 to launch the new database, run the update & close

I have used this approach every night for about 15 years to extract 30 CSV files from an external database, process them then update tables in the main SQL backend database

OR as previously mentioned use the DAO functions from Allen Browne's website

Whichever approach you use, macros aren't up to the job. Use VBA
 

Users who are viewing this thread

Top Bottom