Creating/deleting a table in another MDB

  • Thread starter Thread starter WSF
  • Start date Start date
W

WSF

Access97
Compliments of the Season to you all.

I am trying to create a table in a second MDB using:

dbs.Execute "SELECT qryAllInToday.* INTO " & ReportTable & " IN '" &
ReportsDBase & "' FROM qryAllInToday;"

ReportTable and ReportsDBase are variable strings representing the Report
MDB, and table to be created therein.

This works fine first time run. Problem is when the table already exists. I
am trying to use an On Error trap to deflect the routine to another
sub-routine to delete the offending existent table, but no luck. With
DoCmd.SetWarnings False etc the above routine still presents an error msgbox
"Runtime error 3010 - Table already exists" etc. thereby halting the code.

I have no problem deleting the already existing table using code, if only I
can get past this error message.

Am I doing it correctly by using an On Error event, or should I test for the
table's existence first and then delete if it does exist. If so, how do I go
about that?

Any help gratefully appreciated.

WSF
 
You don't really care if the table exists or not, so just drop the table
before you run your INSERT INTO query:

on error resume next
dbs.Execute "DROP " & ReportTable
DoEvents
'resume standard error handling
dbs.Execute "SELECT qryAllInToday etc etc ...

The "on error resume next" tells VBA to just keep going if it runs into
trouble (like if the table doesn't exist). Make SURE to turn your standard
error handler back on by replace the commented line with an "On Error GoTo
xxx" line
 
Back
Top