I am going to talk about how you can link to an Excel spreadsheet within MS Access.
In MS Access 2003 you can make changes to the spreadsheet from within Access. You cannot do this in Access 2007 however.
Access to Excel will create a linked table to the spreadsheet.
You could create an MS Access form or a query around the spreadsheet data.
Let’s get started.
Here is my MS Excel spreadsheet that I will be bringing into MS Access.
In MS Access go to the Ribbon and click on the ‘External Data’ tab.
At the ‘Import’ section click on the Excel button.
This brings up the Excel import wizard.
Click the ‘Browse’ button and locate the spreadsheet you wish to bring into MS Access.
Now click the option for linking to the data source.
Click the ‘OK’ button to move on.
At the next screen click the small box for making the first row in your MS Access table contain the column heading from the Excel spreadsheet.
You will also be shown how the data is looking.
Click the ‘Next’ button to move on.
Add a name for the linked table you want to show in MS Access.
Click the ‘Finish’ button.
You will see a message saying the linking has been completed.
In MS Access go to the navigation pane and you will see under tables a small Excel icon.
Double click the icon to open the table.
The table will open and show the Excel data.
When you make changes to the Excel spredsheet it will be reflected back in your MS Access table, even if both the table and spreadsheet are currently open.
You could even graph the data in Access.
Supposing you don’t want to link to the spreadsheet, but want to keep all the data you have from it and create your own table. This is easily done within Access by creating a ‘Make Table Query’.
I will show you this in the accompanying video.
I hope this has given you an insight of the power of MS Access Excel linking.