SQL "insert into" syntax

[mark kubicki]

I have a form which included fields [Manufacturer], [CatalogNo], data
and have inserted this code behind the CatalogNo update event

it is intended to insert into table: [additionalPages] in its field:
[catalogsheetlink]
records from the table: [FixtureCatalogsPages] that match the values in the
current record [Manufacturer], [CatalogNo]


Dim SQL As String
SQL = "INSERT INTO additionalPages (catalogsheetlink) " & _
"from FixtureCatalogsPages" & _
"WHERE frm.Manufacturer = manufacturer and frm.CatalogNo =
CatalogNo"
DoCmd.RunSQL SQL


.... I'm getting a syntax error and can't seem to "pin it down"; any
suggestions

thanks in advance,
mark

 

[Jeff Boyce]

I didn't see a ";" at the end

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bob Hairgrove]

I have a form which included fields [Manufacturer], [CatalogNo], data
and have inserted this code behind the CatalogNo update event

it is intended to insert into table: [additionalPages] in its field:
[catalogsheetlink]
records from the table: [FixtureCatalogsPages] that match the values in the
current record [Manufacturer], [CatalogNo]


Dim SQL As String
SQL = "INSERT INTO additionalPages (catalogsheetlink) " & _
"from FixtureCatalogsPages" & _
"WHERE frm.Manufacturer = manufacturer and frm.CatalogNo =
CatalogNo"
DoCmd.RunSQL SQL


... I'm getting a syntax error and can't seem to "pin it down"; any
suggestions

thanks in advance,
mark

What other columns does your table "additionalPages" have? Because you
are inserting a new row with "INSERT INTO", yet you only specify one
column name. Are you sure you don't mean to update an existing row (or
several) instead of creating a new row?

If you do mean to insert a new row, then all other columns should
either be nullable or have default values for this to work.

The syntax for INSERT INTO would be one of the following:

(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 ... 'etc.

You seem to want the second form, but you don't have the keyword
SELECT anywhere.

 

[Bob Hairgrove]

I didn't see a ";" at the end

I don't think it matters. Access will run it anyway -- if it is SQL in
a stored query, Access will add the semicolon for you.

 

['69 Camaro]

Hi, Mark.

... I'm getting a syntax error and can't seem to "pin it down"; any
suggestions

Here are several suggestions for you:

1.) Don't use Reserved words as identifiers. SQL is a Reserved word.

2.) There's no space between FixtureCatalogsPages and WHERE, so Jet will
check for the data source that you named "FixtureCatalogsPagesWHERE," which
doesn't exist. Your complete SQL Statement looks like the following:

"INSERT INTO additionalPages (catalogsheetlink) from
FixtureCatalogsPagesWHERE frm.Manufacturer = manufacturer and frm.CatalogNo
= CatalogNo"

3.) It's missing the SELECT clause needed when selecting a column "FROM" a
data source, such as FixtureCatalogsPages.

4.) It's missing the column name in FixtureCatalogsPages that corresponds
with catalogsheetlink in the additionalPages table.

5.) The frm.Manufacturer and frm.CatalogNo are values in VBA, not in SQL,
so you need to concatenate the values in the string you're building. And if
these values are strings, not numbers, then they also need to be delimited
with quotation marks around the value within the string. For example, if
manufacturer were a string and CatalogNo were a number, then the code would
be the following (replace SomeCol with your column's name):

Dim sSQL As String

sSQL = "INSERT INTO additionalPages (catalogsheetlink) " & _
"SELECT SomeCol " & _
"from FixtureCatalogsPages " & _
"WHERE manufacturer = '" & frm.Manufacturer.Value & _
"' and CatalogNo = " & frm.CatalogNo.Value & ";"

CurrentDb().Execute sSQL, dbFailOnError

6.) The SQL statement above will produce the following string (where I've
inserted example values Boeing and 4140 for the values of the two form's
properties):

"INSERT INTO additionalPages (catalogsheetlink) SELECT SomeCol from
FixtureCatalogsPages WHERE manufacturer = 'Boeing' and CatalogNo = 4140;"

7.) Note that the Execute method with the dbFailOnError argument is
preferred over RunSQL so that if the transaction fails, the transaction will
be rolled back and an error message will be shown. Otherwise, the query
runs silently, meaning no confirmation message where the user needs to click
"OK" will appear if the "Confirm record changes" for action queries option
is set. And it runs faster than RunSQL.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

I have a form which included fields [Manufacturer], [CatalogNo], data
and have inserted this code behind the CatalogNo update event

it is intended to insert into table: [additionalPages] in its field:
[catalogsheetlink]
records from the table: [FixtureCatalogsPages] that match the values in
the current record [Manufacturer], [CatalogNo]


Dim SQL As String
SQL = "INSERT INTO additionalPages (catalogsheetlink) " & _
"from FixtureCatalogsPages" & _
"WHERE frm.Manufacturer = manufacturer and frm.CatalogNo =
CatalogNo"
DoCmd.RunSQL SQL


... I'm getting a syntax error and can't seem to "pin it down"; any
suggestions

thanks in advance,
mark