4.2. Linking Excel Data

 < Day Day Up > 

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, so you might want to abbreviate a product name in your database and upset the person in charge of maintaining the product list in the Excel workbook.

As mentioned at the beginning of the chapter, it is also helpful to link Excel worksheets when you want to find items that are exclusively in one worksheet. Access provides a Find Unmatched Query Wizard that allows you to easily find items in one list that are not in another. Let's say you have an Excel worksheet with product information and a primary key of a Product Number, and another worksheet with sales information by product number, sale date, and amount sold. If you want to know which products had no sales, link to the two worksheets and run the Find Unmatched Query Wizard on the linked tables. The resulting query result set would give you the answer, and you could build a report using this data.

Sometimes I use Access as a place to hold data on which to perform analysis, and all of the data entry and validation are done in different places. Access makes easy work of linking, importing, and combining information from various sources, so it is ideal for these types of tasks. A good example is corporate budgeting. Many companies use Excel as a data entry tool for budget information. Assume you are dealing with 100 individual budget files; if you had to look in every workbook to determine whether a particular account was budgeted, or some other nugget of information like that, you would search for a better solution. By using Access and importing all of the information, you can quickly run queries, produce reports, and update data. You can even take the updated data from Access and update the Excel workbooks using VBA.

4.2.1. Combining Linking and Importing

If you want to import data so you do not need to worry about accidentally making changes while you are working with it, but you also want to ensure that you have the most up-to-date information, you can have both. Taking the product list example, if you want to make changes without affecting the original Excel workbook, import the table first and create a link to it. You can then use the imported data in your application while still being able to view and retrieve the data in the original Excel workbook. You must give these tables different names; I sometimes use tbl_TABLENAME and lnktbl_TABLENAME. If you don't, you will be given tbl_TABLENAME and tbl_TABLENAME1. These might be OK, but I have had to go back to the database window enough times while writing queries to figure out which name is which that I find making more descriptive names to be a time saver.

Once you have the two tables in the database, create a Find Unmatched Query to determine what records are missing. This gives you the information in the linked table but not in the local database table. If you need the data locally, create an Append Query using the Unmatched Query as the source and the local table as the destination where you'll put the missing information. If you have to worry about values in the individual fields changing, you could also create an Update Query and update all of the fields of the unconnected table with the values in the linked table. You could have a few lines of code in the Open event of your startup form that would run these queries to automate this process.

While this is useful in Excel, it is also useful when dealing with data from databases. Sometimes you have a large table on a database server, and you want to run a lot of queries locally to reduce impact on server performance. Link to the data and run Import and Update Queries described previously to make sure you have the most up-to-date information.

     < Day Day Up > 


    Integrating Excel and Access
    Integrating Excel and Access
    ISBN: 0596009739
    EAN: 2147483647
    Year: 2005
    Pages: 132

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net