By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,477 Members | 1,602 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,477 IT Pros & Developers. It's quick & easy.

Insert Into SQL

P: n/a
Can the SQL code from an Append query be inserted into the form module?
The form module currently has a DoCmd Run Macro statement.
The Macro opens the Append query which runs correctly.
I'm trying to eliminate the DoCmd in the form code and the referenced Macro.

SQL code:
INSERT INTO tblWT1 ( Mfg, Series, Model, Config, Options )
SELECT tblMain.Mfg, tblMain.Series, tblMain.Model, tblMain.Config, _
tblMain.Options
FROM tblMain
WHERE (((tblMain.Mfg)=[Forms]![Form1].[Text1]));

Help file on Insert Into shows:
Syntax

Multiple-record append query:

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

So
INSERT INTO tblWT1 ( Mfg, Series, Model, Config, Options )
was modified to:
INSERT INTO tblWT1 [(Mfg[, Series[, Model[, Config[, Options]]]])]

tblWT1 gets highlighted and error message says Expected End of Statement.

There are probably other syntax errors as well, but the code stops at the
target definition. The module has DIM statements for all tables and fields.

Chuck
--

Oct 15 '07 #1
Share this Question
Share on Google+
11 Replies


P: n/a
Yes: you can dispense with both the macro and the saved query, and Execute
the query statement directly in your code.

Here's an explanation and example:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chuck" <li*****@schoollink.netwrote in message
news:tk********************************@4ax.com...
Can the SQL code from an Append query be inserted into the form module?
The form module currently has a DoCmd Run Macro statement.
The Macro opens the Append query which runs correctly.
I'm trying to eliminate the DoCmd in the form code and the referenced
Macro.

SQL code:
INSERT INTO tblWT1 ( Mfg, Series, Model, Config, Options )
SELECT tblMain.Mfg, tblMain.Series, tblMain.Model, tblMain.Config, _
tblMain.Options
FROM tblMain
WHERE (((tblMain.Mfg)=[Forms]![Form1].[Text1]));

Help file on Insert Into shows:
Syntax

Multiple-record append query:

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

So
INSERT INTO tblWT1 ( Mfg, Series, Model, Config, Options )
was modified to:
INSERT INTO tblWT1 [(Mfg[, Series[, Model[, Config[, Options]]]])]

tblWT1 gets highlighted and error message says Expected End of Statement.

There are probably other syntax errors as well, but the code stops at the
target definition. The module has DIM statements for all tables and
fields.

Chuck
--
Oct 15 '07 #2

P: n/a
On Mon, 15 Oct 2007 23:00:48 +0800, "Allen Browne" <Al*********@SeeSig.Invalid>
wrote:
>Yes: you can dispense with both the macro and the saved query, and Execute
the query statement directly in your code.

Here's an explanation and example:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html
Thanks fot your help.
I'm not enough of a programer - yet - to make use of it - yet.
I'm going to keep working on it until I understand what it is doing.

Chuck
--

Oct 15 '07 #3

P: n/a
If you mean can you insert an SQL statement inline like a VBA command, VBA
will not know how to handle it. You use the Execute method of a database
object to execute SQL in a module. That passes it to the database engine,
which does know how to handle it.

See Allen's examples.

Larry Linson
Microsoft Access MVP
"Chuck" <li*****@schoollink.netwrote in message
news:tk********************************@4ax.com...
Can the SQL code from an Append query be inserted into the form module?
The form module currently has a DoCmd Run Macro statement.
The Macro opens the Append query which runs correctly.
I'm trying to eliminate the DoCmd in the form code and the referenced
Macro.

SQL code:
INSERT INTO tblWT1 ( Mfg, Series, Model, Config, Options )
SELECT tblMain.Mfg, tblMain.Series, tblMain.Model, tblMain.Config, _
tblMain.Options
FROM tblMain
WHERE (((tblMain.Mfg)=[Forms]![Form1].[Text1]));

Help file on Insert Into shows:
Syntax

Multiple-record append query:

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

So
INSERT INTO tblWT1 ( Mfg, Series, Model, Config, Options )
was modified to:
INSERT INTO tblWT1 [(Mfg[, Series[, Model[, Config[, Options]]]])]

tblWT1 gets highlighted and error message says Expected End of Statement.

There are probably other syntax errors as well, but the code stops at the
target definition. The module has DIM statements for all tables and
fields.

