Deleting Multiple tables

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

I need to build a macro. What I need it to do is delete
the ImportErrors tables that are created in my database
during the import of about 50 files. I can do it through
the normal method of DeleteObject-->Tables--
File1_ImportErrors, but like I said there is about 50
Click to expand...
files, and I would have to do this for those 50 files.

My question is...is there a wildcard that I could use to
delete all the ImportErrors tables? Or is there something
else I could do?

I have tried building a module like this...
Option Explicit
Function DeleteImportErrors()
Dim db As Database, t As TableDef, i As Integer
Set db = CurrentDb()
For i = db.TableDefs.Count - 1 To 0 Step -1
Set t = db.TableDefs(i)
If t.Name Like "*ImportErrors" Then
db.TableDefs.Delete t.Name
End If
Next i
db.Close
End Function

....however I am not sure how to incoporate this into a
button on a form. I am not even sure if this will work
how I want it too.

Please help???
 
Kevin,

At first glancwee, your code looks reasonable. In order to gat a form button
fire it, open the form in design view, fo to the button's properties, and
invoke the code builder on event On Click. There, you could either put all
your code (except for the function / end function satetments), or just a
statement:
Call DeleteImportErrors
and leave your function as is in a general module. Personally I would go for
the Call option, it's easier to debug and can be called from elsewhere if
required.
Alternatively, you could have the button fire a macro with a single RunCode
action to call the function - but since you're already into VB, it's
pointless.
By the way, if you're not calling the code from a macro, and since you're
not using it to return any values, it would be better to change it to a sub
instead of a function.

HTH,
Nikos
 
The following code will delete all tables in your db starting with th
word 'import'.
In design view, right click the button and select properties. Scrol
down to OnClick, select from the dropdown 'Event Procedure'. Click th
"..." button next to the dropdown to open the code window. Place th
following code between the "Private Sub Command Blah_Click" and th
"End Sub".
That should do it. :)

Code
-------------------

Dim tdTable As TableDef
For Each tdTable In CurrentDb.TableDefs
If Left(tdTable.Name, 6) = "import" Then
DoCmd.DeleteObject acTable, tdTable.Name
End If
Next tdTable
 
Back
Top