Creating a new Access database from an Excel spreadsheet

Applies to
Microsoft Office Access 2003
Microsoft Access 2000 and 2002

The procedure in this article creates a new database by first exporting data from the Northwind.mdb sample database into Microsoft Excel, and then getting that information into Access. You can do this by either importing or by linking that data into a new database table.

ShowI want to use my own Excel spreadsheet

If you'd prefer importing or linking your own Excel spreadsheet data, do the following:

  1. In Access, point to Get External Data on the File menu, and click either Import or Link Tables.
  2. In the dialog box that appears, browse to and select your Excel file, and click either Import or Link.
  3. Skip directly to step 6 in the following procedure.

To export the table to Excel and create the database in Access    

  1. Open the Northwind sample database.

ShowI can't find the Northwind.mdb database file

The default folder location of the Northwind sample database is as follows, for these versions of Access:

  • Access 2003    \Program Files\Microsoft Office\Office11\Samples
  • Access 2002    \Program Files\Microsoft Office\Office10\Samples
  • Access 2000    \Program Files\Microsoft Office\Office\Samples
  1. Export the Employees table to an Excel 97-2003 (*.xls) file by clicking Export on the File menu, and then clicking Microsoft Excel 97-2003 (*.xls) in the Save as type box.

 Note   In Access 2000, select file type Microsoft Excel 97-2000. In Access 2002, select file type Microsoft Excel 97-2002 (*.xls).

  1. Close and then restart Access.
  2. Click Open on the File menu.
  3. Click the arrow to the right of the Look in box, select the path to the Excel file, and then double-click its icon.
  4. On the first page of the wizard, select the First Row Contains Column Headings check box, and then click Next. If you are not using Northwind.mdb, be sure to select the check box only if your data contains column headings in the first row.
  5. On the second page of the wizard:
    • If you are linking data, type a name for the new table in the Linked Table Name box, and then click Finish.
    • If you are importing data, click In a New Table, click Next three times to accept the default values, and type a name for the new table in the Import to Table box. Click Finish.

Access creates and opens a new database. If you used the Link Spreadsheet Wizard, Access linked the data in the Excel spreadsheet file to the new table. In the following graphic, notice the icon that designates it as a linked table from Excel. If you used the Import Spreadsheet Wizard, Access imported the Excel spreadsheet as a new table.

Access Database window showing linked Excel file

 
 
Applies to:
Access 2003