Error 3144 - Syntax Error in UPDATE Statement

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
 
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

Issue resolved. Thanks
 

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

Top