How to link ms excel 2003 to ms access 2003

Question

I have office 2003, and i have develop a database from ms access 2003. What i want is to link ms excel and ms access so as when am updating either of them both will be updated

Answer 1

I know that from within Access, you can add a "linked table" with an Excel worksheet as a data source. This will cause the data in the Excel sheet to show up as a table in Access. When you make changes to the data in Access, the data in the underlying Excel sheet will change. Also, if you open up the Excel sheet and change the values in one of the rows, the change will be reflected when the link in Access is refreshed.

However, there can be issues when trying to link Access and Excel together. You may not be able to edit the same document from Access and Excel at the same time. Also, if you add additional columns or rows to your source data in Excel, you may have to completely re-link the table in Access.

If you are the only user of both the Access application and Excel sheet, you can develop a fairly robust solution using this method. However, you have to be careful with trying to deploy a solution like this with multiple users because you may start to get issues with multiple people trying to edit the same document at one time. At this point, you may need to look at more robust solutions involving a database or SharePoint backend.

Answer 2

As Ben said in his answer it is possible but can easily lead to problems in a multi user environment. I would suggest keeping the data in access and pulling it from excel. Or revisiting if excel is needed at all to update the date (read only reports would be ok)

Answer 3

When you use a spreadsheet as a linked table in Access 2003, the link is read-only. Your Access application will be able to "see" changes made to the spreadsheet by Excel, but you won't be able to edit the spreadsheet data from Access.

See this advisory from Microsoft: You cannot change, add, or delete data in tables that are linked to an Excel workbook in Office Access 2007, Office Access 2003, or Access 2002

Perhaps you could automate Excel to allow your Access application to change the spreadsheet data, but that would require more effort than editing a table through a form.

Edit: After reading that Microsoft link more carefully, I think you might be able to have an editable linked spreadsheet if you don't install "Microsoft Office 2003 Service Pack 2 (SP2) or a later service pack or any Access 2003 updates that were released after Office 2003 SP2". But that's not something I would do.