Chuck
--

Oct 16 '07 #4

P: n/a
On Mon, 15 Oct 2007 23:00:48 +0800, "Allen Browne" <Al*********@SeeSig.Invalid>
wrote:
>Yes: you can dispense with both the macro and the saved query, and Execute
the query statement directly in your code.

Here's an explanation and example:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html
Allen,

I've fought this thing to a stand still. The Function UnpickAll() reads:

Function UnpickAll()
Dim db As DAO.Database
Dim strSql As String

strSql = "INSERT INTO WT1 ( Mfg, Series, Model, Config, Options ) " & _
"SELECT SmallTable.Mfg, SmallTable.Series, SmallTable.Model, SmallTable.
Config, SmallTable.Options " & _
"FROM SmallTable " & _
"WHERE (((SmallTable.Mfg)=[Forms]![Form1].[Text1])) SET IsPicked = False
WHERE IsPicked = True;"
Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) where unpicked."
Set db = Nothing
End Function

The line: db.Execute strSql, dbFailOnError gives:
Syntax error (missing operator) in query expreddion
'(((SmallTable.Mfg)=[Forms]![Form1].[Text1])) SET IsPicked = False WHERE
IsPicked = True'

If the line: db.Execute strSql, dbFailOnError is commented out, the function
runs and the MsgBox correctly shows 0 records were unpicked.

A missing operator should be obvious, but I can't find it.
Suggestions please.

Chuck
--
Oct 20 '07 #5

P: n/a
Chuck wrote:
On Mon, 15 Oct 2007 23:00:48 +0800, "Allen Browne" <Al*********@SeeSig.Invalid>
wrote:

>>Yes: you can dispense with both the macro and the saved query, and Execute
the query statement directly in your code.

Here's an explanation and example:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html


Allen,

I've fought this thing to a stand still. The Function UnpickAll() reads:

Function UnpickAll()
Dim db As DAO.Database
Dim strSql As String

strSql = "INSERT INTO WT1 ( Mfg, Series, Model, Config, Options ) " & _
"SELECT SmallTable.Mfg, SmallTable.Series, SmallTable.Model, SmallTable.
Config, SmallTable.Options " & _
"FROM SmallTable " & _
"WHERE (((SmallTable.Mfg)=[Forms]![Form1].[Text1])) SET IsPicked = False
WHERE IsPicked = True;"
Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) where unpicked."
Set db = Nothing
End Function

The line: db.Execute strSql, dbFailOnError gives:
Syntax error (missing operator) in query expreddion
'(((SmallTable.Mfg)=[Forms]![Form1].[Text1])) SET IsPicked = False WHERE
IsPicked = True'

If the line: db.Execute strSql, dbFailOnError is commented out, the function
runs and the MsgBox correctly shows 0 records were unpicked.

A missing operator should be obvious, but I can't find it.
Suggestions please.

Chuck
The first thing I'd do if I were in your position is
1) after you define strSQL, add the lines
Debug.Print strSQLs
Exit sub
2) Go to the debug window and copy the line to the clipboard
3) Create a new query, select View/SQL from the menu and paste the
strSQL line
4) Run.

Anyway, you wrote in the opening post
"INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression"

Your new code is
strSql = "INSERT INTO WT1 ( Mfg, Series, Model, Config, Options ) " & _
"SELECT SmallTable.Mfg, SmallTable.Series,
SmallTable.Model, SmallTable.Config, SmallTable.Options " & _
"FROM SmallTable " & _
"WHERE (((SmallTable.Mfg)=[Forms]![Form1].[Text1]))
SET IsPicked = False
WHERE IsPicked = True;"

I don't see the word "Where" in your definition. You can use one. But
I now seen 2 Where's. What's with that?

And what is SET? Are you doing an APPEND and UPDATE query at the same
time? Are we talking Access?

In the past I've done things like
IIF(IsPicked,False,IsPicked) As IsPicked
Oct 21 '07 #6

P: n/a
You need to concatenate the value from Text1 on Form1 into the string.

This kind of thing:

Function InsertEm()
Dim db As DAO.Database
Dim strSql As String

