Error 3144 - Syntax Error in UPDATE Statement

  • Thread starter Thread starter HRE
  • Start date Start date
H

HRE

I am trying to run an UPDATE sql query in a module. I get the 3144
error but I'm not sure where the error is. Any help would be
appreciated.

Thanks

Function UpdateData()

Dim RS As DAO.Recordset
Dim DB As DAO.Database
Dim qdf As QueryDef
Dim strQryName As String
Dim CtyName As String
Dim FirstLine As Integer
Dim LastLine As Integer
Dim cty As String
Dim strSQL As String

Set DB = CurrentDb
Set RS = CurrentDb.OpenRecordset("T_CountyLineNumbers")

With RS
.MoveFirst
Do While Not .EOF

CtyName = ![County]
FirstLine = ![FirstRow]
LastLine = ![LastRow]

strSQL = "UPDATE T_0002_QFR145RA_Phase_2 SET
(((T_0002_QFR145RA_Phase_2.County)=""" & CtyName & """))" & _
"WHERE (((T_0002_QFR145RA_Phase_2.ID)>=" & FirstLine & ")
And ((T_0002_QFR145RA_Phase_2.ID)<=" & LastLine & "));"

DoCmd.RunSQL strSQL

' Set qdf = DB.CreateQueryDef("qryUpdateData", strSQL)
'
' strQryName = "qryUpdateData"
'
' DB.Execute "qryUpdateData"
'
' DB.QueryDefs.Delete qdf.Name
.MoveNext
Loop
End With

RS.Close
Set DB = Nothing

End Function
 
I am trying to run an UPDATE sql query in a module. I get the 3144
error but I'm not sure where the error is. Any help would be
appreciated.

Thanks

Function UpdateData()

Dim RS As DAO.Recordset
Dim DB As DAO.Database
Dim qdf As QueryDef
Dim strQryName As String
Dim CtyName As String
Dim FirstLine As Integer
Dim LastLine As Integer
Dim cty As String
Dim strSQL As String

Set DB = CurrentDb
Set RS = CurrentDb.OpenRecordset("T_CountyLineNumbers")

With RS
.MoveFirst
Do While Not .EOF

CtyName = ![County]
FirstLine = ![FirstRow]
LastLine = ![LastRow]

strSQL = "UPDATE T_0002_QFR145RA_Phase_2 SET
(((T_0002_QFR145RA_Phase_2.County)=""" & CtyName & """))" & _
"WHERE (((T_0002_QFR145RA_Phase_2.ID)>=" & FirstLine & ")
And ((T_0002_QFR145RA_Phase_2.ID)<=" & LastLine & "));"

DoCmd.RunSQL strSQL

' Set qdf = DB.CreateQueryDef("qryUpdateData", strSQL)
'
' strQryName = "qryUpdateData"
'
' DB.Execute "qryUpdateData"
'
' DB.QueryDefs.Delete qdf.Name
.MoveNext
Loop
End With

RS.Close
Set DB = Nothing

End Function

Issue resolved. Thanks
 
Back
Top