VB to Turn off Notifications

[jo15765]

Trying to run this query to create a table which would be a backup copy of my table from the current database, but create the copy in another database. I want to run this automatically on my formclose() event.....

   Dim db As Database  
   Set db = CurrentDb  
   DoCmd.SetWarnings False  
   db.Execute "qryBackupTable", dbFailOnError  
   Set db = Nothing  
   DoCmd.SetWarnings True

But I get an error table already exists, how to overwrite table so this error does not occur?

[orange]

According to your code you are working with Currentdb.
What is the query sql? How are you accessing/connecting/linking to the
"other" database?

You could open the "other" database" and check if a particular table exists or not.
You could run sql to delete the table in the "other database", and if it isn't there, just continue on.


Good luck

[jo15765]

I just used the prompts when building the query to make a table, and it said make table in current database or another database, and I selected my other database. It has no problem making the table, but once the table is there, it can not overwrite it w/o human intervention (at least with the current code).

How would I use SQL to delete the table in the manner you are referencing?
Or could I set it in the VBA of my onclose() event like I am trying to work with?
This may not even be possible, but say something like,
if tbl.backuptable = true then
docmd.delete backuptable
end if


Or maybe not?!

[ajetrumpet]

have you tried copying the first table and then deleting it when the make table op is done??

you can use DOCMD's for that. in addition, you can set a string variable to:

db.TableDefs(tblName).name

with 'tblName' being the name of the table that either exists or not. check the ERR.NUMBER property after that. if it's 3265, the table does not exist.

or better yet, if the thing doesn't error out, the table is probably already there.

[jo15765]

I just changed my query from a make table query to an append query. Then I set a delete query to run 1st to delete the entries on the table, and then my append query runs, to append the data to the table.