Syntax 101 for SQL String

[Guest]

I am attempting to update fields from a form to a table, but I am making a
basic error.

This is my code:

Dim SQL As String

SQL = "UPDATE TCURRENT " & _
"SET CurrentLabRate = Forms.[FESC].Rate " & _
"WHERE TCURRENT.SMInvID = Forms.[FESC].SM Inv ID"

DoCmd.RunSQL SQL

I get an error message:

Syntax error (missing operator) in querry expresion 'TCURRENT.SMInvID =
Forms.[FESC].SM Inv ID'

What am i doing wrong?

Thank you

 

[Guest]

This is one of the reasons you should never use spaces in any names. When
you use spaces, special characters, or reserved words, you have to bracket
the name so it will be understood.
Also, using a period after forms means what follows is a method or property.
Using the Exclamation point means it is a user created object.
And, it is always best to include the closing semi-colon in SQL statements

"WHERE TCURRENT.SMInvID = [Forms]![FESC]![SM Inv ID];"

To conclude, I recommend using the Execute method of CurrentDb object rather
than the RunSQL. It is a whole lot faster. It does the same thing except
that with the Execute, it doesn't pass through the Access UI, it goes
directly to Jet.

 

[Dirk Goldgar]

This is one of the reasons you should never use spaces in any names.
When you use spaces, special characters, or reserved words, you have
to bracket the name so it will be understood.
Also, using a period after forms means what follows is a method or
property. Using the Exclamation point means it is a user created
object.
And, it is always best to include the closing semi-colon in SQL
statements

"WHERE TCURRENT.SMInvID = [Forms]![FESC]![SM Inv ID];"

To conclude, I recommend using the Execute method of CurrentDb object
rather than the RunSQL. It is a whole lot faster. It does the same
thing except that with the Execute, it doesn't pass through the
Access UI, it goes directly to Jet.

I agree, but then it will be necessary to put the form-control
references outside the quotes:

SQL = "UPDATE TCURRENT " & _
"SET CurrentLabRate = " & Forms.[FESC].Rate & _
" WHERE TCURRENT.SMInvID = " & Forms.[FESC].SM Inv ID

CurrentDb.Execute SQL, dbFailOnError

That's assuming both of those fields are numeric; if they are text, we
need to put quotes around their values *inside* the SQL string.

 

[Guest]

I get the message about using proper conventions.

There are actually 3 fields that I need to update and using RunSQL give me 3
warning messages.

Can you give a brief sample of how the execute method would look for
updating 3 fields?

Thank you

 

[Guest]

Oh, what's a little syntax error amongst friends
Thanks Dirk

This is one of the reasons you should never use spaces in any names.
When you use spaces, special characters, or reserved words, you have
to bracket the name so it will be understood.
Also, using a period after forms means what follows is a method or
property. Using the Exclamation point means it is a user created
object.
And, it is always best to include the closing semi-colon in SQL
statements

"WHERE TCURRENT.SMInvID = [Forms]![FESC]![SM Inv ID];"

To conclude, I recommend using the Execute method of CurrentDb object
rather than the RunSQL. It is a whole lot faster. It does the same
thing except that with the Execute, it doesn't pass through the
Access UI, it goes directly to Jet.

I agree, but then it will be necessary to put the form-control
references outside the quotes:

SQL = "UPDATE TCURRENT " & _
"SET CurrentLabRate = " & Forms.[FESC].Rate & _
" WHERE TCURRENT.SMInvID = " & Forms.[FESC].SM Inv ID

CurrentDb.Execute SQL, dbFailOnError

That's assuming both of those fields are numeric; if they are text, we
need to put quotes around their values *inside* the SQL string.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

SQL = "UPDATE TCURRENT " & _
"SET CurrentLabRate = " & Forms!FESC!Rate, " & _
"FIELD_2 = '" & Forms!FESC!txtField2, & ";" & _
"FIELD_3 = " & Forms!FESC!tstField3 " & _
"WHERE TCURRENT.SMInvID = " & Forms!FESC![SM Inv ID] & ";"

For example purposes, FIELD_2 is a text field. notice the quotes that
surround the value being passed.

 

