Select Into VBA

[snoopy2003]

Hello,



I am trying to populate an empty table with select query results (query name = 'qry'.

stSQL = "SELECT * INTO tmpEmpPerType FROM (" & qry & ");"

Method1: DoCmd.RunSQL stSQL
Method2: CurrentDb.Execute stSQL, dbFailOnError

Method 1 works fine and populate the table.
Method 2 cause an error: "table tmpEmpPerType allready exists"

What am I doing wrong in method 2 ?
(I preffer using CurrentDb.Execute)

Thank you for your help

[ajetrumpet]

the table already exists. that's what wrong.

I believe, if you use docmd.runsql instead of currentdb.execute, you won't get that message because the warning message will popup asking if you want to delete the table first.

consequently, if you want to get rid of this error completely with the ".execute" method, run this code before anything else:

on error resume next
docmd.DeleteObject acTable, "tablename"

make sure to set those vba error options to "break in class module", otherwise that code will still break.

[snoopy2003]

Hello,

Thank you for your help.

When I used docmd.runsql I disabled the error messages and so
I was not aware that it delete the table before insert new data.

I deleted the table contents before running "select into".
I thought that delete the table content and adding new data
runs faster than dropping the table and adding new data.

What do you think ?

Anyway, I will use db.execute to drop the table before running select into.

Thank you for help and support

[rpeare]

If you have a large dataset dropping the table and recreating it is more efficient and it saves your database some space as it works. If your dataset is small it doesn't really matter which way you choose quite honestly but if the dataset has the potential to become very large (10s of thousands of records) dropping the table then recreating/importing is what you want to do.