Building a Database Management System Using Access VBA

Introduction

Microsoft Access is a powerful database management tool, and combining it with VBA (Visual Basic for Applications) allows for advanced automation, custom forms, and efficient data handling. A Database Management System (DBMS) built with Access and VBA can store, retrieve, and manage large amounts of data seamlessly.

In this guide, we'll explore how to build a basic DBMS using Access VBA, including:

✔️ Setting up tables and relationships

✔️ Creating a user-friendly form

✔️ Automating database tasks with VBA

✔️ Generating reports

1️⃣ Setting Up the Database Structure

📍 Step 1: Create Tables

Tables form the foundation of a database. Let’s create a Customer Database with the following table:

Table Name: Customers

🔹 Primary Key: CustomerID ensures each record is unique.

📍 Step 2: Define Relationships

If you need a one-to-many relationship (e.g., Customers & Orders), create another table:

Table Name: Orders

➡️ Use the Relationships Tool in Access to establish a one-to-many relationship between Customers and Orders via CustomerID.

2️⃣ Creating a User-Friendly Form

Access Forms help users interact with the database efficiently.

📍 Step 1: Create a Customer Form

1. Go to Create > Form Wizard.
2. Select the Customers table and add all fields.
3. Choose Columnar Layout and finish the wizard.
4. Switch to Design View to customize the form.

📍 Step 2: Add VBA to Automate Actions

To create a New Customer Button:

1. Open the form in Design View.
2. Add a button (from Controls).
3. Name it btnAddCustomer.
4. Open the VBA Editor (ALT + F11).
5. Add this VBA code:

Private Sub btnAddCustomer_Click()
    DoCmd.GoToRecord , , acNewRec
End Sub

🔹 This code moves to a new record, allowing users to add a new customer easily.

3️⃣ Automating Database Tasks with VBA

📍 Step 1: Save Data Using VBA

Instead of relying on manual entry, automate data saving with VBA.

Private Sub btnSave_Click()
    If IsNull(Me.FirstName) Or IsNull(Me.LastName) Then
        MsgBox "Please enter customer details.", vbExclamation, "Missing Data"
        Exit Sub
    End If
    
    DoCmd.RunCommand acCmdSaveRecord
    MsgBox "Customer saved successfully!", vbInformation, "Success"
End Sub

🔹 This code validates input and saves the record, ensuring required fields are filled.

📍 Step 2: Search for a Customer Using VBA

Instead of scrolling manually, create a search function.

Private Sub btnSearch_Click()
    Dim searchValue As String
    searchValue = InputBox("Enter Customer Last Name:", "Search Customer")

    If searchValue <> "" Then
        Me.Filter = "LastName LIKE '*" & searchValue & "*'"
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
End Sub

🔹 This code prompts the user for a last name, then filters the results dynamically.

4️⃣ Generating Reports with VBA

Reports help summarize and present data effectively.

📍 Step 1: Create an Orders Report

1. Go to Create > Report Wizard.
2. Select the Orders table, add required fields, and finish the wizard.
3. Open the report in Design View.

📍 Step 2: Print a Report Using VBA

Add a Print Report Button on the main form and use this VBA code:

Private Sub btnPrintReport_Click()
    DoCmd.OpenReport "Orders Report", acViewPreview
End Sub

🔹 This opens the report in preview mode, allowing users to print it directly.

5️⃣ Securing and Optimizing the Database

📍 Step 1: Prevent Unauthorized Edits

Restrict modifications by locking form fields:

Private Sub Form_Current()
    Me.FirstName.Locked = True
    Me.LastName.Locked = True
    Me.Email.Locked = True
End Sub

🔹 Users can view but not edit existing records.

📍 Step 2: Backup Database Automatically

Use VBA to create automatic backups:

Private Sub btnBackup_Click()
    Dim backupPath As String
    backupPath = "C:\Backups\CustomerDB_" & Format(Now, "yyyy-mm-dd_hhmmss") & ".accdb"

    FileCopy CurrentDb.Name, backupPath
    MsgBox "Backup created: " & backupPath, vbInformation, "Backup Complete"
End Sub

🔹 Saves a timestamped copy of the database in the specified folder.

Conclusion

Building a Database Management System using Access VBA enhances efficiency by automating data entry, searching, reporting, and backups. With custom forms, filters, and reports, businesses can manage their data more effectively and securely.