Parameters, Passthrough UpdateQueries, and SQL

  • Thread starter Thread starter jmillerWV
  • Start date Start date
J

jmillerWV

Hello All,
Have a SQL DB (MySQL), a table(tblUPL), a column in the table(CompanyName)
An Access2003 Frontend (OurWork.mdb). A form (ClntName) a field inside the
form (CoName), A passthrough Query (PTNameChng).

the query was orginally and Access Update query that changed
table!tblUPL!CompanyName to the current value of Form![clntName]![CoName].
when I change it to a passthrough query and run it, I get the error
"ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: incorrect syntax near
'!'. (#170)."

I have tried changing the syntax to single quote marks , double quote marks,
% sign, Added brackets, added (), peroids between the form designators
nothing helped. What do I need to do to get this to work?
 
Thanks for the reply. I have tried your suggestion, but I get the error
message "Cannot execute a select query." I have attached the code just as it
is.
Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim db As DAO.Database, qd As DAO.QueryDef

Set db = CurrentDb
Set qd = db.QueryDefs("PassThruTemplate")

With qd
.SQL = "UPDATE UPLPricing" & _
"SET CompanyName = '" & Me!CoName & "'" & _
"where RecNum = " & Me!coid
.Execute dbFailOnError
End With

Set qd = Nothing
Set db = Nothing

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub


UPLPricing is the actual name of the table all other field names are correct.
I assume that the CoID is the record number of each item in the UPLPricing
table? If so I created a field on my form called coid with the final number
of the list I want to update. Where have I gone wrong? Why is the error
message calling for a secelct query?

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

MySQL doesn't recognize the reference to the form's control. You have
to send the actual value of the control. E.g. (Use a VBA routine
attached to a CommandButton):

Private Sub cmdRunPassThru_Click()

dim db as dao.database, qd as dao.querydef

set db = currentdb
' PassThruTemplate is the name of your pass-thru query
set qd = db.querydefs("PassThruTemplate")

with qd

' Assumes there is a control named CoID on form
' put your Update statement here

.SQL = "UPDATE tblUPL " & _
"SET CompanyName = '" & Me!CoName & "'" & _
"WHERE CoID = " & Me!CoID

.execute dbfailonerror

end with

set qd = nothing
set db = nothing

' set up an error handler

End Sub

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqmUVYechKqOuFEgEQIMQQCfSZmXXB6LbSbc4dxbnwPVJqt6gwcAoOQw
C9uO/9ZLELjiLm8fLagTIVOX
=hHqp
-----END PGP SIGNATURE-----
Hello All,
Have a SQL DB (MySQL), a table(tblUPL), a column in the table(CompanyName)
An Access2003 Frontend (OurWork.mdb). A form (ClntName) a field inside the
form (CoName), A passthrough Query (PTNameChng).

the query was orginally and Access Update query that changed
table!tblUPL!CompanyName to the current value of Form![clntName]![CoName].
when I change it to a passthrough query and run it, I get the error
"ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: incorrect syntax near
'!'. (#170)."

I have tried changing the syntax to single quote marks , double quote marks,
% sign, Added brackets, added (), peroids between the form designators
nothing helped. What do I need to do to get this to work?
Click to expand...
Click to expand...
 
I forgot to add that I changed the name of the query to your example, I was
getting all kinds of errors before that.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

MySQL doesn't recognize the reference to the form's control. You have
to send the actual value of the control. E.g. (Use a VBA routine
attached to a CommandButton):

Private Sub cmdRunPassThru_Click()

dim db as dao.database, qd as dao.querydef

set db = currentdb
' PassThruTemplate is the name of your pass-thru query
set qd = db.querydefs("PassThruTemplate")

with qd

' Assumes there is a control named CoID on form
' put your Update statement here

.SQL = "UPDATE tblUPL " & _
"SET CompanyName = '" & Me!CoName & "'" & _
"WHERE CoID = " & Me!CoID

.execute dbfailonerror

end with

set qd = nothing
set db = nothing

' set up an error handler

End Sub

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqmUVYechKqOuFEgEQIMQQCfSZmXXB6LbSbc4dxbnwPVJqt6gwcAoOQw
C9uO/9ZLELjiLm8fLagTIVOX
=hHqp
-----END PGP SIGNATURE-----
Hello All,
Have a SQL DB (MySQL), a table(tblUPL), a column in the table(CompanyName)
An Access2003 Frontend (OurWork.mdb). A form (ClntName) a field inside the
form (CoName), A passthrough Query (PTNameChng).

the query was orginally and Access Update query that changed
table!tblUPL!CompanyName to the current value of Form![clntName]![CoName].
when I change it to a passthrough query and run it, I get the error
"ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: incorrect syntax near
'!'. (#170)."

I have tried changing the syntax to single quote marks , double quote marks,
% sign, Added brackets, added (), peroids between the form designators
nothing helped. What do I need to do to get this to work?
Click to expand...
Click to expand...
 
You have left out spaces in the SQL statement before the word SET and the word
WHERE. So your SQL reads something like
UPDATE UPLPricingSET CompanyName = 'xxx'WHERE RecNum= 123

.SQL = "UPDATE UPLPricing" & _
" SET CompanyName = '" & Me!CoName & "'" & _
" WHERE RecNum = " & Me!coid


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks for the reply. I have tried your suggestion, but I get the error
message "Cannot execute a select query." I have attached the code just as it
is.
Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim db As DAO.Database, qd As DAO.QueryDef

Set db = CurrentDb
Set qd = db.QueryDefs("PassThruTemplate")

With qd
.SQL = "UPDATE UPLPricing" & _
"SET CompanyName = '" & Me!CoName & "'" & _
"where RecNum = " & Me!coid
.Execute dbFailOnError
End With

Set qd = Nothing
Set db = Nothing

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub


UPLPricing is the actual name of the table all other field names are correct.
I assume that the CoID is the record number of each item in the UPLPricing
table? If so I created a field on my form called coid with the final number
of the list I want to update. Where have I gone wrong? Why is the error
message calling for a secelct query?

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

MySQL doesn't recognize the reference to the form's control. You have
to send the actual value of the control. E.g. (Use a VBA routine
attached to a CommandButton):

Private Sub cmdRunPassThru_Click()

dim db as dao.database, qd as dao.querydef

set db = currentdb
' PassThruTemplate is the name of your pass-thru query
set qd = db.querydefs("PassThruTemplate")

with qd

' Assumes there is a control named CoID on form
' put your Update statement here

.SQL = "UPDATE tblUPL " & _
"SET CompanyName = '" & Me!CoName & "'" & _
"WHERE CoID = " & Me!CoID

.execute dbfailonerror

end with

set qd = nothing
set db = nothing

' set up an error handler

End Sub

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqmUVYechKqOuFEgEQIMQQCfSZmXXB6LbSbc4dxbnwPVJqt6gwcAoOQw
C9uO/9ZLELjiLm8fLagTIVOX
=hHqp
-----END PGP SIGNATURE-----
Hello All,
Have a SQL DB (MySQL), a table(tblUPL), a column in the table(CompanyName)
An Access2003 Frontend (OurWork.mdb). A form (ClntName) a field inside the
form (CoName), A passthrough Query (PTNameChng).

the query was orginally and Access Update query that changed
table!tblUPL!CompanyName to the current value of Form![clntName]![CoName].
when I change it to a passthrough query and run it, I get the error
"ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: incorrect syntax near
'!'. (#170)."

I have tried changing the syntax to single quote marks , double quote marks,
% sign, Added brackets, added (), peroids between the form designators
nothing helped. What do I need to do to get this to work?
Click to expand...
Click to expand...
Click to expand...
 
John thanks for the reply. I have placed the spaces per your instructions and
am still getting the "Cannot execute a select query". Does it make a
difference if the front end is an Access .mdb file vs and Access .adp file? I
am using the.mdb file. What bearing does the " Where ... "phrase have on the
query. The table I am updating has 63 line items (it is a standard price list
for our business) we create a single price list for each client, as prices
for that client may change if volume rises.
I need to be able to change the company name so that it can be moved to our
main pricing list. Which is used to price out the clients work. The field
RecNum is the line item number for each county in our state. The table is
laid out RecNum, CompanyName, County, State then 25 to 30 columns for the
type of work performed. I hope this didn't confuse it was meant to help.
Again thank you and all for your assistance in this problem.

John Spencer said:
You have left out spaces in the SQL statement before the word SET and the word
WHERE. So your SQL reads something like
UPDATE UPLPricingSET CompanyName = 'xxx'WHERE RecNum= 123

.SQL = "UPDATE UPLPricing" & _
" SET CompanyName = '" & Me!CoName & "'" & _
" WHERE RecNum = " & Me!coid


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks for the reply. I have tried your suggestion, but I get the error
message "Cannot execute a select query." I have attached the code just as it
is.
Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim db As DAO.Database, qd As DAO.QueryDef

Set db = CurrentDb
Set qd = db.QueryDefs("PassThruTemplate")

With qd
.SQL = "UPDATE UPLPricing" & _
"SET CompanyName = '" & Me!CoName & "'" & _
"where RecNum = " & Me!coid
.Execute dbFailOnError
End With

Set qd = Nothing
Set db = Nothing

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub


UPLPricing is the actual name of the table all other field names are correct.
I assume that the CoID is the record number of each item in the UPLPricing
table? If so I created a field on my form called coid with the final number
of the list I want to update. Where have I gone wrong? Why is the error
message calling for a secelct query?

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

MySQL doesn't recognize the reference to the form's control. You have
to send the actual value of the control. E.g. (Use a VBA routine
attached to a CommandButton):

Private Sub cmdRunPassThru_Click()

dim db as dao.database, qd as dao.querydef

set db = currentdb
' PassThruTemplate is the name of your pass-thru query
set qd = db.querydefs("PassThruTemplate")

with qd

' Assumes there is a control named CoID on form
' put your Update statement here

.SQL = "UPDATE tblUPL " & _
"SET CompanyName = '" & Me!CoName & "'" & _
"WHERE CoID = " & Me!CoID

.execute dbfailonerror

end with

set qd = nothing
set db = nothing

' set up an error handler

End Sub

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqmUVYechKqOuFEgEQIMQQCfSZmXXB6LbSbc4dxbnwPVJqt6gwcAoOQw
C9uO/9ZLELjiLm8fLagTIVOX
=hHqp
-----END PGP SIGNATURE-----

jmillerWV wrote:
Hello All,
Have a SQL DB (MySQL), a table(tblUPL), a column in the table(CompanyName)
An Access2003 Frontend (OurWork.mdb). A form (ClntName) a field inside the
form (CoName), A passthrough Query (PTNameChng).

the query was orginally and Access Update query that changed
table!tblUPL!CompanyName to the current value of Form![clntName]![CoName].
when I change it to a passthrough query and run it, I get the error
"ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: incorrect syntax near
'!'. (#170)."

I have tried changing the syntax to single quote marks , double quote marks,
% sign, Added brackets, added (), peroids between the form designators
nothing helped. What do I need to do to get this to work?
Click to expand...
Click to expand...
Click to expand...
 
Strange. This is NOT a select query.

And if you are working with a linked table, I would not bother with a
pass-through query for a small amount of records. I would just use
Dim strSQL as String
strSQL = "UPDATE UPLPricing" & _
" SET CompanyName = '" & Me.CoName & "'" & _
" WHERE RecNum = " & Me.coid

Db.Execute strSQL ,dbFailOnError

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John thanks for the reply. I have placed the spaces per your instructions and
am still getting the "Cannot execute a select query". Does it make a
difference if the front end is an Access .mdb file vs and Access .adp file? I
am using the.mdb file. What bearing does the " Where ... "phrase have on the
query. The table I am updating has 63 line items (it is a standard price list
for our business) we create a single price list for each client, as prices
for that client may change if volume rises.
I need to be able to change the company name so that it can be moved to our
main pricing list. Which is used to price out the clients work. The field
RecNum is the line item number for each county in our state. The table is
laid out RecNum, CompanyName, County, State then 25 to 30 columns for the
type of work performed. I hope this didn't confuse it was meant to help.
Again thank you and all for your assistance in this problem.

John Spencer said:
You have left out spaces in the SQL statement before the word SET and the word
WHERE. So your SQL reads something like
UPDATE UPLPricingSET CompanyName = 'xxx'WHERE RecNum= 123

.SQL = "UPDATE UPLPricing" & _
" SET CompanyName = '" & Me!CoName & "'" & _
" WHERE RecNum = " & Me!coid


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks for the reply. I have tried your suggestion, but I get the error
message "Cannot execute a select query." I have attached the code just as it
is.
Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim db As DAO.Database, qd As DAO.QueryDef

Set db = CurrentDb
Set qd = db.QueryDefs("PassThruTemplate")

With qd
.SQL = "UPDATE UPLPricing" & _
"SET CompanyName = '" & Me!CoName & "'" & _
"where RecNum = " & Me!coid
.Execute dbFailOnError
End With

Set qd = Nothing
Set db = Nothing

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub


UPLPricing is the actual name of the table all other field names are correct.
I assume that the CoID is the record number of each item in the UPLPricing
table? If so I created a field on my form called coid with the final number
of the list I want to update. Where have I gone wrong? Why is the error
message calling for a secelct query?

:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

MySQL doesn't recognize the reference to the form's control. You have
to send the actual value of the control. E.g. (Use a VBA routine
attached to a CommandButton):

Private Sub cmdRunPassThru_Click()

dim db as dao.database, qd as dao.querydef

set db = currentdb
' PassThruTemplate is the name of your pass-thru query
set qd = db.querydefs("PassThruTemplate")

with qd

' Assumes there is a control named CoID on form
' put your Update statement here

.SQL = "UPDATE tblUPL " & _
"SET CompanyName = '" & Me!CoName & "'" & _
"WHERE CoID = " & Me!CoID

.execute dbfailonerror

end with

set qd = nothing
set db = nothing

' set up an error handler

End Sub

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqmUVYechKqOuFEgEQIMQQCfSZmXXB6LbSbc4dxbnwPVJqt6gwcAoOQw
C9uO/9ZLELjiLm8fLagTIVOX
=hHqp
-----END PGP SIGNATURE-----

jmillerWV wrote:
Hello All,
Have a SQL DB (MySQL), a table(tblUPL), a column in the table(CompanyName)
An Access2003 Frontend (OurWork.mdb). A form (ClntName) a field inside the
form (CoName), A passthrough Query (PTNameChng).

the query was orginally and Access Update query that changed
table!tblUPL!CompanyName to the current value of Form![clntName]![CoName].
when I change it to a passthrough query and run it, I get the error
"ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: incorrect syntax near
'!'. (#170)."

I have tried changing the syntax to single quote marks , double quote marks,
% sign, Added brackets, added (), peroids between the form designators
nothing helped. What do I need to do to get this to work?
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the reply. guess i am trying to make it more diffucult than it
needs to be.

John Spencer said:
Strange. This is NOT a select query.

And if you are working with a linked table, I would not bother with a
pass-through query for a small amount of records. I would just use
Dim strSQL as String
strSQL = "UPDATE UPLPricing" & _
" SET CompanyName = '" & Me.CoName & "'" & _
" WHERE RecNum = " & Me.coid

Db.Execute strSQL ,dbFailOnError

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John thanks for the reply. I have placed the spaces per your instructions and
am still getting the "Cannot execute a select query". Does it make a
difference if the front end is an Access .mdb file vs and Access .adp file? I
am using the.mdb file. What bearing does the " Where ... "phrase have on the
query. The table I am updating has 63 line items (it is a standard price list
for our business) we create a single price list for each client, as prices
for that client may change if volume rises.
I need to be able to change the company name so that it can be moved to our
main pricing list. Which is used to price out the clients work. The field
RecNum is the line item number for each county in our state. The table is
laid out RecNum, CompanyName, County, State then 25 to 30 columns for the
type of work performed. I hope this didn't confuse it was meant to help.
Again thank you and all for your assistance in this problem.

John Spencer said:
You have left out spaces in the SQL statement before the word SET and the word
WHERE. So your SQL reads something like
UPDATE UPLPricingSET CompanyName = 'xxx'WHERE RecNum= 123

.SQL = "UPDATE UPLPricing" & _
" SET CompanyName = '" & Me!CoName & "'" & _
" WHERE RecNum = " & Me!coid


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

jmillerWV wrote:
Thanks for the reply. I have tried your suggestion, but I get the error
message "Cannot execute a select query." I have attached the code just as it
is.
Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim db As DAO.Database, qd As DAO.QueryDef

Set db = CurrentDb
Set qd = db.QueryDefs("PassThruTemplate")

With qd
.SQL = "UPDATE UPLPricing" & _
"SET CompanyName = '" & Me!CoName & "'" & _
"where RecNum = " & Me!coid
.Execute dbFailOnError
End With

Set qd = Nothing
Set db = Nothing

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub


UPLPricing is the actual name of the table all other field names are correct.
I assume that the CoID is the record number of each item in the UPLPricing
table? If so I created a field on my form called coid with the final number
of the list I want to update. Where have I gone wrong? Why is the error
message calling for a secelct query?

:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

MySQL doesn't recognize the reference to the form's control. You have
to send the actual value of the control. E.g. (Use a VBA routine
attached to a CommandButton):

Private Sub cmdRunPassThru_Click()

dim db as dao.database, qd as dao.querydef

set db = currentdb
' PassThruTemplate is the name of your pass-thru query
set qd = db.querydefs("PassThruTemplate")

with qd

' Assumes there is a control named CoID on form
' put your Update statement here

.SQL = "UPDATE tblUPL " & _
"SET CompanyName = '" & Me!CoName & "'" & _
"WHERE CoID = " & Me!CoID

.execute dbfailonerror

end with

set qd = nothing
set db = nothing

' set up an error handler

End Sub

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqmUVYechKqOuFEgEQIMQQCfSZmXXB6LbSbc4dxbnwPVJqt6gwcAoOQw
C9uO/9ZLELjiLm8fLagTIVOX
=hHqp
-----END PGP SIGNATURE-----

jmillerWV wrote:
Hello All,
Have a SQL DB (MySQL), a table(tblUPL), a column in the table(CompanyName)
An Access2003 Frontend (OurWork.mdb). A form (ClntName) a field inside the
form (CoName), A passthrough Query (PTNameChng).

the query was orginally and Access Update query that changed
table!tblUPL!CompanyName to the current value of Form![clntName]![CoName].
when I change it to a passthrough query and run it, I get the error
"ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: incorrect syntax near
'!'. (#170)."

I have tried changing the syntax to single quote marks , double quote marks,
% sign, Added brackets, added (), peroids between the form designators
nothing helped. What do I need to do to get this to work?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top