Microsoft Office Online
Sign in to My Office Online (What's this?) | Sign in

 
 
Help and How-to
Search
Search
 
Check for updates: (c) Microsoft
Microsoft Update
 
 
 
Help by Product
Find help quickly and easily.
» Select your products
How-to Resources
2007 Release Learning Resources
Support and Feedback
Technical Resources
Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.
Email this linkEmail this link Printer-Friendly VersionPrinter-Friendly Version Bookmark and ShareShare
Link data from Excel to Access with AccessLinks
 
Applies to
Microsoft Excel 2002

If you're working in Microsoft Excel and Microsoft Access, you can use several Access features to maintain your Excel data with the AccessLinks add-in program.

Note  If the Convert to MS Access, MS Access Report or MS Access Form commands are not available from the Data menu, you need to install the AccessLinks add-in, which is available from Downloads on Microsoft Office Online.

Convert an Excel list to an Access database

If you have a large Excel list and you want to take advantage of the Access data management capabilities, security, or multiuser capabilities, you can convert the data from Excel to an Access database. Use this method when you want to move the data from Excel into Access to use and maintain the data in Access from then on.

ShowConvert Excel data to an Access database

  1. Make sure the Excel data is in list format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list.
  2. Click the list.
  3. On the Data menu, click Convert to MS Access.
  4. Click New database.
  5. Click OK, and then follow the directions in the Access Import Spreadsheet Wizard.

    For more information about this wizard, see Microsoft Access Help.

Create an Access report from Excel data

If you are familiar with designing Access reports and want to summarize and organize your Excel data this way, you can create an Access report from the data in your Excel list. For more information about designing and using Access reports, refer to Microsoft Access Help.

ShowCreate an Access report from Excel data

  1. Make sure the Microsoft Excel data is in list format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list.
  2. Click the Excel list.
  3. On the Data menu, click MS Access Report.
  4. If prompted, save the workbook by clicking Save on the File menu, and then click MS Access Report again.
  5. In Microsoft Access, do one of the following:
    • To create the report in a new Access database, click New database.
    • To create the report in an existing Access database, click Existing database, and then type the path to the database in the box under Existing database. To look for the database on your hard disk or network, click Browse.
  6. Click OK, and then follow the instructions in the Access Report Wizard. Do not change the names that the wizard proposes for the table and report.

    For more information about this wizard and Access reports, see Microsoft Access Help.

ShowView or update an existing report

To view the report again in Microsoft Excel, click the View MS Access Report button on the worksheet to the right of your list. If you've added data or changed the data in the list since you created or viewed the report, the report is updated with the changes.

For more information about the report, see Microsoft Access Help.

Use an Access form to enter Excel data

If you want to use a customized form to enter, find, or delete data in an Excel list (a series of worksheet rows that contain related data), you can create an Access form for your list. For example, you can create an Access form that lets you type the entries for an Excel list in a different order from the order of the columns on your worksheet. Use this method if you want the specific features available in Access forms. For more information about designing and using Access forms, refer to Microsoft Access Help.

ShowCreate a new Microsoft Access form

  1. Make sure the existing data on the Microsoft Excel worksheet is in list format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list.
  2. Click a cell in the list.
  3. On the Data menu, click MS Access Form. If prompted, save the workbook by clicking Save on the File menu. Then click MS Access Form again.
  4. In Microsoft Access, do one of the following:
    • To create the form in a new Access database, click New database.
    • To create the form in an existing Access database, click Existing database, and then type the path to the database in the box under Existing database. To look for the database on your system or network, click Browse.
  5. Click OK, and then follow the instructions in the Access Form Wizard. Do not change the names that the wizard supplies for the table and form. For more information about the wizard, see Microsoft Access Help.
  6. When you complete the steps in the wizard, enter data using the form that appears. Click the new record button at the bottom of the form to start entering new data. Each time you enter a new record, Access updates the Excel list. For more information about using an Access form to enter data see Microsoft Access Help.
  7. When you're finished entering data close the form.

ShowReuse an existing Access form

To use the form again in Microsoft Excel, click the View MS Access Form button on the worksheet to the right of the list.

If Microsoft Excel can't find the form, the Create Microsoft Access Form dialog box appears. To look for the Access .mdb file that contains the form, click Browse.

See Also
advertisement
Printer-Friendly VersionPrinter-Friendly Version