INSERT INTO programmed query failure

[Doojinsi]

Hi,

This query, to backup my People table --
INSERT INTO People_BACKUP SELECT * FROM People;

works fine. But when I run it from a button in VB code on
a form like this --

Set DB = CurrentDb()
SQL_STMT = "INSERT INTO " & "People_BACKUP" & _
" SELECT * FROM " & "People" & ";"
DB.Execute (SQL_STMT)
SQL_STMT = ""
Set CURRENT_RS = DB.OpenRecordset ("People_BACKUP") 'DAO!
COUNT_OF_ROWS = CURRENT_RS.RecordCount
CURRENT_RS.Close
Set CURRENT_RS = Nothing
MsgBox "People_BACKUP contains " & COUNT_OF_ROWS & _
" records"

It actually displays the number of records in the
recordset but when I exit the form and examine the list of
tables, People_BACKUP is not there. Why would this be?

Dooj--

 

[Van T. Dinh]

INSERT INTO ... is an *Append* Query so the Table MUST exist before the
Query is executed.

You are not confused between "SELECT ... INTO ..." (Make-Table Query) and
"INSERT INTO ..." (Append Query), are you?

 

[Doojinsi]

-----Original Message-----
INSERT INTO ... is an *Append* Query so the Table MUST exist before the
Query is executed.

You are not confused between "SELECT ... INTO ..." (Make- Table Query) and
"INSERT INTO ..." (Append Query), are you?

Hi Van,

Yes, I have confused them. But the question is, why does
it work in an Access query (creating the file when it
doesn't exist), but doesn't work when executing the SQL
from VB?

Dooj --

 

[Van T. Dinh]

Anything can happened since you confused between the 2 SQL, for example, you
might have tried an Append Query and the destination Table didn't exist or
you might have tried a Make-Table Query and the dest. Table already existed.

You should ALWAYS use the dbFailOnError like

DB.Execute SQL_STMT, dbFailOnError

so that you can see if any error happens during the execution of the SQL
String.