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

 

[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


Code:
--------------------
Option Explicit
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
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

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=XXX;Password=XXXX;Data 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 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("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