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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Update Help!
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_proppert y_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 -------------------- -- Dnereb ------------------------------------------------------------------------ Dnereb's Profile: http://www.excelforum.com/member.php...o&userid=26182 View this thread: http://www.excelforum.com/showthread...hreadid=399711 |
Reply |
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 |