Item not found in this collection (RT 3265)

[degsyh]

Hi

Newbie to the forum but consider myself an 'ok' coder Can't see that this question has come up anywhere but apologies if it has.

Background

Creating a db for work. I've got two tables linked in a relationship. 1 will get refreshed on a weekly basis and will contain differing information. The other table is user comments relating to the information in the 1st table. They are linked with cascading updates via a primary & unique field.

In order to refresh the 1st table i need to break the relationships, delete the information from it and then re-create the relationships. Data won't get deleted from the 1st table if its still linked to the second table. I don't have cascading deletes set as I dont want data being deleted from the second table.

Problem

I've got VBA code to do all of the above. The problem is deleting the relationship - even though I'm creating it with the same name further down in code it fails at trying to delete it with "item not found in collection". I can even run debug code to show names of all relationships (there are only 2; this and the system one) and its the exact same name as I'm trying to delete. Also if I run code to try and create the relationship with the same name it says it already exists!

I can delete/ recreate manually through the relationship window but it just doesn't seem to like it via code. When run from fresh (1st run) or after I do a compact/repair the code works. I'm guessing its something to do with indexes?

Any help appreciated

 

[boerbende]

i am not sure if I get it right here, but why not
- copy table 2 to a temp table
- delete records table 2
- delete records table 1
- populate table 2 again from tmp table
- delete tmp table

don't forget to compact on close

Ben

 

[Cronk]

You are making the situation difficult for yourself.

If the record needs to be changed, update it with the new information ie change everything except the primary key.

If the record is to deleted, your cascade delete will remove the corresponding record in the second table.

 

[degsyh]

Hi, I cant change everything but the primary key in the first table as it won't always be the same set of records that is pulled back - there may be additional records. I also don't want the record deleting from the second table (i've not got cascade deletes set) as I need to keep a record of the data in case it does come back again into the first table.

 

[degsyh]

Thanks for the reply Ben I'll give it a go. Sounds like a bit of a bodge though!

 

[degsyh]

i am not sure if I get it right here, but why not
- copy table 2 to a temp table
- delete records table 2
- delete records table 1
* populate table 1 here *
- populate table 2 again from tmp table
- delete tmp table

don't forget to compact on close

Ben

Sorted cheers. Had to populate table 1 at the above point as otherwise it wouldnt let me populate table 2 due to linked records.

Thinking about it deleting and recreating the relationship was a bodge anyway. Thanks for your help