Updating records in Access from Excel

[flowergirl]

I have the following structure in Access

[vba]EmpID EmpName Dimension Evaluator1 ..Evaluator10 Target1...Target10

Appraiser_Comments1..Appraiser_Comments10

3857 Sutapa Financial
3857 Sutapa Customer

I am using the following code to update row1 in Access

Sub appraiser_view_gen_month_save_1a()
Dim dbConnection As ADODB.Connection
Dim dbFileName As String
Dim dbRecordset As New ADODB.Recordset
Dim dbRecordset1 As New ADODB.Recordset
Dim xRow As Long, xColumn As Long
Dim LastRow As Long
Dim xEmpID As Integer
Dim xEmpName As String
Dim strSQL As String
Worksheets("PMS").Activate
'LastRow = Cells(Rows.Count, 1).End(x1Up).Row
Set dbConnection = New ADODB.Connection
dbFileName = "C:\Documents and Settings\ADRY_7258\My Documents\HR_PMS.accdb"
With dbConnection
.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName & ";Persist Security Info=False;"
.Open dbFileName

End With
Set dbRecordset = New ADODB.Recordset
dbRecordset.CursorLocation = adUseServer
dbRecordset.Open Source:="HR_PMS_KRA_KPI", _
ActiveConnection:=dbConnection, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable
xEmpID = Worksheets("PMS").Range("E3").Value
xEmpName = Worksheets("PMS").Range("E2").Value
strSQL = "Select HR_PMS_KRA_KPI.* From HR_PMS_KRA_KPI where Dimension = 'Financial' and Employee_Name = '" & xEmpName & "'" & " and Employee_id = " & xEmpID
With dbRecordset1
.Open strSQL, dbConnection
'Set .ActiveConnection = Nothing
End With
'dbRecordset1.Open strSQL, dbConnection
'If dbRecordset1.EOF = False And dbRecordset1.BOF = False Then
' MsgBox "Duplicate Record", vbOKOnly, "Cannot insert record"
'Else

'dbRecordset.Edit
dbRecordset("Evaluator1") = Worksheets("PMS").Range("N13").Value
dbRecordset("Evaluator2") = Worksheets("PMS").Range("N14").Value
dbRecordset("Evaluator3") = Worksheets("PMS").Range("N15").Value
dbRecordset("Evaluator4") = Worksheets("PMS").Range("N16").Value
dbRecordset("Evaluator5") = Worksheets("PMS").Range("N17").Value
dbRecordset("Evaluator6") = Worksheets("PMS").Range("N18").Value
dbRecordset("Evaluator7") = Worksheets("PMS").Range("N19").Value
dbRecordset("Evaluator8") = Worksheets("PMS").Range("N20").Value
dbRecordset("Evaluator9") = Worksheets("PMS").Range("N21").Value
dbRecordset("Evaluator10") = Worksheets("PMS").Range("N22").Value
dbRecordset("Target1") = Worksheets("PMS").Range("O13").Value
dbRecordset("Target2") = Worksheets("PMS").Range("O14").Value
dbRecordset("Target3") = Worksheets("PMS").Range("O15").Value
dbRecordset("Target4") = Worksheets("PMS").Range("O16").Value
dbRecordset("Target5") = Worksheets("PMS").Range("O17").Value
dbRecordset("Target6") = Worksheets("PMS").Range("O18").Value
dbRecordset("Target7") = Worksheets("PMS").Range("O19").Value
dbRecordset("Target8") = Worksheets("PMS").Range("O20").Value
dbRecordset("Target9") = Worksheets("PMS").Range("O21").Value
dbRecordset("Target10") = Worksheets("PMS").Range("O22").Value
dbRecordset("Appraiser_Comments1") = Worksheets("PMS").Range("S13").Value
dbRecordset("Appraiser_Comments2") = Worksheets("PMS").Range("S14").Value
dbRecordset("Appraiser_Comments3") = Worksheets("PMS").Range("S15").Value
dbRecordset("Appraiser_Comments4") = Worksheets("PMS").Range("S16").Value
dbRecordset("Appraiser_Comments5") = Worksheets("PMS").Range("S17").Value
dbRecordset("Appraiser_Comments6") = Worksheets("PMS").Range("S18").Value
dbRecordset("Appraiser_Comments7") = Worksheets("PMS").Range("S19").Value
dbRecordset("Appraiser_Comments8") = Worksheets("PMS").Range("S20").Value
dbRecordset("Appraiser_Comments9") = Worksheets("PMS").Range("S21").Value
dbRecordset("Appraiser_Comments10") = Worksheets("PMS").Range("S22").Value
'Next xColumn
dbRecordset.Update
'Next xRow
'End If
dbRecordset.Close
dbConnection.Close
Set dbRecordset = Nothing
Set dbConnection = Nothing
End Sub

