Dear all,

I am using the below code to update Data in SQL Server:

Sub UpdateT1()

'Update T7 based on T3
On Error GoTo ADO_err

Dim str, str1, str2 As String
Dim i, x As Double
Dim OldStatus
Sheets("Update").Activate
Application.ScreenUpdating = False
OldStatus = Application.DisplayStatusBar
Application.StatusBar = ActiveSheet.Name & " is running, pleas
wait..."
x = Range("A65536").End(xlUp).Row
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
str = "Provider=SQLOLEDB;User ID=LAS;Password=lasata;Dat
Source=SUNSYSTEM"
'rs.CursorType = adOpenStatic
'rs.LockType = adLockOptimistic
cn.Open str

'str1 = "Update SALFLDG112 SET ANAL_T7=''"
'cn.Execute (str1)
str2 = "select * from SALFLDG112 where ACCNT_CODE lik
'22003CIT%'"
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.Open str2, cn
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("ANAL_T1") = Trim(Cells(i, 10))
rs.Update
End If
Next i
rs.movenext
Loop
End If
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
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

However, there is an error : 'Arguments are of wrong type, are out o
acceptable range, or are in conflict with one another'

I have check many times that error comes from my rs.LockType. If
don't use LockType it runs but can not update.

Could any one can help me.

Thanks,

Na

--
lehaina
-----------------------------------------------------------------------
lehainam's Profile: http://www.excelforum.com/member.php...fo&userid=1861
View this thread: http://www.excelforum.com/showthread.php?threadid=39971