Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
update record in mdb..
.... i have this sheet RATE with a event change code. Is possible to intercept the index in AC when i fill a cell in column M and put the related value into rs of mdb...? here the macro, natuyrally is arranged from old code and naturally not work!!!!: sorry for diemension of file he http://www7.rapidupload.com/d.php?file=dl&filepath=4874 Private Sub Worksheet_Change(ByVal Target As Range) Dim oCell As Range If Intersect(Range("M3:M65536"), Target) Is Nothing Then Exit Sub For Each oCell In Intersect(Range("M3:M65536"), Target) If oCell = "" Then oCell.Offset(0, -1) = "" Cells(oCell.Row, 27) = "" Cells(oCell.Row, 35) = "" Else oCell.Offset(0, -1) = Format(Now, "dd/mm/yyyy") Cells(oCell.Row, 27) = [A1] Cells(oCell.Row, 35) = "M" Call INPS_TOTALE_MASTER End If Next oCell End Sub Global Const gPROVADatabasePath2 = "Data Source=\\GCD01F4500\DATI\PUBBLICA\INPS\STORICO_INP S.MDB;" Sub INPS_TOTALE_MASTER() Sheets("RATE").Select Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long, N As Long Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & gPROVADatabasePath2 Set rs = New ADODB.Recordset rs.Open "INPS_02", cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect rs.INDEX = "PROVA29" r = 3 Do While Len(Range("A" & r).FORMULA) 0 'If Not Trim(Range("L" & r) & Range("M" & r) & Range("AA" & r) & Range("AI" & r)) = "" Then With rs If Not rs.BOF Then rs.MoveFirst End If rs.Seek Array(Range("AC" & r)), adSeekFirstEQ If rs.EOF = True Then ..AddNew ..Fields("PROVA12") = Range("L" & r).Value ..Fields("PROVA13") = Range("M" & r).Value ..Fields("PROVA27") = Range("AA" & r).Value ..Fields("PROVA31") = Range("AI" & r).Value ..Update End If End With 'End If r = r + 1 Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub updating the refred record (match index in Excel range AC and match rs PROVA29) in MDB... ..Fields("PROVA12") = Range("L" & r).Value ..Fields("PROVA13") = Range("M" & r).Value ..Fields("PROVA27") = Range("AA" & r).Value ..Fields("PROVA31") = Range("AI" & r).Value ... in effect the code into change event of sheet RATE write value into column L,M,AA, and AI if i select from a list into column M, after this operation update the related recordset into mdb... -- sal21 ------------------------------------------------------------------------ sal21's Profile: http://www.excelforum.com/member.php...fo&userid=2040 View this thread: http://www.excelforum.com/showthread...hreadid=492140 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
update record in mdb..
Once you retrieve your ADO recordset, you can change a field value in it and
then use the .Update method to update the source db. See this article: http://msdn.microsoft.com/library/de...dmthupdate.asp But note that your recordset must have the correct properties and your database userID/password needs to have the proper permissions to allow updates. -- - K Dales "sal21" wrote: ... i have this sheet RATE with a event change code. Is possible to intercept the index in AC when i fill a cell in column M and put the related value into rs of mdb...? here the macro, natuyrally is arranged from old code and naturally not work!!!!: sorry for diemension of file he http://www7.rapidupload.com/d.php?file=dl&filepath=4874 Private Sub Worksheet_Change(ByVal Target As Range) Dim oCell As Range If Intersect(Range("M3:M65536"), Target) Is Nothing Then Exit Sub For Each oCell In Intersect(Range("M3:M65536"), Target) If oCell = "" Then oCell.Offset(0, -1) = "" Cells(oCell.Row, 27) = "" Cells(oCell.Row, 35) = "" Else oCell.Offset(0, -1) = Format(Now, "dd/mm/yyyy") Cells(oCell.Row, 27) = [A1] Cells(oCell.Row, 35) = "M" Call INPS_TOTALE_MASTER End If Next oCell End Sub Global Const gPROVADatabasePath2 = "Data Source=\\GCD01F4500\DATI\PUBBLICA\INPS\STORICO_INP S.MDB;" Sub INPS_TOTALE_MASTER() Sheets("RATE").Select Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long, N As Long Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & gPROVADatabasePath2 Set rs = New ADODB.Recordset rs.Open "INPS_02", cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect rs.INDEX = "PROVA29" r = 3 Do While Len(Range("A" & r).FORMULA) 0 'If Not Trim(Range("L" & r) & Range("M" & r) & Range("AA" & r) & Range("AI" & r)) = "" Then With rs If Not rs.BOF Then rs.MoveFirst End If rs.Seek Array(Range("AC" & r)), adSeekFirstEQ If rs.EOF = True Then .AddNew .Fields("PROVA12") = Range("L" & r).Value .Fields("PROVA13") = Range("M" & r).Value .Fields("PROVA27") = Range("AA" & r).Value .Fields("PROVA31") = Range("AI" & r).Value .Update End If End With 'End If r = r + 1 Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub updating the refred record (match index in Excel range AC and match rs PROVA29) in MDB... .Fields("PROVA12") = Range("L" & r).Value .Fields("PROVA13") = Range("M" & r).Value .Fields("PROVA27") = Range("AA" & r).Value .Fields("PROVA31") = Range("AI" & r).Value ... in effect the code into change event of sheet RATE write value into column L,M,AA, and AI if i select from a list into column M, after this operation update the related recordset into mdb... -- sal21 ------------------------------------------------------------------------ sal21's Profile: http://www.excelforum.com/member.php...fo&userid=2040 View this thread: http://www.excelforum.com/showthread...hreadid=492140 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
record update VBA form | Excel Discussion (Misc queries) | |||
Record update date in cell | Excel Programming | |||
On Duplicate, update record | Excel Discussion (Misc queries) | |||
Retrieve and update 1 record at a time | Excel Programming | |||
Code to Update a Record | Excel Programming |