'Specify the file path of the accdb file. 'The code uses late binding, so no reference to external library is required. ''Excel Data' in the 'Customers' table of the database. 'The macro opens the Sample.accdb database and adds the 7 rows from the sheet The Excel sheet data should be of the same data type as the ones in the Access table (e.g., you cannot add a string value in an integer field).The Excel sheet headers should match the Access table ones (e.g., cell A1: FirstName, Access header: FirstName).You can click the “Close” button.Ĭongratulations! You just imported the Excel data into the Access table.Īs the above image shows, two things should be carefully considered when using this VBA code: Step 6: In the latter case, the wizard’s final form will ask you if you want to save the import step. Step 5: In the next form of the wizard, check the “I would like a wizard to analyze my table after importing the data.” If you like, or you can click the “Finish” button. Step 4:If your headers are in the first row, the corresponding checkbox will already be checked on the next screen. Note that this form will not appear if the selected spreadsheet contains only a single worksheet. Step 3:If your spreadsheet contains multiple sheets, then you will have to click on the “Show Worksheets” radio button, select the appropriate sheet (here “Sheet1”) and click the “Next” button. Click the “Append a copy of the records to the table” radio button, and from the dropdown, select the appropriate table (in this case, “Customers”). Step 2: Click the “Browse” button and find the spreadsheet file that contains the data to be imported. Step 1:Open the Access database, select the “External Data” tab in the Ribbon, and in the group “Import & Link,” click the Excel button. The 6-step importing process can be done by using the Access wizard that was created for this reason. How to manually add records into Access table from Excel Loop through Excel data and add them to the recordset (row by row).īefore we dive into the VBA code, let’s see how to accomplish the same thing, but manually, shall we?.Create and open a recordset that will contain the table data.Create and open a connection to the Access database.The idea behind the code is the following: In the next paragraph, you will find a VBA code snippet used from Excel to add data into an existing Access table. It’s time now to learn how to add records to an existing Access table. We have seen in the past how to run Access queries from Excel, as well as how to export Access data to Excel.

How to convert excel to access table