Option Compare Database

  
Private Sub Komut0_Click()
  
    Dim say As Long, say1 As Long
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sSql As String
    Dim txtDosyaAdres As String
  
    
    txtDosyaAdres = CurrentProject.Path & "\veri.xlsx"
    sSql = "select [KOD],[AD],[YAŞ] from [Sayfa1$B3:E] where [KOD] Is Not Null" '

    
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & txtDosyaAdres & ";extended properties=""excel 12.0;hdr=Yes;imex=1"""
    
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.Open sSql, con

    Do While Not rs.EOF And Not rs.BOF 'Tablo güncelleme icin(Exceldeki recordseti icine alinan veriler icin döngü)
        If DCount("[kod]", "Tablo1", "[kod] = '" & rs(0) & "'") > 0 Then 'Eger Tablo1 deki kod sütununda excelden alinan recordset icindeki veri mevcutsa

          CurrentDb.Execute _
            "UPDATE Tablo1 SET [kod] = '" & rs(0) & "'," & _
                              "[ad] = '" & rs(1) & "'," & _
                              "[yas] = '" & rs(2) & "' WHERE [kod] = '" & rs(0) & "'"
            
            say = say + 1
            
        ElseIf DCount("[kod]", "Tablo1", "[kod] = '" & rs(0) & "'") = 0 Then 'Eger Tablo1 deki kod sütununda excelden alinan recordset icindeki veri mevcut degilse
            
            CurrentDb.Execute _
                        "INSERT INTO Tablo1" _
                          & " ([kod], [ad], [yas])" _
                          & " VALUES ( '" & rs(0) & "' , '" & rs(1) & "' , " & rs(2) & " );" 'Kayit ekle exceldekinde olup accesste mevcut olmayan veri

            say1 = say1 + 1
        End If

        rs.MoveNext
    Loop


  Form2.Requery
  
  MsgBox "Eklenen kayit sayisi=" & say1, vbInformation, "Bilgi"
  MsgBox "Düzeltilen kayit sayisi=" & say, vbInformation, "Bilgi"
  

    rs.Close
    con.Close
    Set rs = Nothing


End Sub