update record in mdb..

[sal21]

.... 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 here:
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_INPS.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...

 

[Guest]

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/default.asp?url=/library/en-us/ado270/htm/mdmthupdate.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

... 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 here:
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_INPS.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...