Saved Import Help Needed

[jmischel]

I have a saved import routine that has suddenly began giving me trouble. The import should overwrite the existing data. When I try to run the saved import, I get the error:
"You can't delete the table 'MakeSchedule'; it is participating in one or more relationships."

Any idea what causes this error and/or how to fix it?

Thanks,
Jason

 

[jmischel]

I should also note that I can successfully append data to the MakeSchedule table using a saved import. It is only the overwrite import that does not work.

Thanks,
Jason

 

[boblarson]

If your table is in your Relationships and is linked to something else, it will not let you replace it unless you remove that relationship.

I usually suggest NOT replacing the table and keeping the relationships intact. Instead you can either clear the table and import or, if you need to just append data you can import to a temporary table that you set up once and then you clear it, import the data, and then use an append query to move the data over to the live table.

 

[jmischel]

Thanks for the response. How is the best way to clear all the data in the table and use an append import? Is there some simple VBA code that I can assign to a button?

I have some pretty good VBA knowledge in Excel but have done very little in Access.

Thanks,
Jason

 

[boblarson]

To clear a table with VBA:

CurrentDb.Execute "DELETE * FROM TableNameHere", dbFailOnError

 

[dem1an]

Does this actually clear the table with relationships still in tact? I used this command and it would delete only rows without relationships.

DELETE [TableName].* FROM [TableName]

 

[boblarson]

Yes, it doesn't mess with the relationships, it only deletes data.

 

[dem1an]

Yes, it doesn't mess with the relationships, it only deletes data.

I created a button and put the code into VBA. I got a Run-time error '3200':

The record cannot be deleted or changed because table includes related records.

I deleted the relationship and was off to the races. Am I missing something? I'd like to clear it and the relationship remain. My index is built into the data I'm reimporting, so it's no problem losing my index.

 

[boblarson]

The reason why you couldn't delete it is because it had been used somewhere. The error message you got wasn't really an error message but a notice to you that you can't delete a record which has other child records in other tables. By forcing the delete you just deleted that record but left the child records in place (thereby creating what is known as orphan records).

If you really needed to delete that then you should have had the checkbox for Cascade Deletes checked in your relationship from that table to the others that it is linked to. Then by deleting that record all of the child records would also be deleted.

Also, a lot of people will tell you that you shouldn't really delete anything. But instead you should have a boolean field in each table to be able to mark something as deleted and then in all of your queries and form/report recordsources you just select records that haven't been checked as deleted. That way, if you find you did delete in error, it is easy to restore. Plus you can actually get data/statistical data from the deleted items if you need later on.