Insert Statement Syntax

[Magius96]

Coming from MySQL to Access, I realize there are some syntax differences.
The following code works in MySQL, but doesn't work in Access. What's the
correct syntax for the SQL string?

Public Sub AddValues()
Dim strString As String
strString = "Insert into [import] set [Agency]='" & Agency & "',
[Regular]=" & Regular & ", [AWG]=" & AWG
strString = strString & ", [Rehab]=" & Rehab & ", [FFEL]=" & FFEL & ",
[FDSLP]=" & FDSLP & ", [Direct]=" & Direct
DoCmd.RunSQL strString
End Sub

 

[Klatuu]

doesn't work in Access doesn't give us much to go on. Please describe the
problem.

 

[Magius96]

Nevermind, I found the correct syntax. Here it is incase other people have
the same problem

Public Sub AddValues()
Dim strString As String
strString = "INSERT INTO import(Agency, Regular, AWG, Rehab, FFEL, FDSLP,
Direct) SELECT "
strString = strString & Agency & " as Agency, " & Regular & " as Regular,"
& AWG & " as AWG," & Rehab
strString = strString & " as Rehab," & FFEL & " as FFEL," & FDSLP & " as
FDSLP," & Direct & " as Direct"
DoCmd.RunSQL strString
End Sub

doesn't work in Access doesn't give us much to go on. Please describe the
problem.
--
Dave Hargis, Microsoft Access MVP

Coming from MySQL to Access, I realize there are some syntax differences.
The following code works in MySQL, but doesn't work in Access. What's the
correct syntax for the SQL string?

Public Sub AddValues()
Dim strString As String
strString = "Insert into [import] set [Agency]='" & Agency & "',
[Regular]=" & Regular & ", [AWG]=" & AWG
strString = strString & ", [Rehab]=" & Rehab & ", [FFEL]=" & FFEL & ",
[FDSLP]=" & FDSLP & ", [Direct]=" & Direct
DoCmd.RunSQL strString
End Sub

 

[Magius96]

To further explain, in mysql an insert statement used this syntax:

INSERT INTO <table> SET <field>=<value>, <field>=<value>...

But in Access the syntax is:

INSERT INTO <tabl>(<field>,<field>,...) SELECT <value> AS <field>, <value>
AS <field>, <value> AS <field>...

Maybe that clarifies what I was asking a bit more, even though I've already
found the answer.

 

[Steve Sanford]

In Access, the SQL INSERT INTO syntax has 2 main forms and the result of
either of them is adding a new row into the database table.

Form 1
Multiple-record append query (from existing records):

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


Instead of appending existing records from another table, you can specify
the value for each field in a single new record using the VALUES clause.

Form 2
Single-record append query:

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)

As you might already have guessed, the number of the columns in the second
INSERT INTO syntax form must match the number of values into the SQL
statement, otherwise you will get an error.
If you omit the field list, the VALUES clause must include a value for every
field in the table; otherwise, the INSERT operation will fail. Use an
additional INSERT INTO statement with a VALUES clause for each additional
record you want to create.


HTH