How to Convert Excel to Access Database?

Convert Excel to Access Database

Use this simple method to learn how to create Access database from Excel spreadsheet.

Programmers convert Excel to Access database for easy maintenance, minimize the load or as a database backup. This article is going to explain how to import Excel into Access without using any ADODB or connection string. This method is going to very simple.

Instead of creating a connection string and mapping to it in SQL query every time, we are going to just do an Export in single step by using the Microsoft Access Object Library. The process explained in this article will work better in Office 2007 and above.

How to Import Excel into Access Database?

Before using the Macro code, let’s see how import Excel into Access is done manually. We need a source file (Excel) and a target destination file (MDB). Assume there is a table as in below format in an Excel sheet. To convert this Excel to Access database, create a new MDB file.

1. Open the MDB file & Choose “External Data” from Menu.
2. Choose “Excel” from the list of sources (Access, Excel, Text, XML etc) Import Tab.
3. Enter Excel file path in ‘File Name’ field in the Pop up window.
4. Choose ‘Import to new Table’ or ‘Append’ or ‘Link’ as per your requirement.
5. Click next, proceed with few more windows to choose the Sheet or view that has data.
6. click Finish to complete importing Excel  data to Access.

Now, the data from Excel will be imported to the current mdb database. Access will also ask confirmation whether you want to store the import steps, so that it will be easy to repeat the same steps in just one click.

Also Read: Different methods to Read Data from external Excel Workbook.

VBA to Export Excel to Access Database

To create Access database from Excel spreadsheet, Press Alt + F11 to view the VB Editor in Excel. Copy paste the below code, change the MDB File path and Table name as per your need.

1. Include “Microsoft Access Object Library” from Tools -> References.
2. Press F5 to execute this code and export data.

Option Explicit
'This Code is downloaded from Officetricks.com
Public Sub Export_Excel_To_Access_MDB()
    Dim oDB As New Access.Application
    
    'Create Object to Link to the Access Databse
    oDB.OpenCurrentDatabase "C:\OfficeTricks\Excel_To_Access.accdb", False
    
    'Import data from Excel Spreadsheet to Access Database
    oDB.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "ExcelToAccess", ThisWorkbook.FullName, True
    
    'Import only specific data range from Excel to Access
    oDB.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "ExcelToAccess", ThisWorkbook.FullName, True, "Sheet1$A1:E3"
    
    'Export Data from Access to a Text File
    oDB.DoCmd.TransferText acExportDelim, TableName:="ExcelToAccess", Filename:=ThisWorkbook.Path & "\ExportText.txt"
    
    'Close Access Application and quit
    oDB.CloseCurrentDatabase
    oDB.Quit
    Set oDB = Nothing
    
    'Process Completed
    MsgBox "Data Exported to Access DB"
End Sub

The above code also explains how to (1) Export particular range from Excel to Access and (2) Export data from Access database to a Text file.

This page only explains about how to do a plain data transfer (i.,e directly convert Excel to Access). It does not include the explanations about how to build a SWL query, to insert, alter, and inquire data from Access. To do those processes, we have to make use of other options present in DoCmd collection of Access Application Object.