When to drop tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Each month, I create several tables and scrub the data using SQL to import
clean data from a JD Edwards file. I use VBA to drop each table so that
the new monthly tables can be created. How can I first check to see if the
tables already exist in the database? If they exist, I want to drop the
table so I can create new ones. If they don't exist, I want it to create
the tables.

I've reviewed a function that checks for existing tables, however, I'm not
sure on where to place the function or how to use it. I would appreciate an
explanation of this funtion if that is the best route to use.

Thanks!
 
I use the following. It loops through every table in the database and checks
if its name tallies with the one I want to drop - that way the code won't
fall over if the table doesn't happen to be there (I would guess the function
you're talking about does a similar loop each time, returning True if the
table is there, False if not):

Sub DeleteTables()
Dim myTable As DAO.TableDef
For Each myTable In CurrentDb.TableDefs
If myTable.Name = "[the particular table name]" Then
[your code to drop table]
Next
End Sub

MartinInUK
 
=?Utf-8?B?U2hhcm9uSW5HZW9yZ2lh?=
I use VBA to drop each table so that
the new monthly tables can be created. How can I first check to see
if the tables already exist in the database? If they exist, I want
to drop the table so I can create new ones. If they don't exist, I
want it to create the tables.
Click to expand...

This is not a very good idea: it's going to cause file bloat so you'll
end up with very big mdbs unless you compact and repair frequently, and
it's also asking for a file corruptioin, so make sure you do lots of
backups.

In any case, it's faster and safer to empty the tables:


' we can pick up errors here:
'
On Error Resume Next

' get rid of the P098 table:
'
' the WHERE TRUE is redundant, but reminds you this is a
' Big Operation with no chance to undo!
'
db.Execute "DELETE FROM P098 WHERE TRUE", dbFailOnError

' now see if it failed
If Err.Number <> 0 Then
' no such table, better make one
db.Execute "CREATE TABLE P098 (etc etc ", dbFailOnError
End If

' you now have one empty table


Hope that helps


Tim F
 
Back
Top