Need Help with SQL statement - "Insert Into"

F

FatMan

Hi all:
I need some help working with a SQL Insert Into statement. I have used it
to add a record to a table and replace one field with the desired data, with
the help of a previous post. To be perfectly honest though I don't
understand it as I don't normally use SQL.

The code I have used to add the record and update the value for field
FileName is:

CurrentDb.Execute ("INSERT INTO tblFileName (FileName) SELECT """ & Response
& """")

Now I am faced with a much larger task of trying to add a record to table
and update 13 fields with the values stored in variables in my code. The
fields in the table (tblFillingData) and data types are as follows:

Seg01 - text field
Seg02 - text field
Seg03 - text field
Seg04 - text field
Seg05 - number (double)
Seg06 - number (double)
Seg07 - Date/Time (value will always be a time)
Seg08 - Date/Time (value will always be a time)
Seg09 - Date/Time (value will always be a time)
Seg10 - number (double)
Seg11 - text
Seg12 - text
Seg13 - number (long integer)

If someone could please explain the syntax to use the SQL Insert Into
statement to append a record to a table (table: tblFillingData) with the
variables stored in my program I would greatly appreciated it.

Thanks,
FatMan
 
P

pietlinden

Hi all:
I need some help working with a SQL Insert Into statement.  I have usedit
to add a record to a table and replace one field with the desired data, with
the help of a previous post.  To be perfectly honest though I don't
understand it as I don't normally use SQL.

The code I have used to add the record and update the value for field
FileName is:

CurrentDb.Execute ("INSERT INTO tblFileName (FileName) SELECT """ & Response
& """")

Now I am faced with a much larger task of trying to add a record to table
and update 13 fields with the values stored in variables in my code.  The
fields in the table (tblFillingData) and data types are as follows:

Seg01 - text field
Seg02 - text field
Seg03 - text field
Seg04 - text field
Seg05 - number (double)
Seg06 - number (double)
Seg07 - Date/Time (value will always be a time)
Seg08 - Date/Time (value will always be a time)
Seg09 - Date/Time (value will always be a time)
Seg10 - number (double)
Seg11 - text
Seg12 - text
Seg13 - number (long integer)

If someone could please explain the syntax to use the SQL Insert Into
statement to append a record to a table (table: tblFillingData) with the
variables stored in my program I would greatly appreciated it.

Thanks,
FatMan
Click to expand...

use a recordset

dim rs as dao.recordset
set rs=DBEngine(0)
(0).OpenRecordset("MyTableName",dbOpenTable,dbAppendOnly)
rs.AddNew
rs.fields("TextFieldName")="textvalue"
rs.fields("NumericFieldName")=3
rs.fields("SomeCalculatedValue")=fGetResult()
rs.Update

rs.close
set rs=nothing
 
D

Douglas J. Steele

CurrentDb.Execute "INSERT INTO tblFillingData " & _
"(Seg01, Seg02, Seg03, Seg04, Seg05, Seg06, " & _
"Seg07, Seg08, Seg09, Seg10, Seg11, Seg12, " & _
"Seg13) " & _
"VALUES (""" & Variable1 & """, """ & Variable2 & """, """ & _
Variable3 & """, """ & Variable4 & """, " & Variable5 & ", "
Variable6 & ", " & Format(Variable7, "\#hh\:nn\:ss\#") & ", " & _
Format(Variable8, "\#hh\:nn\:ss\#") & ", " & _
Format(Variable9, "\#hh\:nn\:ss\#") & ", " & _
Variable10 & ", """ & Variable11 & """, " & _
Variable12 & """, " & Variable13 & ")"


Text fields need to be delimited with quotes, Date and Time fields need to
be delimited to #, and numeric fields don't need a delimiter. The SQL string
you're executing shouldn't be in parentheses, and if you're strictly insert
a value from a variable, using the VALUES syntax is probably better. In
other words, your previous one should have been:

