In this tutorial, we discuss about how to update Access Database from Excel. As we know, we can import or export data between Ms Access and Ms Excel.
Link Ms Excel with Access Database
First thing to do is to link the spreadsheet in Ms Excel with the database in the Ms Access. To do this, follow the steps:
1. Open Ms. Excel. Go to Data tab, the select From Access.
2. Locate the database path in the dialog box.
3. In the pop up menu, choose the table to be imported. Then choose the table type.
4. Wait until the table is show up in the spreadsheet. And you are done with linking.
Linked Ms Excel Spreadsheet
Next, we’re linked the Ms Excel spreadsheet with the Ms Access database.
1. Open the Ms Access. Then, open the database that has just been imported in previous instructions.
2. Go to External Data tab, then, click Excel in Import & Link.
3. In the pop up menu, locate the new spreadsheet. Then in the option below, choose Link to data source by creating a linked table. Then click ok.
4. Choose the sheet to be linked. Hit next.
5. Lastly, rename the new linked table before hit the Finish button.
6. You are done! Now, there will be a new table that linked to Excel spreadsheet. Every time you update the data in Ms Excel, it will automatically updated to the Access database.
Link Database in Query
Note: If you have query, form or report using the excel database, you need to link the database in the query. To link the database, follow this steps:
1. Create the new query, then rename and save it.
2. Insert both tables: the original table and the linked excel table.
3. In Query Design, linked both table by the primary key.
4. Then in the box below, select the primary key from the original table. In Update To field, input the syntax: [ExcelTableName]![ColumnName]
Example: [TransactionExcel]![TransactionID]
The ExcelTableName is the linked excel table name, while the column name is the field name from the linked Excel table.
5. Then run the query. Now, every time you update the Excel spreadsheet, it will automatically not only update the database in the linked database, but the data in query, form, and report as well.