Access Database Management With Excel Userform

Access Database Example          

        We have two files in a folder named Personel.xls (Excel workbook) and Personel.mdb (Access database file).
The Access file contains PERSONEL table with personnel information.

       
         There is a userform in the Excel workbook with many VBA controls on it. Through userform, we will do operations onto the Access database ,such as adding data , deleting data, changing data  and querying the database.

With this userform ;

☑️ New data can be added to Access Database

☑️ Data can be changed

☑️ Data can be deleted

☑️ Data can be searched in Access Database

☑️ Data can be transferred from the Access Database to Excel.

We used the Adodb Connection Object for connection to Access database. Codes to connect :

Sub baglanti()
Set baglan = CreateObject("adodb.connection")
#If VBA7 And Win64 Then
baglan.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\PERSONEL.mdb"
#Else
baglan.Open "provider=microsoft.jet.oledb.4.0;data source=" & ThisWorkbook.Path & "\PERSONEL.mdb"
#End If
End Sub

         We will do all the operations related (data adding,deleting,changing etc.) to the database with the Adodb Connection Object that we created. 

Set baglan = CreateObject("adodb.connection")

For example , let's add a new record to Access database .

Private Sub CommandButton3_Click()
Dim i As Byte
On Error GoTo hata
For i = 0 To 51
If Mid(UserForm1.Controls(i).Name, 1, 3) = "txt" Then
If UserForm1.Controls(i).Value = "" Then
MsgBox "Required field is missing.", vbCritical + vbOKOnly
Exit Sub
End If
End If
If Mid(UserForm1.Controls(i).Name, 1, 3) = "cmb" Then
If UserForm1.Controls(i).Value = "" Then
MsgBox "Required field is missing.", vbCritical + vbOKOnly
Exit Sub
End If
End If
Next i

kod = "'" & txtCalisanKod & "'"
ad = "'" & txtAdi & "'"
dtarih = "'" & txtDtarih & "'"
soyad = "'" & txtSoyadi & "'"
tc = "'" & txtTC & "'"
sgk = "'" & txtSGK & "'"
dyeri = "'" & txtDyeri & "'"
baba = "'" & txtBabaAdi & "'"
anne = "'" & txtAnneAdi & "'"
gtarih = "'" & txtGirisTarih & "'"
ucret = "'" & txtUcret & "'"
vno = "'" & txtVergiNo & "'"
adres = "'" & txtAdres & "'"
Email = "'" & txtEmail & "'"
il = "'" & txtIl & "'"
ilce = "'" & txtIlce & "'"
semt = "'" & txtSemt & "'"
evtel = "'" & txtEvtel & "'"
ceptel = "'" & txtCeptel & "'"
cinsiyet = "'" & cmbCinsiyet & "'"
departman = "'" & cmbDepartman & "'"
gorev = "'" & cmbGorev & "'"
uturu = "'" & cmbUcretturu & "'"

Call baglanti
Set rs = baglan.Execute("INSERT INTO PERSONEL (CALISAN_KODU,ADI,SOYADI,TC_KIMLIK,DOGUM_TARIHI,CINSIYET,DOGUM_YERI,BABA_ADI,ANNE_ADI,GIRIS_TARIH,SGK,VERGI_NO,ADRES,DEPARTMAN,GOREVI,UCRET_TURU,UCRET,IL,ILCE,SEMT,EV_TEL,CEP_TEL,EMAIL) Values _
(" & kod & "," & ad & "," & soyad & "," & tc & "," & dtarih & "," & cinsiyet & "," & dyeri & "," & baba & "," & anne & "," & gtarih & "," & sgk & "," & vno & "," & adres & "," & departman & "," & gorev & "," & uturu & "," & ucret & "," & il & "," & ilce & "," & semt & "," & evtel & "," & ceptel & "," & Email & ")")
Set baglan = Nothing: Set rs = Nothing:

temizle
ListBox1.Clear
MsgBox "New record was added.", vbInformation + vbOKOnly
Label25.Caption = "Total Records = " & ListBox1.ListCount

hata:
If Err = -2147217913 Then
MsgBox "The salary must be numeric values and The dates must be valid date (e.g.: 20.12.2009)", vbCritical + vbOKOnly
End If
End Sub