CurrentDb.Execute "INSERT INTO tblFileName (FileName) " & _
"VALUES(""" & Response & """)"


Note that there can be a problem if your text strings happen to include
double quotes. Rather than go into the details here, take a look at my May,
2004 "Access Answers" column in Pinnacle Publication's "Smart Access". You
can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html
 
D

Dirk Goldgar

FatMan said:
Hi all:
I need some help working with a SQL Insert Into statement. I have used it
to add a record to a table and replace one field with the desired data,
with
the help of a previous post. To be perfectly honest though I don't
understand it as I don't normally use SQL.

The code I have used to add the record and update the value for field
FileName is:

CurrentDb.Execute ("INSERT INTO tblFileName (FileName) SELECT """ &
Response
& """")

Now I am faced with a much larger task of trying to add a record to table
and update 13 fields with the values stored in variables in my code. The
fields in the table (tblFillingData) and data types are as follows:

Seg01 - text field
Seg02 - text field
Seg03 - text field
Seg04 - text field
Seg05 - number (double)
Seg06 - number (double)
Seg07 - Date/Time (value will always be a time)
Seg08 - Date/Time (value will always be a time)
Seg09 - Date/Time (value will always be a time)
Seg10 - number (double)
Seg11 - text
Seg12 - text
Seg13 - number (long integer)

If someone could please explain the syntax to use the SQL Insert Into
statement to append a record to a table (table: tblFillingData) with the
variables stored in my program I would greatly appreciated it.
Click to expand...


Text and date literals need to be surrounded by the appropriate delimiters
when embedded in a SQL string, while numeric values do not. To simplify
things when coding up SQL statements in VBA code, I like to use this
function:

'----- start of function code -----
Public Function fncSQLLiteral( _
ArgValue As Variant, ValType As Integer) _
As String

' Format the argument value for embedding into an SQL statement,
' complete with the appropriate delimiter characters, according
' to the data type that is passed.
'
' Arguments:
' ArgValue - the value to be formatted (may be Null)
' ValType - one of the DAO DataTypeEnum members, or a number
' value corresponding to them. Only these are
' significant:
' dbText = 10 = text/string/memo data
' dbDate = 8 = date/time data
' dbMemo = 12 = text/string/memo data
' All others values (e.g., dbLong, dbDouble) are
assumed
' to be numeric and need no delimiter.
'
' Copyright (c) 2008, Dirk Goldgar
' License is granted to all to use this code in your applications,
' so long as the copyright notice is maintained.

Select Case ValType

Case dbDate
If Len(ArgValue & vbNullString) = 0 Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = Format(ArgValue, "\#mm/dd/yyyy hh:nn:ss\#")
End If

Case dbText, dbMemo
If IsNull(ArgValue) Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = _
Chr(34) & _
Replace( _
ArgValue, """", """""", , , vbBinaryCompare _
) & _
Chr(34)
End If

Case Else
If Len(ArgValue & vbNullString) = 0 Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = ArgValue
End If

End Select

End Function

'----- end of function code -----

To use this function in your example, you might have something like this:

'----- start of example code -----

' Assume some sort of declarations for variables to hold
' the values to be inserted.
Dim varSeg01, varSeg02, varSeg03, varSeg04, varSeg05
Dim varSeg06, varSeg07, varSeg08, varSeg09, varSeg10
Dim varSeg11, varSeg12, varSeg13

Dim strSQL As String

strSQL = _
"INSERT INTO tblFillingData (" &
"Seg01, Seg02, Seg03, Seg04, Seg05, Seg06, " & _
"Seg07, Seg08, Seg09, Seg10, Seg11, Seg12, Seg13" & _
") VALUES (" & _
fncSQLLiteral(varSeg01 , dbText) & ", " & _
fncSQLLiteral(varSeg02 , dbText) & ", " & _
fncSQLLiteral(varSeg03 , dbText) & ", " & _
fncSQLLiteral(varSeg04 , dbText) & ", " & _
fncSQLLiteral(varSeg05, dbDouble) & ", " & _
fncSQLLiteral(varSeg06, dbDouble) & ", " & _
fncSQLLiteral(varSeg07, dbDate) & ", " & _
fncSQLLiteral(varSeg08, dbDate) & ", " & _
fncSQLLiteral(varSeg09, dbDate) & ", " & _
fncSQLLiteral(varSeg10, dbDouble) & ", " & _
fncSQLLiteral(varSeg11 , dbText) & ", " & _
fncSQLLiteral(varSeg12 , dbText) & ", " & _
fncSQLLiteral(varSeg13, dbLong) & _
")"

CurrentDb.Execute strSQL, dbFailOnError

'----- end of example code -----

You could certainly code up the statement without using the function, but
this helps me keep everything straightforward and be sure that all the text
and date values are properly formatted and delimited.
 
F

FatMan

Doug:
Thank you for your post. I have tried it but of course I am getting a
syntax error. Can you hlep me please.

The error message is:

Run-time error “3075’:
Syntax error in string in query expression ‘082050320101â€, 19)’

Where.....
082050320101 is varValues(11) and should update the field FilUniqueBinNoFormat
19 is intFileID and should update the field FilFilenameID

My Insert Into statement looks like this....

CurrentDb.Execute "INSERT INTO tblFillingData " & _
"(FilTempBinNo, FilUniqueBinNo, FilPhysicalOutlet, FilMainGroupNo,
FilBinWeight, FilNoOfPieces, " & _
"FilStartTime, FilStopTime, FilFillingTime, FilNoOfdays, FilBatchMessage,
FilUniqueBinNoFormat, " & _
"FilFilenameID) " & _
"VALUES (""" & Trim(varValues(0)) & """, """ & Trim(varValues(1)) & """, """
& _
Trim(varValues(2)) & """, """ & Trim(varValues(3)) & """, " &
varValues(4) & ", " & _
varValues(5) & ", " & Format(varValues(6), "\#hh\:nn\:ss\#") &
", " & _
Format(varValues(7), "\#hh\:nn\:ss\#") & ", " & _
Format(varValues(8), "\#hh\:nn\:ss\#") & ", " & _
varValues(9) & ", """ & Trim(varValues(10)) & """, " & _
Trim(varValues(11)) & """, " & intFileID & " )"