When I am using the following code to update the second row in Access the first row gets updated

Can you please help.

Sub appraiser_view_gen_month_save_1b()
Dim dbConnection As ADODB.Connection
Dim dbFileName As String
Dim dbRecordset As New ADODB.Recordset
Dim dbRecordset1 As New ADODB.Recordset
Dim xRow As Long, xColumn As Long
Dim LastRow As Long
Dim xEmpID As Integer
Dim xEmpName As String
Dim strSQL As String
Worksheets("PMS").Activate
'LastRow = Cells(Rows.Count, 1).End(x1Up).Row
Set dbConnection = New ADODB.Connection
dbFileName = "C:\Documents and Settings\ADRY_7258\My Documents\HR_PMS.accdb"
With dbConnection
.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName & ";Persist Security Info=False;"
.Open dbFileName

End With
Set dbRecordset = New ADODB.Recordset
dbRecordset.CursorLocation = adUseServer
dbRecordset.Open Source:="HR_PMS_KRA_KPI", _
ActiveConnection:=dbConnection, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable
xEmpID = Worksheets("PMS").Range("E3").Value
xEmpName = Worksheets("PMS").Range("E2").Value
strSQL = "Select HR_PMS_KRA_KPI.* From HR_PMS_KRA_KPI where Dimension = 'Customer' and Employee_Name = '" & xEmpName & "'" & " and Employee_id = " & xEmpID
With dbRecordset1
.Open strSQL, dbConnection
'Set .ActiveConnection = Nothing
End With
'dbRecordset1.Open strSQL, dbConnection
'If dbRecordset1.EOF = False And dbRecordset1.BOF = False Then
' MsgBox "Duplicate Record", vbOKOnly, "Cannot insert record"
'Else

'dbRecordset.Edit
dbRecordset("Evaluator1") = Worksheets("PMS").Range("N23").Value
dbRecordset("Evaluator2") = Worksheets("PMS").Range("N24").Value
dbRecordset("Evaluator3") = Worksheets("PMS").Range("N25").Value
dbRecordset("Evaluator4") = Worksheets("PMS").Range("N26").Value
dbRecordset("Evaluator5") = Worksheets("PMS").Range("N27").Value
dbRecordset("Evaluator6") = Worksheets("PMS").Range("N28").Value
dbRecordset("Evaluator7") = Worksheets("PMS").Range("N29").Value
dbRecordset("Evaluator8") = Worksheets("PMS").Range("N30").Value
dbRecordset("Evaluator9") = Worksheets("PMS").Range("N31").Value
dbRecordset("Evaluator10") = Worksheets("PMS").Range("N32").Value
dbRecordset("Target1") = Worksheets("PMS").Range("O23").Value
dbRecordset("Target2") = Worksheets("PMS").Range("O24").Value
dbRecordset("Target3") = Worksheets("PMS").Range("O25").Value
dbRecordset("Target4") = Worksheets("PMS").Range("O26").Value
dbRecordset("Target5") = Worksheets("PMS").Range("O27").Value
dbRecordset("Target6") = Worksheets("PMS").Range("O28").Value
dbRecordset("Target7") = Worksheets("PMS").Range("O29").Value
dbRecordset("Target8") = Worksheets("PMS").Range("O30").Value
dbRecordset("Target9") = Worksheets("PMS").Range("O31").Value
dbRecordset("Target10") = Worksheets("PMS").Range("O32").Value
dbRecordset("Appraiser_Comments1") = Worksheets("PMS").Range("S23").Value
dbRecordset("Appraiser_Comments2") = Worksheets("PMS").Range("S24").Value
dbRecordset("Appraiser_Comments3") = Worksheets("PMS").Range("S25").Value
dbRecordset("Appraiser_Comments4") = Worksheets("PMS").Range("S26").Value
dbRecordset("Appraiser_Comments5") = Worksheets("PMS").Range("S27").Value
dbRecordset("Appraiser_Comments6") = Worksheets("PMS").Range("S28").Value
dbRecordset("Appraiser_Comments7") = Worksheets("PMS").Range("S29").Value
dbRecordset("Appraiser_Comments8") = Worksheets("PMS").Range("S30").Value
dbRecordset("Appraiser_Comments9") = Worksheets("PMS").Range("S31").Value
dbRecordset("Appraiser_Comments10") = Worksheets("PMS").Range("S32").Value
'Next xColumn
dbRecordset.Update
'Next xRow
'End If
dbRecordset.Close
dbRecordset1.Close
dbConnection.Close
Set dbRecordset = Nothing
Set dbRecordset1 = Nothing
Set dbConnection = Nothing
End Sub[/vba]

[Aussiebear]

and your issue is?

[flowergirl]

Please ignore my previous mail. The matter has been resolved.