Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Update Help!
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update one worksheet tab to automatically update all other tabs? | Excel Worksheet Functions | |||
Data Validation lists update orginal cell with list update | Excel Worksheet Functions | |||
i want to update one excel file the other one update automaticaly | New Users to Excel | |||
Excel 2003 - Update or Don't Update Links Problem | Excel Programming | |||
how to update data on sheet1 and have it auto update on sheet2 | Excel Programming |