Access Veri Tabanı İle CRUD İşlemleri - Microsoft Excel

Bir UserForm üzerinden Access veri tabanındaki tablo üzerinde CRUD (Create, Read, Update, Delete) işlemlerinin ve süzme işleminin yapılmasını sağlayan VBA projesidir.

crud (Module) Kodları

Option Explicit

'--------------------------------------------------
'Listele
'--------------------------------------------------
Sub Listele()
    Dim baglanti   As ADODB.Connection
    Dim kayit_seti As ADODB.Recordset
    Dim sorgu      As String
    
    Set baglanti = New ADODB.Connection
    Set kayit_seti = New ADODB.Recordset
    
    baglanti.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
                  "Data Source=" & ThisWorkbook.Path & "\veri.accdb"
    
    sorgu = "SELECT * FROM kisiler"
    Set kayit_seti = baglanti.Execute(sorgu)
    
    With AnaUserForm.icerikListBox
        .ColumnCount = kayit_seti.Fields.Count
        .ColumnWidths = "30;50;50;70"
        .RowSource = ""
            .Column = kayit_seti.GetRows(kayit_seti.RecordCount)
    End With
    
    Set kayit_seti = Nothing
    Set baglanti = Nothing
End Sub

'--------------------------------------------------
'Süz
'--------------------------------------------------
Sub Suz()
    Dim baglanti    As ADODB.Connection
    Dim kayit_seti  As ADODB.Recordset
    Dim sorgu       As String
    Dim aranan      As String

    Set baglanti = New ADODB.Connection
    Set kayit_seti = New ADODB.Recordset
    
    baglanti.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
                  "Data Source=" & ThisWorkbook.Path & "\veri.accdb"

    aranan = "'%" & AnaUserForm.ismeGoreSuzTextBox.Text & "%'"
    
    sorgu = "SELECT * FROM kisiler WHERE ad LIKE " & aranan
    Set kayit_seti = baglanti.Execute(sorgu)

    With AnaUserForm.icerikListBox
        .ColumnCount = kayit_seti.Fields.Count
        .ColumnWidths = "30;50;50;70"
        .RowSource = ""
        .Column = kayit_seti.GetRows(kayit_seti.RecordCount)
    End With
    
    Set kayit_seti = Nothing
    Set baglanti = Nothing
    
End Sub

'--------------------------------------------------
'Ekle
'--------------------------------------------------
Sub Ekle()
    Dim baglanti   As ADODB.Connection
    Dim kayit_seti As ADODB.Recordset
    Dim sorgu      As String
    Dim ad         As String
    Dim soyad      As String
    Dim eposta     As String

    Set baglanti = New ADODB.Connection
    Set kayit_seti = New ADODB.Recordset
    
    baglanti.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
                  "Data Source=" & ThisWorkbook.Path & "\veri.accdb"
    
    ad = "'" & AnaUserForm.adTextBox.Text & "'"
    soyad = "'" & AnaUserForm.soyadTextBox.Text & "'"
    eposta = "'" & AnaUserForm.ePostaTextBox.Text & "'"
    
    sorgu = "INSERT INTO kisiler(ad, soyad, eposta) VALUES(" & ad & ", " & soyad & ", " & eposta & ")"
    Set kayit_seti = baglanti.Execute(sorgu)
End Sub

'--------------------------------------------------
'Güncelle
'--------------------------------------------------
Sub Guncelle()
    Dim baglanti     As ADODB.Connection
    Dim kayit_seti   As ADODB.Recordset
    Dim sorgu        As String
    Dim ad_giris     As String
    Dim soyad_giris  As String
    Dim eposta_giris As String
    Dim id_degeri    As String
    Dim i            As Integer
    
    Set baglanti = New ADODB.Connection
    Set kayit_seti = New ADODB.Recordset
    
    baglanti.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
                  "Data Source=" & ThisWorkbook.Path & "\veri.accdb"
    
    With AnaUserForm
        ad_giris = "ad='" & .adTextBox.Text & "', "
        soyad_giris = "soyad='" & .soyadTextBox.Text & "', "
        eposta_giris = "eposta='" & .ePostaTextBox.Text & "'"
    
        For i = 0 To .icerikListBox.ListCount - 1
            If .icerikListBox.Selected(i) = True Then
                sorgu = "UPDATE kisiler SET " & ad_giris & soyad_giris & eposta_giris & _
                        " WHERE id=" & .icerikListBox.List(i, 0)
                Set kayit_seti = baglanti.Execute(sorgu)
            End If
        Next
    End With
    
    Set kayit_seti = Nothing
    Set baglanti = Nothing
End Sub

'--------------------------------------------------
'Sil
'--------------------------------------------------
Sub Sil()
    Dim baglanti   As ADODB.Connection
    Dim kayit_seti As ADODB.Recordset
    Dim sorgu      As String
    Dim id_degeri  As String
    Dim i          As Integer
    
    Set baglanti = New ADODB.Connection
    Set kayit_seti = New ADODB.Recordset
    
    baglanti.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
                  "Data Source=" & ThisWorkbook.Path & "\veri.accdb"
    
    With AnaUserForm
        For i = 0 To .icerikListBox.ListCount - 1
            If .icerikListBox.Selected(i) = True Then
                sorgu = "DELETE FROM kisiler WHERE id=" & .icerikListBox.List(i, 0)
                Set kayit_seti = baglanti.Execute(sorgu)
            End If
        Next
    End With
    
    Set kayit_seti = Nothing
    Set baglanti = Nothing
End Sub

Sub Ayarlar()
    AnaUserForm.Caption = "Access Veri Tabanı - ListBox İle CRUD İşlemleri"
End Sub

Sub FormAc()
    AnaUserForm.Show
End Sub

AnaUserForm (UserForm) Kodları

Option Explicit

Private Sub ekleCommandButton_Click()
    Ekle
    Listele
End Sub

Private Sub guncelleCommandButton_Click()
    Guncelle
    Listele
End Sub

Private Sub silCommandButton_Click()
    Sil
    Listele
End Sub

Private Sub listeleCommandButton_Click()
    Listele
End Sub

Private Sub ismeGoreSuzTextBox_Change()
    Suz
End Sub

Private Sub UserForm_Initialize()
    Ayarlar
End Sub

Konu ile ilgili örnek dosyayı bu bağlantıdan indirebilirsiniz: İndir