Create/Delete a relationship in Access 2007 Visual basic

[DaveB]

Hi All,

I am currently working on an Access 2007 database with a relationship which I would like to drop, then reattach as part of a procedure.

In a nutshell, I have a ‘create table’ query which fetches data from an SQL database creating a new table in the Access database with all of the data and structure. This is then referenced as a read only table in the local Access database so no changes are made to it. When the user of the database wants to get the updates from the SQL backend a procedure is run to delete the local copy of the table and a new version created again.

The problem I am having is I need to create a 1-to-1 relationship with the read only table which is fine, but when I try to run the delete/re-create procedure it won’t allow me to run it because the table is in a relationship. I would like to know the code I can use to delete a relationship, then afterwards recreate the relationship.

This is the code I have in place at the moment:

DoCmd.SetWarnings warningsoff
‘Turns off Access warnings
DoCmd.OpenQuery "Q:Create Policy Details"
‘This creates the table Tolicy details based on the SQL database
DoCmd.SetWarnings warningson
‘Turns Access warnings back on
MsgBox "Records Updated"
‘Advises user that records have been updated

When I run this is says the following. “you can't delete the table 'Tolicy Details'; it is participating in one or more relationships”
If I remove the relationship it’s fine but I need that in place for other aspects of the database to work.

I'm aware that the procedure of copying the SQL database to the local database is a little unorphodox but because of the nature of the SQL database that the information is retrieved from, I am reluctant to setup any permament links to it so I have opted for this option as a compromise to getting the data without the prospect of making changes or allowing any permanent link to the SQL database.

Any help would be much appreciated.
Thanks in advance,
Dave

 

[DCrake]

Is there any reason you are going down this route? The normal procedure would be to delete the contents on the table first then run an append query to populate the exisitng table with the new records.

If the structure is identical why try to delete and then recreate it?

 

[DaveB]

Hi David,

Thanks for the prompt reply. To be honest I don't know why it's setup like that. That's the way I initially created the database and I've just had a 4 month break from working on it so I'm not entirely sure why I set it up this way. I've been trying to remember where I was at and hadn't really considered changing the way I get the data. Thinking about it, this method you have proposed makes much more sense. I am going to have a tinker now and see if it gets the desired results. I'll post back once done.

Thanks again
Dave

 

[DaveB]

Hi David,

That worked a treat. Thanks a lot for your help. Much appreciated.

Dave.