Quote Originally Posted by lehainam
Thanks for your help, here is my code

Sub UpdateT6()

'Update T6 based on A1 Group
On Error GoTo ADO_err

Dim str, str1, str2 As String
Dim aqlstring As String
Dim i As Double, X As Double, j As Double
Dim OldStatus
If Trim(ActiveSheet.Name) <> "Update_T6" Then
MsgBox "Active sheet is not valid", vbExclamation, "Notice"
Exit Sub
End If
Sheets("Update_T6").Activate
Application.ScreenUpdating = False
OldStatus = Application.DisplayStatusBar
Application.StatusBar = ActiveSheet.Name & " is running, please wait..."
X = Range("A65536").End(xlUp).Row
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
str = "Provider=SQLOLEDB;User ID=SUN;Password=SUNSYS;Data Source=SUNSYSTEM"
'rs.CursorType = adOpenStatic
'rs.LockType = adLockOptimistic
cn.Open str
sqlstring = "select * from SALFLDG112"
'str1 = "Update SALFLDG112 SET ****_T7=''"
'cn.Execute (str1)
'str2 = "select * from SALFLDG112 where ACCNT_CODE like '22003CIT%'"
'rs.CursorType = adOpenStatic
'rs.LockType = adLockOptimistic
'rs.Open str2, cn
rs.Open sqlstring, cn, adOpenStatic, adLockOptimistic, adCmdText
If rs.EOF = False Then
Do Until rs.EOF = True
For i = 2 To X
If Trim(rs("ACCNT_CODE")) = Trim(Cells(i, 1)) _
And Trim(rs("PERIOD")) = Trim(Cells(i, 2)) _
And Trim(rs("JRNAL_NO")) = Trim(Cells(i, 3)) _
And Trim(rs("JRNAL_LINE")) = Trim(Cells(i, 4)) _
And Trim(rs("TREFERENCE")) = Trim(Cells(i, 7)) Then
rs("****_T6") = Trim(Cells(i, 9))
rs.Update
j = j + 1
End If
Next i
rs.movenext
Loop
End If
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
MsgBox j & " records have been updated", vbExclamation, "Notice by LHN"
ADO_exit:
Application.ScreenUpdating = True
Application.StatusBar = False
Application.DisplayStatusBar = OldStatus
Set rs = Nothing
Set cn = Nothing
Exit Sub
ADO_err:
MsgBox Err.Description
Resume ADO_exit
End Sub


Nam
Apart from the Dim aqlstring As String being mis-spelled ( sqlstring ) nothing I can see, but I cannot open an SQL to check then what happens.

Hopefully someone else can spot this

---