[Michel Walsh]

Hi,


Or you keep DoCmd AND you keep the FORMS!formName!ControlName INSIDE the
string (as in your original post), but with [ ] around the ill formed
names.

DoCmd solves for you the FORMS!FormName!ControlName syntax, while
CurrentDb DOES NOT. So DoCmd is safer if you have to deal with possible
different delimiters/dateTime format setting... because you don't have to
worry about having, or not, a " in the value used as argument; having a US
dateTime format, or something else; a coma, or a dot as decimal operator,
etc. So, in the end, DoCmd can be 1/1000 of a second slower in execution,
but avoid a large span of possible errors.



Vanderghast, Access MVP

 

[Guest]

The performance difference is much greater than that. If you are working
with a very small recordset, okay, but with a recordset of any significant
size, the Execute is 5 to 7 times faster.

Hi,


Or you keep DoCmd AND you keep the FORMS!formName!ControlName INSIDE the
string (as in your original post), but with [ ] around the ill formed
names.

DoCmd solves for you the FORMS!FormName!ControlName syntax, while
CurrentDb DOES NOT. So DoCmd is safer if you have to deal with possible
different delimiters/dateTime format setting... because you don't have to
worry about having, or not, a " in the value used as argument; having a US
dateTime format, or something else; a coma, or a dot as decimal operator,
etc. So, in the end, DoCmd can be 1/1000 of a second slower in execution,
but avoid a large span of possible errors.



Vanderghast, Access MVP

I get the message about using proper conventions.

There are actually 3 fields that I need to update and using RunSQL give me
3
warning messages.

Can you give a brief sample of how the execute method would look for
updating 3 fields?

Thank you

 

[Dirk Goldgar]

Hi,


Or you keep DoCmd AND you keep the FORMS!formName!ControlName INSIDE
the string (as in your original post), but with [ ] around the ill
formed names.

DoCmd solves for you the FORMS!FormName!ControlName syntax, while
CurrentDb DOES NOT. So DoCmd is safer if you have to deal with
possible different delimiters/dateTime format setting... because you
don't have to worry about having, or not, a " in the value used as
argument; having a US dateTime format, or something else; a coma, or
a dot as decimal operator, etc. So, in the end, DoCmd can be 1/1000
of a second slower in execution, but avoid a large span of possible
errors.

On the down side, RunSQL produces warnings, unless I turn them off --
and then I have to be sure to turn them on again. And I can't get the
number records affected by the action query when I use RunSQL.

 

[Michel Walsh]

Hi,


Updating 1 000 000 records leave me with a difference of .8 sec, and that
includes the two SetWarnings:


*********************


=============
Private Type LARGE_INTEGER
lowpart As Long
highpart As Long
End Type

Private Declare Function QueryPerformanceCounter Lib "kernel32"
(lpPerformanceCount As LARGE_INTEGER) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32"
(lpFrequency As LARGE_INTEGER) As Long

Private Declare Function apiPlaySound Lib "Winmm.dll" Alias "sndPlaySoundA"
(ByVal lpszSoundName As String, ByVal uFlags As Long) As Long
---------------------------------
Public Sub TestRunSQLvsCurrentDB()
Dim freq As LARGE_INTEGER
Dim starting As LARGE_INTEGER
Dim Ending As LARGE_INTEGER
Dim dfreq As Variant

QueryPerformanceFrequency freq
dfreq = LargeToDec(freq)


QueryPerformanceCounter starting
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE iotas SET iotas.iota=iota WHERE iota <=
FORMS!Form51.Text0"
DoCmd.SetWarnings True
QueryPerformanceCounter Ending
Debug.Print "RunSQL: ", (LargeToDec(Ending) - LargeToDec(starting)) /
dfreq

QueryPerformanceCounter starting
CurrentDb.Execute "UPDATE iotas SET iotas.iota=iota WHERE iota <=" &
Forms!Form51.Text0
QueryPerformanceCounter Ending
Debug.Print "CurrentDb.Execute", (LargeToDec(Ending) -
LargeToDec(starting)) / dfreq

End Sub
---------------------
Public Function LargeToDec(Arg As LARGE_INTEGER) As Variant

