Converting an Excel List into an Access Database

If you have the Professional or Premium Edition of Microsoft Office 2000, you also have Access, Microsoft's relational database management system.

If you have been working with lists in Excel for a while, you might be wondering whether your lists are compatible with Access, and when, if ever, you should move up to a more sophisticated database. What are the real differences between these two products? The short answer is that Excel is perfectly suited to list management as long as your databases don't become too large and you don't need to track unusual data or run especially advanced commands. However, Excel has the following limitations when it's dealing with databases:

  • Worksheets are limited to 65,536 rows, meaning that you can't have more than 65,535 records (names in your mailing list, sales transactions, and the like).
  • Fields can contain a maximum of 256 characters, limiting you to shorter descriptions or notes in your lists.
  • Excel can't store pictures, sounds, and other types of special data in fields.
  • Excel lacks advanced data protection or sophisticated backup features.
  • You can't create custom data entry forms without using Access.

If you'd like to move your list into Access in the future, be assured that the transition will be relatively painless. To convert your Excel list into an Access database, you start Access 2000, click the More Files entry in the first Access dialog box, and use the Open dialog box to locate and open your Excel workbook. (If Access is already running, you can use the Import command on the Get External Data submenu of the File menu.)

When you open an Excel workbook in Access, Access launches a wizard that saves your list as an Access table. (You need to specify the worksheet that the list is in to complete the conversion.) Figure 22-8 shows the Pivot.xls worksheet used in this chapter organized as an Access database.

After you import and save the table, you'll find many familiar data management and formatting commands on the Access menus.

click to view at full size.

Figure 22-8. Excel lists appear as database tables in Microsoft Access. The simple conversion process is handled by an Access wizard.

Running Microsoft Office 2000 Small Business
Running Microsoft Office 2000
ISBN: 1572319585
EAN: 2147483647
Year: 2005
Pages: 228 © 2008-2017.
If you may any questions please contact us: