SQL - insert into

[Daniel Pineault]

Hello,

I am trying to process data from a temp table and depending on criteria, in
one case append it to the data table. To do so I thought that the INSERT
INTO SQL statement was the way to go but seem to be having difficulty with
the proper syntax.

here is an excerpt from my code

sISQL = "INSERT INTO tbl_Clients * " & _
"SELECT * " & _
"FROM [tbl_sales tree import] " & _
"WHERE [Hist ID]='" & ![Hist ID] & "'"
DoCmd.RunSQL sISQL 'Execute the SQL statement

I figure the issue is with the wilcards. What is the proper approach? I
really do not want to list all the fields (what happens if they are changed)
as illustrated in the help file. Is there a way to simply copy a specific
record from one table to another?

Thank you for the helping hand,

Daniel P

 

[Dirk Goldgar]

Hello,

I am trying to process data from a temp table and depending on criteria,
in
one case append it to the data table. To do so I thought that the INSERT
INTO SQL statement was the way to go but seem to be having difficulty with
the proper syntax.

here is an excerpt from my code

sISQL = "INSERT INTO tbl_Clients * " & _
"SELECT * " & _
"FROM [tbl_sales tree import] " & _
"WHERE [Hist ID]='" & ![Hist ID] & "'"
DoCmd.RunSQL sISQL 'Execute the SQL statement

I figure the issue is with the wilcards. What is the proper approach? I
really do not want to list all the fields (what happens if they are
changed)
as illustrated in the help file. Is there a way to simply copy a specific
record from one table to another?

You've got an unwanted asterisk in the statement. Assuming, of course, that
both tables have the same fields in the same order -- not always a safe
assumption -- your statement could be:

sISQL = "INSERT INTO tbl_Clients " & _
"SELECT * " & _
"FROM [tbl_sales tree import] " & _
"WHERE [Hist ID]='" & ![Hist ID] & "'"

Note that, for a production application, the assumption I mentioned above is
not usually a good one to make. Listing the fields is safer, though it
requires more maintenance; or you could use DAO to discover the fields in
the target table and build your SQL string accordingly.