SQL INSERT INTO

[rekibmtn]

Hi all,

I have the following code that is updating a SQL database table from Access
(linked application to SQL). The following lines of code work fine as long as
all the Access fields are numeric. If I try to use a text field, I get an
invalid number of paramaters error. I know it is just the syntax for text
fields - think I am missing some ' or "....Any help is greatly appreciated.

Dim strSql As String
strSql = "INSERT INTO VIS_TRANS_AUDIT(PART_ID,QUANTITY,ROLL_NO,MSF,L_FEET,
COMMENTS) SELECT " & Forms![PolyexeProcess]![PartID] & ", " & Forms!
[PolyexeProcess]![RollAWeight] & ", " & Forms![PolyexeProcess]![PolyexeID] &
", " & Forms![PolyexeProcess]![CalcMsfRollA] & ", " & Forms![PolyexeProcess]!
[RollALength] & ", " & Forms![PolyexeProcess]![MEMO] & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError

 

[Tom van Stiphout]

The syntax requires text and date values to be wrapped in
single-quotes:
strSql = "insert into myTable(myTextField1, myDateField2) values ('" &
forms!myForm!myTextField1 & "', '" & forms!myForm!myDateField2 & "')"

In this sample I am wrapping the values in single-quotes, and I am
using the INSERT INTO ... VALUES syntax to insert a single record.
The INSERT INTO ... SELECT syntax is typically used to insert a set of
records, most often from another table.

-Tom.
Microsoft Access MVP

 

[rekibmtn]

Thanks Tom, have it working now....Joe

The syntax requires text and date values to be wrapped in
single-quotes:
strSql = "insert into myTable(myTextField1, myDateField2) values ('" &
forms!myForm!myTextField1 & "', '" & forms!myForm!myDateField2 & "')"

In this sample I am wrapping the values in single-quotes, and I am
using the INSERT INTO ... VALUES syntax to insert a single record.
The INSERT INTO ... SELECT syntax is typically used to insert a set of
records, most often from another table.

-Tom.
Microsoft Access MVP

Hi all,

[quoted text clipped - 11 lines]

[RollALength] & ", " & Forms![PolyexeProcess]![MEMO] & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError

 

[florine]

Hi all,

I have the following code that is updating a SQL database table from
Access
(linked application to SQL). The following lines of code work fine as long
as
all the Access fields are numeric. If I try to use a text field, I get an
invalid number of paramaters error. I know it is just the syntax for text
fields - think I am missing some ' or "....Any help is greatly
appreciated.

Dim strSql As String
strSql = "INSERT INTO VIS_TRANS_AUDIT(PART_ID,QUANTITY,ROLL_NO,MSF,L_FEET,
COMMENTS) SELECT " & Forms![PolyexeProcess]![PartID] & ", " & Forms!
[PolyexeProcess]![RollAWeight] & ", " & Forms![PolyexeProcess]![PolyexeID]
&
", " & Forms![PolyexeProcess]![CalcMsfRollA] & ", " &
Forms![PolyexeProcess]!
[RollALength] & ", " & Forms![PolyexeProcess]![MEMO] & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError