Help Needed :If table exists, then Delete table

[Infamous]

I need urgent help, I am required at my job to come up with a command that will check to see if a table exists, and if so, delete the table. The whole process goes like this:


There's a form, and a listbox. 2 buttons...1 is import a file and 2nd is generate report. In the listbox theres 3 excel files. You select one excel file then click on Import, followed by clicking on Generate Report button.

The whole technical process is this:
1. A csv file is imported into a temp table
2. A temp table is created and named "_ImportedSKUS".
3. An append query is exectued to add the data from the "_ImportedSKUS" temp table to the final table, "Imported SKUs".
4. Then the temp table, "_ImportedSKUs" is closed and deleted.

I have a delete command at the end of the sub, which goes like this:

DoCmd.DeleteObject acTable, "_ImportedSKUS".

But I'm also need to generate an If statement before the loop that will check to see if the "_ImportedSKUs" table exists, if so, delete it (using same code listed above) and end if.

Can anyone help me out here???

 

[mhartman]

Hello:
The following will do what you ask. This code is under a command button named Command0. It searches the AllTables collection for a table named tblEmployees and displays a message box if it finds it and then deletes it after the message box is dismissed.

Private Sub Command0_Click()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
' Search for open AccessObject objects in AllTables collection.
For Each obj In dbs.AllTables
If obj.Name = "tblEmployees" Then
MsgBox "Found table"
DoCmd.DeleteObject acTable, "tblEmployees"
End If
Next obj
End Sub

Regards
Mark

 

[Infamous]

Hello:
The following will do what you ask. This code is under a command button named Command0. It searches the AllTables collection for a table named tblEmployees and displays a message box if it finds it and then deletes it after the message box is dismissed.

Private Sub Command0_Click()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
' Search for open AccessObject objects in AllTables collection.
For Each obj In dbs.AllTables
If obj.Name = "tblEmployees" Then
MsgBox "Found table"
DoCmd.DeleteObject acTable, "tblEmployees"
End If
Next obj
End Sub

Regards
Mark

A few questions...

what's Accessobject?
What's application.currentdata?
dbs.alltables?

 

[mhartman]

Hello:

The AllTables collection contains an AccessObject for each table in the CurrentData or CodeData object.

An AccessObject object includes information about one instance of an object.

The CurrentData object refers to the objects stored in the current database by the source (server) application (Jet or SQL).

Regards
Mark

 

[vgersghost]

Infamous:

I'm looking for code to import files like you explain in your first post. " 1. A csv file is imported into a temp table" Do you have a sample of the code you might be able to post to explain this operation.

thx dmh