Insert Into SQL
am 15.10.2007 16:47:16 von Chuck
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
--
Re: Insert Into SQL
am 15.10.2007 17:00:48 von Allen Browne
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" wrote in message
news:tkt6h31tur84hgkv7l0u6ktfs8kkj2l9k6@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
> --
>
Re: Insert Into SQL
am 16.10.2007 00:59:32 von Chuck
On Mon, 15 Oct 2007 23:00:48 +0800, "Allen Browne"
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
--
Re: Insert Into SQL
am 16.10.2007 06:00:13 von Larry Linson
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" wrote in message
news:tkt6h31tur84hgkv7l0u6ktfs8kkj2l9k6@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
> --
>
Re: Insert Into SQL
am 21.10.2007 00:57:27 von Chuck
On Mon, 15 Oct 2007 23:00:48 +0800, "Allen Browne"
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
--
Re: Insert Into SQL
am 21.10.2007 02:59:45 von Salad
Chuck wrote:
> On Mon, 15 Oct 2007 23:00:48 +0800, "Allen Browne"
> 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
Re: Insert Into SQL
am 21.10.2007 05:15:39 von Allen Browne
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" wrote in message
news:as0lh3t8jn5qje0b7095ohutlfleo7p0ri@4ax.com...
> On Mon, 15 Oct 2007 23:00:48 +0800, "Allen Browne"
>
> 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
> --
Re: Insert Into SQL
am 21.10.2007 15:48:19 von Chuck
On Sun, 21 Oct 2007 11:15:39 +0800, "Allen Browne"
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
--
Re: Insert Into SQL
am 22.10.2007 03:48:36 von Allen Browne
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" wrote in message
news:bbkmh3d9fpmthe7mg46nkm66s871dv4omv@4ax.com...
> On Sun, 21 Oct 2007 11:15:39 +0800, "Allen Browne"
>
> 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
> --
Re: Insert Into SQL
am 22.10.2007 14:53:54 von Chuck
On Mon, 22 Oct 2007 09:48:36 +0800, "Allen Browne"
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
--
Re: Insert Into SQL
am 23.10.2007 12:25:04 von Chuck
On Mon, 22 Oct 2007 09:48:36 +0800, "Allen Browne"
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
--
Re: Insert Into SQL
am 23.10.2007 14:57:59 von Allen Browne
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" wrote in message
news:j8irh3tcj55vf9l1qnlo7i4i53boup5ajj@4ax.com...
> On Mon, 22 Oct 2007 09:48:36 +0800, "Allen Browne"
>
> 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
> --
Re: Insert Into SQL
am 30.10.2007 00:26:12 von Chuck
On Mon, 22 Oct 2007 09:48:36 +0800, "Allen Browne"
wrote:
>If you really want a book on SQL, "SQL for Mere Mortals" would be good (John
>Viescas and Michael Hernandez)
>
Book ordered from Barns and Noble. Free shipping.
Much cheaper than driving a hundred miles one way to store
Chuck
--