CODEWITHSUNDEEP
sqlexcelms-access
Jeevan
Jeevan

Reputation: 1

How to connect an Excel file to an Access Database

I have created a query in an Access database and exported the query result into an Excel file. Now, I want to connect that Excel file to the Access database (or to that query) so that whenever some fields are updated in database, those changes would be automatically updated in the exported Excel file (report). What would be the best way to do it?

Thanks

Upvotes: 0

Views: 2067

Answers (2)

SunKnight0
SunKnight0

Reputation: 3351

You can create a link between Excel and an Access table or query by going to the Data tab on Excel and clicking the "From Access" icon.

Alternatively if there is a trigger inside access that you can use to update data (or if you don't need real time updates just hourly or daily ones), you could have access programmatically re-export.

Upvotes: 1

Johnny Bones
Johnny Bones

Reputation: 8404

If you want to work with data in Access, but still maintain the data in Excel, you need to link to the data rather than import it. Follow these steps:

  1. Create a blank database or open an existing file in Access.

  2. Select File, Get External Data, Link Tables.

  3. Select Microsoft Excel as the file type.

  4. Select a worksheet or named range to import, and then click Next. You can import only one worksheet or named range at a time, and each one will become an Access table.

  5. In the next dialog box, select or deselect the check box First Row Contains Column Headings, depending on whether your worksheet has headings. Then click Next.

  6. Enter a name for the table (or accept the default name that Access suggests), click Finish, and click OK.

Now you have an Access table that looks almost exactly like the imported table. The advantage is that it maintains a live link to the Excel worksheet and can be edited in either application.

Upvotes: 0

Related Questions