I’m trying to delete two tables that I import into a 2016 Access database regularly to update other program tables in a program using a VBA code. (1 Viewer)

[Mikee2020]

I’m trying to delete two tables that I import into a 2016 Access database regularly to update other program tables in a program using a VBA code behind an “On Click” [Event Procedure] command button in a form that keeps returning a “Run-time error 2387. You can’t delete the table ‘tbl_(Master_Oil_Sample_Data)1; it is participating in one or more relationships.”

1. When I checked these two tables with a 1 at the end of the table name, do have Relationships assigned to them during the import process, just like the program tables that have the same name, the only difference is that they are automatically assigned a 1 at the end of their names.
2. But when I highlight these two tables and try to delete them manually, I get the following MS Access warning: “Do you want to delete all of the selected objects? Deleting these objects will remove them from all groups.” You cannot undelete forms, reports, and modules.” I press <Yes>, and they're gone.
3. Is there a better way to delete these two imported tables using VBA code so that I do not get the error?

Option Compare Database

Private Sub Command35_Click()

DoCmd.RunSavedImportExport "Import_Data_001"
Beep
MsgBox "Your request to update your tables data has been completed using another MS Access Database file!""", vbInformation, "All rights reserved Copyright © 2019 - 2020, Michael Eisner"
End Sub

Private Sub Command41_Click()

DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "tbl_(Master_Oil_Sample_Interp_Data)1"
DoCmd.DeleteObject acTable, "tbl_(Master_Oil_Sample_Data)1"
Beep
MsgBox "Your request to delete imported tables has been completed!""", vbInformation, "All rights reserved Copyright © 2019 - 2020, Michael Eisner"
End Sub

Private Sub Form_Close()

DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "tbl_(Master_Oil_Sample_Interp_Data)1"
DoCmd.DeleteObject acTable, "tbl_(Master_Oil_Sample_Data)1"
Beep
MsgBox "Temporary imported tables have been completed!""", vbInformation, "All rights reserved Copyright © 2019 - 2020, Michael Eisner"

End Sub

 

[Gasman]

So delete the realtionships first?

A quick Google gives me this from these, but I would probably either delete all the records and re append.?

 

[Dreamweaver]

If these are temp tables why use relationships at all if your going to be deleting them and replacing them

 

[Mikee2020]

Hi, Gasman, I don't want to delete the two unwanted table relationships manually, and then delete the tables. I want to see if the users can delete the two tables using VBA code. They cannot get behind the startup form to do this.

 

[Mikee2020]

Hi, MickJav, The two tables are imported into this database from another access database where they have relationships, and somehow they come over with relationships by there selves. If you noticed the tables I import have the same names as the ones in the user database, they only have a 1 added to them.

 

[Cronk]

Why not link to the tables in the other database instead of importing and deleting repetitively.

 

[Dreamweaver]

What about clearing th data then importing just the data.

 

[Gasman]

Hi, MickJav, The two tables are imported into this database from another access database where they have relationships, and somehow they come over with relationships by there selves. If you noticed the tables I import have the same names as the ones in the user database, they only have a 1 added to them.

I noticed that also have a trailing ) in the name?
The links I posted, if you had looked, show how to delete relationships with VBA?

From what I quickly read, you would need to test extensively, but I would still just clear out the tables and if you ever got that far, then delete the relationships either manually ot via vba to compact and repair now and again.

 

[Mikee2020]

Hi, MickJav, The two tables I imported into the users database from external access database contain only NEW data that is then appended via queries to the existing tables in the user database.
This is why I can't delete the data in the user database. Maybe I can perform Make-Table-Queries of the data in the external database. Name them differently, and import them into the user database? This would stop the table naming issue? And maybe the issue of the relationships?

 

[Mikee2020]

Hi, Cronk, You may have a good idea! Monday, I will try that on a test database, and I’ll let you know what happens.

 

[Mikee2020]

Hi, Gasman, I’m sorry, but I don’t see the links you posted to show me how to delete relationships with VBA? Could you send them again?

 

[Gasman]

Hi, Gasman, I’m sorry, but I don’t see the links you posted to show me how to delete relationships with VBA? Could you send them again?

I tried to be tidy with them?

Look at the words 'this' and 'these'

However linking wuld be a much better option I would have thought.?

 

[Dreamweaver]

I agree with Gasman there then all you need do once the data is added through the queries is tag the records added job done

 

[Mikee2020]

Hi, MickJav, Gasman, Cronk, Thank you for your responses today. I’m going to eat dinner and get ready for work tomorrow.
Yes, we have the corona virus in Florida USA, and I’m an essential worker. I’ll get back to you guys asap.

 

[The_Doc_Man]

There is an easier way, perhaps. Gasman said it as "clear out the tables." I'll be more direct.

Don't bother to delete the tables. Just depopulate them via DELETE * FROM unwantedtable; and *>poof<* the records are gone. To be honest, either method does the same thing to your DB, which is to say it causes "bloat" that will lead to the need to do Compact&Repair on a regular basis.

 

[arnelgp]

instead of of using:

DoCmd.DeleteObject acTable, "tbl_(Master_Oil_Sample_Interp_Data)1"
DoCmd.DeleteObject acTable, "tbl_(Master_Oil_Sample_Data)1"

you can use SQL statement to delete the tables:

Currentdb.Execute "Drop Table tbl_(Master_Oil_Sample_Interp_Data)1;"
Currentdb.Execute "Drop Table tbl_(Master_Oil_Sample_Data)1;"

 

[Pat Hartman]

There is no reason to create relationships between temporary tables. Just delete the relationships. Keep in mind that there is no difference between creating/deleting tables to support a temporary import and leaving the tables but Inserting/Deleting rows. BOTH cause bloat to the database corresponding to the amount of data added/deleted each time. There are other options starting with linking to the external database rather than importing the data and having to replace it when you need fresh data.

 

[Mikee2020]

Hi, arnelgp, Thanks for the suggestion on SQL statement. I will work on this issue this weekend when I have more time. I’m working every day this week. I work for and essential company due to the coronavirus. And thanks to all the other responders.