INSERT INTO Syntax question

  • Thread starter Thread starter dhstein
  • Start date Start date
D

dhstein

I'm not sure what the syntax is for this:


DoCmd.RunSQL "INSERT INTO tblCostChanges ("LogDateTime") SELECT
("LogDateTime") FROM qryCostChanges;"

OR

DoCmd.RunSQL "INSERT INTO tblCostChanges (LogDateTime) SELECT (LogDateTime)
FROM qryCostChanges;"

OR

DoCmd.RunSQL "INSERT INTO tblCostChanges ('LogDateTime') SELECT
('LogDateTime') FROM qryCostChanges;"

OR

DoCmd.RunSQL "INSERT INTO tblCostChanges ('"'LogDateTime'"') SELECT
('"'LogDateTime'"') FROM qryCostChanges;"

OR

DoCmd.RunSQL "INSERT INTO tblCostChanges LogDateTime SELECT LogDateTime FROM
qryCostChanges;"

Any help is appreciated
 
Did you try?

I think you'll find that the only one that will compile is

DoCmd.RunSQL "INSERT INTO tblCostChanges (LogDateTime) SELECT (LogDateTime)
FROM qryCostChanges;"

Of course, that seems a bit silly as a query. It's going to add a number of
rows with one field (LogDateTime) to tblCostChanges. None of the other
fields in the rows will be populated, so I'm not sure how much use you'll be
able to make of the data...
 
Thanks Doug.

Yes that did compile - but in fact I need more fields and was just using
that as an example. So now the problem is what is the syntax as I add the
other fields? I've tried it several ways with no success, for example:


DoCmd.RunSQL "INSERT INTO tblCostChanges (LogDateTime, LogSKU) SELECT
(LogDateTime, LogSKU)
FROM qryCostChanges;"
 
Hi dhstein,
DoCmd.RunSQL "INSERT INTO tblCostChanges (LogDateTime, LogSKU)
SELECT (LogDateTime, LogSKU) FROM qryCostChanges;"
Click to expand...

Why did you include the parenthesis within the SELECT portion of this SQL
statement? Try this insead:

DoCmd.RunSQL "INSERT INTO tblCostChanges (LogDateTime, LogSKU)
SELECT LogDateTime, LogSKU FROM qryCostChanges;"

The syntax for INSERT INTO is one of the following forms:

(a) INSERT INTO [some table] (<list of column names>) VALUES (<list of
values>);

(b) INSERT INTO [some table] (<list of columns>) SELECT <column names> FROM
[another table] WHERE ...


Personally, I prefer to use the .Execute method of CurrentDB for running
action queries instead of DoCmd.RunSQL. I also tend to write the SQL
statement to a string variable first. This way, you can use print the SQL
statement to the Immediate Window for further examination, which can be
useful for things like spotting missing spaces, verifying that variables are
correctly substituted, and even copying the statement and pasting into the
SQL View of a new query (often times provides better help with
troubleshooting a query). For example:

Dim strSQL as String

strSQL = "INSERT INTO tblCostChanges (LogDateTime, LogSKU) " _
& "SELECT LogDateTime, LogSKU FROM qryCostChanges;"

Debug.Print strSQL

CurrentDB.Execute strSQL
' or
CurrentDB.Execute strSQL, dbFailOnError '<--Requires DAO checked reference

If you have more than one action query to run within the same procedure, you
can declare a variable, such as "db", instead of repeatedly calling
CurrentDB. For example:

Dim db As DAO.Database
Dim strSQL as String

Set db = CurrentDB()

strSQL = "INSERT INTO tblCostChanges (LogDateTime, LogSKU) " _
& "SELECT LogDateTime, LogSKU FROM qryCostChanges;"

db.Execute strSQL
' or
db.Execute strSQL, dbFailOnError '<--Requires DAO checked reference

In the ExitProcedure routine, add this line of code:

Set db = Nothing



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Tom - thanks for your help - lots of helpful stuff there. On the Debug.Print
- is that something I see in the immediate window ? Also - I'm not sure how
to check the DAO reference :
db.Execute strSQL, dbFailOnError '<--Requires DAO checked reference

David


Tom Wickerath said:
Hi dhstein,
DoCmd.RunSQL "INSERT INTO tblCostChanges (LogDateTime, LogSKU)
SELECT (LogDateTime, LogSKU) FROM qryCostChanges;"
Click to expand...

Why did you include the parenthesis within the SELECT portion of this SQL
statement? Try this insead:

DoCmd.RunSQL "INSERT INTO tblCostChanges (LogDateTime, LogSKU)
SELECT LogDateTime, LogSKU FROM qryCostChanges;"

The syntax for INSERT INTO is one of the following forms:

(a) INSERT INTO [some table] (<list of column names>) VALUES (<list of
values>);

(b) INSERT INTO [some table] (<list of columns>) SELECT <column names> FROM
[another table] WHERE ...


Personally, I prefer to use the .Execute method of CurrentDB for running
action queries instead of DoCmd.RunSQL. I also tend to write the SQL
statement to a string variable first. This way, you can use print the SQL
statement to the Immediate Window for further examination, which can be
useful for things like spotting missing spaces, verifying that variables are
correctly substituted, and even copying the statement and pasting into the
SQL View of a new query (often times provides better help with
troubleshooting a query). For example:

Dim strSQL as String

strSQL = "INSERT INTO tblCostChanges (LogDateTime, LogSKU) " _
& "SELECT LogDateTime, LogSKU FROM qryCostChanges;"

Debug.Print strSQL

CurrentDB.Execute strSQL
' or
CurrentDB.Execute strSQL, dbFailOnError '<--Requires DAO checked reference

If you have more than one action query to run within the same procedure, you
can declare a variable, such as "db", instead of repeatedly calling
CurrentDB. For example:

Dim db As DAO.Database
Dim strSQL as String

Set db = CurrentDB()

strSQL = "INSERT INTO tblCostChanges (LogDateTime, LogSKU) " _
& "SELECT LogDateTime, LogSKU FROM qryCostChanges;"

db.Execute strSQL
' or
db.Execute strSQL, dbFailOnError '<--Requires DAO checked reference

In the ExitProcedure routine, add this line of code:

Set db = Nothing



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

dhstein said:
Thanks Doug.

Yes that did compile - but in fact I need more fields and was just using
that as an example. So now the problem is what is the syntax as I add the
other fields? I've tried it several ways with no success, for example:


DoCmd.RunSQL "INSERT INTO tblCostChanges (LogDateTime, LogSKU)
SELECT (LogDateTime, LogSKU) FROM qryCostChanges;"
Click to expand...
Click to expand...
 
Hi David,
On the Debug.Print - is that something I see in the immediate window?
Click to expand...

Yes--when the code runs, it should cause the strSQL value to print to the
ie. the said:
Also - I'm not sure how to check the DAO reference
Click to expand...

While you are viewing any code module, click on Tools | References. You
should see a checked reference for the "Microsoft DAO 3.6 Object Library" (if
using Access 2000, 2002, or 2003), or the "Microsoft DAO 3.51 Object Library"
(if using Access 97). Here are two very good links to additional information
on references:

Solving Problems with Library References (Allen Browne)
http://allenbrowne.com/ser-38.html

Access Reference Problems (Doug Steele)
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

When you are modifying VBA code you should save your changes, and do a Debug
| Compile {VBAProjectName}, before attempting to test the code. In the above
case, if you were missing a reference to the DAO Object Library, you would
get a compile time error. If the code compiles without any errors, the option
to compile should appear "greyed out" until you make some change to the code.
As long as the compile action appears disabled, then you should be good to go.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Back
Top