Import Data from Access With Excel VBA

VBA Code to Import Data from Access into Excel

Sub ImportDataFromAccess()
    ' Declare necessary variables
    Dim conn As Object
    Dim rs As Object
    Dim sqlQuery As String
    Dim connectionString As String
    Dim excelSheet As Worksheet
    Dim i As Integer
    ' Initialize the worksheet where the data will be imported
    Set excelSheet = ThisWorkbook.Sheets("Sheet1")  ' Change "Sheet1" to the desired sheet
    ' Clear any existing data on the worksheet
    excelSheet.Cells.Clear
    ' Create an ADO connection object
    Set conn = CreateObject("ADODB.Connection")
    ' Create an ADO recordset object
    Set rs = CreateObject("ADODB.Recordset")
    ' Define the connection string for the Access database
    ' Change the path to your Access database (.accdb or .mdb) file
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\your\database.accdb;"
    ' Open the connection to the Access database
    conn.Open connectionString
    ' Define your SQL query to retrieve data from the Access database
    ' Change "TableName" to the actual table or query name you want to retrieve data from
    sqlQuery = "SELECT * FROM TableName"  ' You can modify this to fetch specific columns or apply filters
    ' Open the recordset with the SQL query
    rs.Open sqlQuery, conn
    ' Loop through the recordset and write the data to the Excel worksheet
    ' Write the headers (field names) to the first row in the worksheet
    For i = 0 To rs.Fields.Count - 1
        excelSheet.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    ' Write the recordset data starting from row 2
    Dim rowNum As Integer
    rowNum = 2
    Do While Not rs.EOF
        For i = 0 To rs.Fields.Count - 1
            excelSheet.Cells(rowNum, i + 1).Value = rs.Fields(i).Value
        Next i
        rs.MoveNext
        rowNum = rowNum + 1
    Loop
    ' Close the recordset and connection objects
    rs.Close
    conn.Close
    ' Release the objects
    Set rs = Nothing
    Set conn = Nothing
    ' Inform the user that the data import is complete
    MsgBox "Data import from Access completed successfully!", vbInformation
End Sub

Explanation of the Code:

1. Declare Variables:
   • conn (ADO Connection Object): This object is used to establish a connection to the Access database.
   • rs (ADO Recordset Object): This object is used to store the data retrieved from the Access database.
   • sqlQuery (String): This is a string that contains the SQL query that will be executed to retrieve data from Access.
   • connectionString (String): This string contains the necessary connection parameters to connect to the Access database.
   • excelSheet (Worksheet): This variable holds a reference to the Excel worksheet where the data will be imported.
   • i (Integer): This variable is used for iterating through the fields and rows in the recordset.
2. Setup Worksheet:
   • The worksheet « Sheet1 » is specified, and any existing data is cleared using excelSheet.Cells.Clear. You can change « Sheet1 » to the name of any other worksheet in your workbook.
3. Create ADO Objects:
   • The CreateObject(« ADODB.Connection ») method creates an ADO connection object that is used to establish a connection to the Access database.
   • The CreateObject(« ADODB.Recordset ») method creates a recordset object that will hold the data from the query.
4. Connection String:
   • The connection string defines the provider (Microsoft.ACE.OLEDB.12.0) and the data source (the path to your .accdb Access file).
   • Be sure to change the path « C:\path\to\your\database.accdb » to the actual path where your Access file is stored.
5. Open Connection:
   • The conn.Open connectionString line opens the connection to the Access database.
6. SQL Query:
   • The sqlQuery variable contains a SELECT query that retrieves data from the Access database. You can modify the query to select specific columns or apply filters, e.g., SELECT Column1, Column2 FROM TableName WHERE Column1 = ‘SomeValue’.
7. Retrieving Data:
   • The rs.Open sqlQuery, conn line executes the SQL query and stores the results in the rs recordset.
   • The field names (column headers) are written to the first row of the Excel worksheet (excelSheet.Cells(1, i + 1).Value).
   • The data from each row in the recordset is written to subsequent rows in the Excel sheet.
8. Closing and Cleanup:
   • After the data has been written to the worksheet, the recordset and connection objects are closed (rs.Close, conn.Close).
   • The Set rs = Nothing and Set conn = Nothing lines release the objects to free up memory.
9. User Notification:
   • A message box (MsgBox) is displayed to inform the user that the data import has been completed successfully.

Expected Output:

1. Excel Worksheet:
   • The specified Excel worksheet (in this case, « Sheet1 ») will be populated with the data from the Access database.
   • The first row will contain the column headers (field names) from the Access table.
   • The data will be imported into the subsequent rows, with each field in a separate column.
2. Message Box:
   • A message box will appear at the end of the process, confirming that the data import was successful.

Customizing the Code:

• Connection String: If you’re using an older version of Access (e.g., .mdb), the connection string will change. For example:
• connectionString = « Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\to\your\database.mdb; »
• SQL Query:
  • If you want to retrieve specific data or apply filters, modify the sqlQuery string:
  • sqlQuery = « SELECT Column1, Column2 FROM TableName WHERE Column1 = ‘Value' »
• Target Worksheet:
  • If you want to import data into a different worksheet, change « Sheet1 » to the name of the target sheet.

This approach uses ADO (ActiveX Data Objects) to query and retrieve data from an Access database. It’s an efficient way to import large datasets from Access into Excel without having to manually copy and paste the data.