If Not IsNull(Forms!Form1!Text1) Then
strSql = "INSERT INTO tblWT1 ( Mfg, Series, Model, Config, Options )
" & _
"SELECT tblMain.Mfg, tblMain.Series, tblMain.Model, " & _
"tblMain.Config, tblMain.Options FROM tblMain " & _
"WHERE tblMain.Mfg = """ & Forms!Form1!Text1 & """;"

Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) inserted."
End If
set db = Nothing
End Function

If Mfg is a Number field (not a Text field), drop the extra quotes, i.e.:
"WHERE tblMain.Mfg = " & Forms!Form1!Text1 & ";"

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chuck" <li*****@schoollink.netwrote in message
news:as********************************@4ax.com...
On Mon, 15 Oct 2007 23:00:48 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid>
wrote:
>>Yes: you can dispense with both the macro and the saved query, and Execute
the query statement directly in your code.

Here's an explanation and example:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

Allen,

I've fought this thing to a stand still. The Function UnpickAll() reads:

Function UnpickAll()
Dim db As DAO.Database
Dim strSql As String

strSql = "INSERT INTO WT1 ( Mfg, Series, Model, Config, Options ) " & _
"SELECT SmallTable.Mfg, SmallTable.Series, SmallTable.Model,
SmallTable.
Config, SmallTable.Options " & _
"FROM SmallTable " & _
"WHERE (((SmallTable.Mfg)=[Forms]![Form1].[Text1])) SET IsPicked =
False
WHERE IsPicked = True;"
Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) where unpicked."
Set db = Nothing
End Function

The line: db.Execute strSql, dbFailOnError gives:
Syntax error (missing operator) in query expreddion
'(((SmallTable.Mfg)=[Forms]![Form1].[Text1])) SET IsPicked = False WHERE
IsPicked = True'

If the line: db.Execute strSql, dbFailOnError is commented out, the
function
runs and the MsgBox correctly shows 0 records were unpicked.

A missing operator should be obvious, but I can't find it.
Suggestions please.

Chuck
--
Oct 21 '07 #7

P: n/a
On Sun, 21 Oct 2007 11:15:39 +0800, "Allen Browne" <Al*********@SeeSig.Invalid>
wrote:
>You need to concatenate the value from Text1 on Form1 into the string.

This kind of thing:

Function InsertEm()
Dim db As DAO.Database
Dim strSql As String

If Not IsNull(Forms!Form1!Text1) Then
strSql = "INSERT INTO tblWT1 ( Mfg, Series, Model, Config, Options )
" & _
"SELECT tblMain.Mfg, tblMain.Series, tblMain.Model, " & _
"tblMain.Config, tblMain.Options FROM tblMain " & _
"WHERE tblMain.Mfg = """ & Forms!Form1!Text1 & """;"

Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) inserted."
End If
set db = Nothing
End Function

If Mfg is a Number field (not a Text field), drop the extra quotes, i.e.:
"WHERE tblMain.Mfg = " & Forms!Form1!Text1 & ";"

No luck.

Been looking in Amazon.com for a book on SQL. Most seem to be SQL Server.
Don't think those are what I need. I'm working on a simple little stand alone
A97 database on my home computer. If I ever get to a city large enough to have
a Barns and Noble, then I can 'peek' to see if anything looks like something I
could use. Any book suggestions you may have would be greatly appreciated.

Chuck
--
Oct 21 '07 #8

P: n/a
If you really want a book on SQL, "SQL for Mere Mortals" would be good (John
Viescas and Michael Hernandez)

Hopefully you are aware that you can build any query in the graphical query
design window, and then switch to SQL View (Query menu, in query design.)
What I do is build the query there using any old criteria, and then copy it
to the code window. You then have to concatenate the query into the string,
adding the " quotes for Text fields or the # delimiter for Date fields.

Here's some info about how to add the quotes:
http://allenbrowne.com/casu-17.html

And here's the form I use to copy the SQL statement into code:
http://allenbrowne.com/ser-71.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chuck" <li*****@schoollink.netwrote in message
news:bb********************************@4ax.com...
On Sun, 21 Oct 2007 11:15:39 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid>
wrote:
>>You need to concatenate the value from Text1 on Form1 into the string.

This kind of thing:

Function InsertEm()
Dim db As DAO.Database
Dim strSql As String

If Not IsNull(Forms!Form1!Text1) Then
strSql = "INSERT INTO tblWT1 ( Mfg, Series, Model, Config,
Options )
" & _
"SELECT tblMain.Mfg, tblMain.Series, tblMain.Model, " & _
"tblMain.Config, tblMain.Options FROM tblMain " & _
"WHERE tblMain.Mfg = """ & Forms!Form1!Text1 & """;"

Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) inserted."
End If
set db = Nothing
End Function

If Mfg is a Number field (not a Text field), drop the extra quotes, i.e.:
"WHERE tblMain.Mfg = " & Forms!Form1!Text1 & ";"


No luck.

Been looking in Amazon.com for a book on SQL. Most seem to be SQL Server.
Don't think those are what I need. I'm working on a simple little stand
alone
A97 database on my home computer. If I ever get to a city large enough to
have
a Barns and Noble, then I can 'peek' to see if anything looks like
something I
could use. Any book suggestions you may have would be greatly
appreciated.

Chuck
--
Oct 22 '07 #9

P: n/a
On Mon, 22 Oct 2007 09:48:36 +0800, "Allen Browne" <Al*********@SeeSig.Invalid>
wrote:
>If you really want a book on SQL, "SQL for Mere Mortals" would be good (John
Viescas and Michael Hernandez)
At this point, I really do want the book. I've avoided writing any real code
nearly 20 years now. And I learned a long time ago not to write a program that
I could get with just plain dirty ol' money. But the itch is coming back.
Thank you.
>Hopefully you are aware that you can build any query in the graphical query
design window, and then switch to SQL View (Query menu, in query design.)
That is what I did. I have a form with a command button that runs a macro.
The macro opens an append query. It's kludgy, but it works correctly. I
copied the append query SQL and replaced the code for the command button to run
a macro with the SQL statement.
>What I do is build the query there using any old criteria, and then copy it
to the code window. You then have to concatenate the query into the string,
adding the " quotes for Text fields or the # delimiter for Date fields.
Did that. It's looking like the syntax of the SQL statement needs to be
different from what the query requires and what [Event Procedure] code in a
from module requires.
>
Here's some info about how to add the quotes:
http://allenbrowne.com/casu-17.html

And here's the form I use to copy the SQL statement into code:
http://allenbrowne.com/ser-71.html
I will get the html files and study.

Thank you,
Chuck
--

Oct 22 '07 #10

P: n/a
On Mon, 22 Oct 2007 09:48:36 +0800, "Allen Browne" <Al*********@SeeSig.Invalid>
wrote:
>If you really want a book on SQL, "SQL for Mere Mortals" would be good (John
Viescas and Michael Hernandez)

Hopefully you are aware that you can build any query in the graphical query
design window, and then switch to SQL View (Query menu, in query design.)
What I do is build the query there using any old criteria, and then copy it
to the code window. You then have to concatenate the query into the string,
adding the " quotes for Text fields or the # delimiter for Date fields.

Here's some info about how to add the quotes:
http://allenbrowne.com/casu-17.html

And here's the form I use to copy the SQL statement into code:
http://allenbrowne.com/ser-71.html
Got it! Database running just fine. Thanks for the two files. Macro deleted.
Query saved so I see where the SQL statement came from, but query is no longer
used. I saved the new form, again so I can see where and how the strSql to
strVBA takes place. My manual translation was close, but I did not have the
vbCrLf. Also thanks for the custom Replace() function. I am using A97.

Chuck
--

Oct 23 '07 #11

P: n/a
That's great news.

Happy Accessing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chuck" <li*****@schoollink.netwrote in message
news:j8********************************@4ax.com...
On Mon, 22 Oct 2007 09:48:36 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid>
wrote:
>>If you really want a book on SQL, "SQL for Mere Mortals" would be good
(John
Viescas and Michael Hernandez)

Hopefully you are aware that you can build any query in the graphical
query
design window, and then switch to SQL View (Query menu, in query design.)
What I do is build the query there using any old criteria, and then copy
it
to the code window. You then have to concatenate the query into the
string,
adding the " quotes for Text fields or the # delimiter for Date fields.

Here's some info about how to add the quotes:
http://allenbrowne.com/casu-17.html

And here's the form I use to copy the SQL statement into code:
http://allenbrowne.com/ser-71.html

Got it! Database running just fine. Thanks for the two files. Macro
deleted.
Query saved so I see where the SQL statement came from, but query is no
longer
used. I saved the new form, again so I can see where and how the strSql
to
strVBA takes place. My manual translation was close, but I did not have
the
vbCrLf. Also thanks for the custom Replace() function. I am using A97.

Chuck
--
Oct 23 '07 #12

This discussion thread is closed

Replies have been disabled for this discussion.