Prevent "you are about to paste # rows into a new table" message

[CrArC]

Hello there! This might not be the right section to post in but it's regarding tables. Basically I have some SQL statements that copy data to a new table, which is written in VBcode and operates on the click of a button.

Basically the only problem is the messages:
"You are about to paste # rows into a new table/table"
"The existing table <name> will be deleted before you run the query"

The kind of message depends on the way I organise the SQL (either using it to append data (as in INSERT INTO tablename SELECT blah blah) or overwrite (SELECT blah blah INTO tablename).

I'd rather use the latter because it facilitates the desired wiping of all previous data and replacing it with fresh records. However I don't want to have to click "yes" every time this is done? Is there some VBcode perhaps, like the On Err code that I could use to get access to not pop up these messages?

 

[gemma-the-husky]

one way is to surround the runsql command with

docmd.setwarnings(false)
docmd.runsql
docmd.setwarnings(true)

you can set the warnnigs permanently off in tools,options,advanced tab (i think) - confirm actions, deletes etc, but generally not recommended just in case

 

[Rich]

You could also use the .Execute method

 

[CrArC]

Fantastic thanks, I shall give these a go

 

[Pat Hartman]

Do NOT turn warnings off permanently. You will no longer be warned that you haven't saved an object that has been modified. So, after you spend an hour getting a form formatted just right, clicking close without first saving will silently discard all changes. A programmer who has done that is not a pretty sight as he prepares to hurl himself off the roof.

 

[boblarson]

Amen to what Pat said. I've been there, done that - in fact accidentally. I now always put a SetWarnings(True) in my error handling, for events that I turn it off, so that if it errors out it will reset the warnings if the code doesn't make it to the normal SetWarnings(True) spot.