ADO Update Help!

[lehainam]

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, please 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;Data Source=SUNSYSTEM"
'rs.CursorType = adOpenStatic
'rs.LockType = adLockOptimistic
cn.Open str

'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
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("****_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 of acceptable range, or are in conflict with one another'

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

Could any one can help me.

Thanks,

Nam

[Dnereb]

REMOVE YOUR ID AND PASSWORD FROM YOUR POST!!


You have to declare the type of an object instead of using variants this way more error checking can be done, propperties are availeble and wrong assignments won't happen.
Something you should now: If a object has a default propperty this propperty is assigned to a variant not the object
This should resolve your problem

Please Login or Register  to view this content.