I am not sure where the â€, and ) is comming from.

Can you please help me out with my syntax.

Thanking you in advace for your assistance.

Thanks,
FatMan
 
D

Douglas J. Steele

You sure you didn't accidentally put a single quote somewhere in your code?

What's the actual code you're running?
 
F

FatMan

Doug:
I don't think I put a single quote in my code. What I did was cut and paste
your code from the post into my code and simply changed the field and
variable names to match those used in my program. When I comment out the
"Insert Into" statement the code seems to run as my "debug.print" displays
the right data. If I only get the error message when the Insert into
statement is NOT commented out then is it not safe to assume the syntax error
must be in the Insert Into statement?

The "insert into" statement looks like....

CurrentDb.Execute "INSERT INTO tblFillingData " & _
"(FilTempBinNo, FilUniqueBinNo, FilPhysicalOutlet, FilMainGroupNo,
FilBinWeight, FilNoOfPieces, " & _
"FilStartTime, FilStopTime, FilFillingTime, FilNoOfdays, FilBatchMessage,
FilUniqueBinNoFormat, " & _
"FilFilenameID) " & _
"VALUES (""" & Trim(varValues(0)) & """, """ & Trim(varValues(1)) & """,
""" & _
Trim(varValues(2)) & """, """ & Trim(varValues(3)) & """, " &
varValues(4) & ", " & _
varValues(5) & ", " & Format(varValues(6), "\#hh\:nn\:ss\#") & ", " & _
Format(varValues(7), "\#hh\:nn\:ss\#") & ", " & _
Format(varValues(8), "\#hh\:nn\:ss\#") & ", " & _
varValues(9) & ", """ & Trim(varValues(10)) & """, " & _
Trim(varValues(11)) & """, " & intFileID & " )"


Can you please help me find the error in the code?

Your help is greatly appreciated.

Thanks,
FatMan
 
F

FatMan

Doug:
Thank you very much for your help. I found my error after much fraustration
and hair pulling. The error was in the lines below and in deed I was missing
some quotes, it needed to be """ & _ and not " & _. GRRRRRR.....I hate
computers....well not really.

varValues(9) & ", """ & Trim(varValues(10)) & """, " & _
Trim(varValues(11)) & """, " & intFileID & " )"

Thanks for everything,
FatMan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL statement needed 0
SQL INSERT 3
Display bug in Word: tables disappearing on copy and paste of text with bullet points into table 2
Insert into syntax error 2
INSERT INTO gets compile error: Expected end of statement. 3
SQL - insert into 1
Need Help - Insert Into Statement not working but no error codes 7
Variables in a SQL statement 1

Top