Integrating Excel and Access

Linking Excel Data

There are many occasions when you want to work with Excel data as if it were in a table in Access, but you need to keep the source of the data in Excel. (This happens frequently at companies where Access is not on every desktop but everyone has Excel.) I worked with one company who maintained their product list in Excel, although many of the people who needed that information used Access. While you could simply import the data each time you needed it, this could cause problems if you forgot the importing step, causing someone to use old data. Linking to the Excel worksheet instead of importing it allows you to take advantage of the ability to query the data using Access while ensuring that you always have up-to-date information.

The steps for linking versus importing are virtually the same, except that when you right-click on the tables area, you select Link Tables instead of Import. What you have to be careful with and always keep in the back of your mind when linking to a worksheet is that any changes you make will also be made and saved within Excel. Normally, this would be a very good thing, but if you forgot about this feature, you could be very surprised when changes occur. For importing versus linking, I suggest determining how the information will be updated and how important the updates are. Consider the risks of linking and making inadvertent updates. It is easy to forget that you are dealing with data that, in reality, exists outside your application, ...