🔒 Closed VBA Excel to Update Existing records in Access Database

[Mielkew]

Mga lodi,

Bka naman po merong gustong tumulong. Hindi q po ma loop ung Primary Key ID

Option Explicit

Sub Update_DB_1()
 
    Dim cnx As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim qry As String
    Dim id
    Dim sFilePath As String
    Dim lastRow, nRow, nCol, a  As Long
      
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
  
    sFilePath = Worksheets("Home").Range("P4").Value
  
    a = 2
  
    id = VBA.Trim(Sheet4.Cells(a, 1))
  
    cnx.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFilePath

    qry = "SELECT * FROM f_SD WHERE ID = '" & id & "'"
      
    rst.Open qry, cnx, adOpenKeyset, adLockOptimistic

For a = 2 To lastRow
  
  For nRow = 2 To lastRow
      
        If IdExists(cnx, Range("A" & nRow).Value) Then
                    
              'Update RecordSet
                For nCol = 2 To 9
                    rst.Fields(Cells(1, nCol).Value2) = Cells(nRow, nCol).Value 'Using the Excel Sheet Column Heading
                Next nCol
        
        rst.Update
      
         Else
      
            Range("S" & nRow).Value2 = "ID NOT FOUND"
        
        End If
    
    Next nRow
a = a + 1
Next a

    rst.Close
    cnx.Close
  
    Set rst = Nothing
    Set cnx = Nothing

   MsgBox "Updated Successfully", vbInformation
  
End Sub

 

[PHC-MakiBoT]

Hindi po talaga pwede ilop yung primary key
pero pwede mo sya isearch through DataReader