Export Data to Access Database with Excel VBA

The explanation includes steps for both preparing the Access database and writing the necessary VBA code in Excel to export the data.

Step 1: Prepare the Access Database

Before you export data from Excel to Access using VBA, you need to prepare your Access database. Here’s how you can do that:

1. Create an Access Database:
   • Open Microsoft Access.
   • Create a new database (you can choose a blank database).
   • Save the database in a directory you can easily access (for example, C:\Users\YourName\Documents\ExportDB.accdb).
2. Create a Table in Access:
   • In the Access database, create a table where you want to export your data.
   • For example, let’s assume we are exporting a list of employees.
   • Create a table called Employees with the following fields:
     • EmployeeID (AutoNumber, Primary Key)
     • FirstName (Text)
     • LastName (Text)
     • Department (Text)
     • HireDate (Date/Time)

Example:

Employees Table

—————————————–

| EmployeeID | FirstName | LastName | Department | HireDate |

—————————————————————

| AutoNumber | Text      | Text     | Text       | DateTime |

Make sure the field names match those you will use in your Excel data.

1. Save and Close Access:
   • Save the Access database and close Access for now, as you’ll be interacting with it using Excel VBA.

Step 2: Excel VBA Code

Now let’s write the VBA code in Excel to export data to the Access database.

VBA Code to Export Data from Excel to Access Database:

Sub ExportToAccess()
    ' Declare variables
    Dim cn As Object
    Dim rs As Object
    Dim strDatabasePath As String
    Dim strSQL As String
    Dim row As Long
    Dim lastRow As Long
    Dim ExcelSheet As Worksheet
    ' Set path to the Access Database
    strDatabasePath = "C:\Users\YourName\Documents\ExportDB.accdb" ' Path to your Access database
    ' Set the worksheet that contains the data to export
    Set ExcelSheet = ThisWorkbook.Sheets("Sheet1") ' Adjust as per your sheet name
    ' Create a connection to the Access database
    Set cn = CreateObject("ADODB.Connection")
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDatabasePath & ";Persist Security Info=False;"
    cn.Open
    ' Find the last row of data in Excel
    lastRow = ExcelSheet.Cells(ExcelSheet.Rows.Count, "A").End(xlUp).Row
    ' Loop through each row of data (starting from row 2 assuming row 1 has headers)
    For row = 2 To lastRow
        ' Construct the SQL query to insert data into the Employees table
        strSQL = "INSERT INTO Employees (FirstName, LastName, Department, HireDate) " & _
                 "VALUES ('" & ExcelSheet.Cells(row, 1).Value & "', " & _  ' FirstName
                 "'" & ExcelSheet.Cells(row, 2).Value & "', " & _  ' LastName
                 "'" & ExcelSheet.Cells(row, 3).Value & "', " & _  ' Department
                 "#" & Format(ExcelSheet.Cells(row, 4).Value, "mm/dd/yyyy") & "#)" ' HireDate (proper date format)
        ' Execute the SQL query to insert the data into the Access table
        cn.Execute strSQL
    Next row
    ' Clean up
    cn.Close
    Set cn = Nothing
    MsgBox "Data export to Access completed successfully!"
End Sub

Explanation of the Code:

1. Variable Declarations:
   • cn: This is an ADODB connection object that allows you to interact with the Access database.
   • rs: This would be a recordset object if needed (but in this case, it’s not used directly for inserting data).
   • strDatabasePath: The path to your Access database file (change the path as needed).
   • strSQL: The SQL query string used to insert data into the Access database.
   • row: A variable used in the loop to iterate through rows in the Excel worksheet.
   • lastRow: The last row of data in the Excel sheet (to know the range of data to process).
   • ExcelSheet: The worksheet that contains the data.
2. Connecting to the Access Database:
   • The cn (connection object) is initialized to connect to the Access database using the connection string.
     • Provider=Microsoft.ACE.OLEDB.12.0 specifies the provider for Access.
     • The Data Source is the path to the Access database file you created earlier.
     • Persist Security Info=False is included to avoid saving security-related information in the connection string.
3. Looping Through Excel Data:
   • The lastRow variable determines the last row of data in the worksheet (assuming data starts from row 2 and has headers in row 1).
   • The For loop starts from row 2 and goes through each row until the last row, constructing an INSERT INTO SQL query for each row of data.
4. SQL Insert Query:
   • The strSQL query inserts the data from Excel into the Access Employees table.
   • Each column value from Excel is taken from ExcelSheet.Cells(row, column) where column represents the column number (1 for FirstName, 2 for LastName, etc.).
   • The Format function is used to ensure the date is in the correct format (mm/dd/yyyy), which is required by Access for date fields.
5. Executing the SQL Query:
   • The cn.Execute method runs the SQL query and inserts the row into the Access table.
6. Clean-up and Completion:
   • After the loop finishes, the connection is closed (cn.Close) and the connection object is set to Nothing to release resources.
   • A message box is displayed to inform the user that the export is complete.

Output:

After running the VBA code, the data from Excel will be inserted into the Access database. Each row from Excel will be exported as a new record in the Employees table of the Access database. If the data is inserted successfully, you will see the message:

Data export to Access completed successfully!

Troubleshooting Tips:

• Ensure that the column names in Excel match exactly with those in the Access table.
• Double-check the path to the Access database to ensure it is correct.
• If you encounter errors, enable Microsoft ActiveX Data Objects (ADO) in your references:
  • In the VBA editor, go to Tools → References, and check Microsoft ActiveX Data Objects 6.1 Library (or a similar version).