Dim temp As Variant
temp = 4 * CDec(1073741824) ' 2 ^ 32

If Arg.lowpart > 0 Then
LargeToDec = Arg.lowpart + Arg.highpart * temp
Else
LargeToDec = temp + Arg.lowpart + Arg.highpart * temp
End If

End Function
==================



in the Immediate Debug window:

================== for 1000000 records
TestRunSQLvsCurrentDB
RunSQL: 3.775162512662106224128659505
CurrentDb.Execute 2.9424456694554564819562380935
==================

sor for 1000 records, the difference won't be really human perceptible, and
per record, the difference is in the order or of 10E-3 sec.

================= for 1 record
TestRunSQLvsCurrentDB
RunSQL: 0.0052739175201864195523763074
CurrentDb.Execute 0.0020895680919091746599468921
=================




*************************



Sure, that takes, in this last case, more than twice the amount of time, but
it is not-perceptible, for the end user. When difference of speed is so low
and so not perceptible to the end user, other criteria, like maintenance,
has to kick in, and clearly, DoCmd does a better job, not only it simplifies
the formulation, but it also takes care of international problems the date
format can bring, and also takes care of the presence of absence of the
delimiter in an text argument. As example, if your criteria is based on a
string, do you really check if ' or " is already present in the argument.
You don't if you simply write:

"... WHERE stringField =""" & FORMS!formName!ControlName & """"


which will break if the control get a value like: 6"



Vanderghast, Access MVP

The performance difference is much greater than that. If you are working
with a very small recordset, okay, but with a recordset of any significant
size, the Execute is 5 to 7 times faster.

Hi,


Or you keep DoCmd AND you keep the FORMS!formName!ControlName INSIDE the
string (as in your original post), but with [ ] around the ill formed
names.

DoCmd solves for you the FORMS!FormName!ControlName syntax, while
CurrentDb DOES NOT. So DoCmd is safer if you have to deal with possible
different delimiters/dateTime format setting... because you don't have to
worry about having, or not, a " in the value used as argument; having a
US
dateTime format, or something else; a coma, or a dot as decimal operator,
etc. So, in the end, DoCmd can be 1/1000 of a second slower in execution,
but avoid a large span of possible errors.



Vanderghast, Access MVP

I get the message about using proper conventions.

There are actually 3 fields that I need to update and using RunSQL give
me
3
warning messages.

Can you give a brief sample of how the execute method would look for
updating 3 fields?

Thank you

 

[Michel Walsh]

Hi,



About Setting the Warnings, does it compares with having to use Replace when
the criteria is a string? to use Format( ..., "mm/dd/yyyy" ) when it is a
date? Ok for the date, if the application does not have to live outside US
(I mean, Canadians, do not use it, neither the rest of the world), but for
strings:

"... WHERE stringField='" & FORMS!FormName!ControlName & "'"


breaks if the control hold a value like Joe's Coffee


Furthermore, if you forgot the SetWarnings, they hit you first time you ever
run the statement, while the ' will hurt ONLY once you get an ' in the
tests you make, if you think to include such string in the test.



About not having the number of affected records, that, I agree.



Vanderghast, Access MVP

Hi,


Or you keep DoCmd AND you keep the FORMS!formName!ControlName INSIDE
the string (as in your original post), but with [ ] around the ill
formed names.

DoCmd solves for you the FORMS!FormName!ControlName syntax, while
CurrentDb DOES NOT. So DoCmd is safer if you have to deal with
possible different delimiters/dateTime format setting... because you
don't have to worry about having, or not, a " in the value used as
argument; having a US dateTime format, or something else; a coma, or
a dot as decimal operator, etc. So, in the end, DoCmd can be 1/1000
of a second slower in execution, but avoid a large span of possible
errors.

On the down side, RunSQL produces warnings, unless I turn them off --
and then I have to be sure to turn them on again. And I can't get the
number records affected by the action query when I use RunSQL.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Michel Walsh]

Hi,


and DoCmd also handle nicely the case where there is nothing in the control,
while CurrentDb approach will ... got an error of syntax!


Vanderghast, Access MVP