Deleting Tables in Query that are in relationships

[pnpez]

Does anyone have a suggestion as to the best way to deal with deleting and replacing a table in a query? The table I'm deleting is already in a relationship so it won't delete. Thanks.

 

[boblarson]

Why are you deleting a table? You are trying to delete a table completely from the database?

Also, just changing tables inside a query is no big deal and doesn't require anything other than replacing the table in a query so I'm not sure what you seem to be dealing with.

 

[Dhamdard]

Exactly. U don't need to delete a table if you want to change an existing table in a query. Simple go to design view of query, click on 'Show Table', and from there add the table that you want. And right click on the upper bar of the previously opened query and select 'remove' which will remove the one that you don't want.

If you want to delete a table, you delete it from the navigation pane from Tables view. Do it after being very sure that you don't need the table.

 

[pnpez]

Sorry I wasn't clear.
Every time I add new data I want to rerun the "make table" query to update another existing table, so the query tells you that the table will be deleted before it runs.
But when I do that it doesn't work because it says the table is in an existing relationship in another query. Do you think append would work better?

 

[boblarson]

Post a screenshot of your relationships. (upload it here - zip it first because you aren't to the threshold which can upload images without being zipped - and not to some photo sharing site as those are all blocked by my workplace).

 

[pnpez]

Bob,
I'm trying to insert the zipped file here but not working.

 

[MSAccessRookie]

Sorry I wasn't clear.
Every time I add new data I want to rerun the "make table" query to update another existing table, so the query tells you that the table will be deleted before it runs.
But when I do that it doesn't work because it says the table is in an existing relationship in another query. Do you think append would work better?

Perhaps instead of a Make Table Query, you can create an Update Table Macro that Deletes the current data and then appends new Rows to the existing Table.

 

[pnpez]

After reading up on the Update Query, it doesn't look like that will help me. I need to completely replace the table every time I run it.

 

[boblarson]

Bob,
I'm trying to insert the zipped file here but not working.

Hmm, see if this helps:
http://www.access-programmers.co.uk/forums/showthread.php?t=140587

It is about uploading a database but if you start with step 3 with a zipped image, you should be able to follow the same steps. Remember it has to be at, or below 2Mb after zipping.

 

[boblarson]

Perhaps instead of a Make Table Query, you can create an Update Table Macro that Deletes the current data and then appends new Rows to the existing Table.

That would be the optimal approach in my opinion too. Thanks for mentioning it.

 

[boblarson]

After reading up on the Update Query, it doesn't look like that will help me. I need to completely replace the table every time I run it.

No, you don't need to replace it everytime. And it isn't an UPDATE query you would be using. You would be using a Delete query and then an APPEND query.