You can easily import an Excel spreadsheet into an Access database. To do so, follow these steps:
Open the database into which you want to import the spreadsheet.
Right-click anywhere in the Database window and choose Import from the context menu. The Import dialog box appears.
Select the folder where the spreadsheet file you want to import is located.
From the Files of Type drop-down list box, select Microsoft Excel.
Double-click the spreadsheet you want to import.
Select Show Worksheets or Show Named Ranges (Access does not display this step of the wizard if the spreadsheet contains only one worksheet) and then click Next. The Import Spreadsheet Wizard appears.
If you plan to import spreadsheet data on a regular basis, it is helpful to define in the Excel spreadsheet a named range that contains the data you want to import. You can then easily opt to import the named range in step 6 each time you execute the import process.
Select First Row Contains Column Headings, if appropriate (see Figure 18.7). Notice in Figure 18.7 that the first row appears as column headings rather than data. Click Next.
Select In a New Table, and then click Next (see Figure 18.8). This means that Access will place the data you are importing into a new table rather than into an existing table.
Type the field name in the Field Name text box, if necessary.
Select whether you want Access to index the field.
Indicate whether to import a field by selecting the Do Not Import option for that field, if desired.
Click in the field list to select the next field.
Repeat steps 9?12 as appropriate for each field and then click Next.
If your data has a column that is appropriate for the primary key, select Choose My Own Primary Key. Otherwise, select Let Access Add Primary Key.
If you opted to choose your own primary key, select from the drop-down box the field that you want Access to use as the primary key, and then click Next.
Type the table name in the Import to Table text box.