- Basic Excel Databases
- Importing Excel Data into Access
- Linking Excel Data into Access
- Creating an Access Form
- Using the Microsoft Accesslinks Add-In
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Your best solution in the long run might be to import the Excel data into an Access table and never look back. With this strategy, you can use the full features of Access and plan for a more complex database without the limitations that linking imposes. (More on linking in a bit.) Several steps are involved, but the process isn't particularly complex:
-
Open Access and create a blank database.
-
From the Access menu, select File, Get External Data, Import.
-
Select Microsoft Excel as the file type.
-
Select a worksheet or named range to import (see Figure 2) and click Next. You can import only one worksheet or named range at a time, and each one will become an Access table.
-
In the next dialog box, select or deselect the check box First Row Contains Column Headings, depending on whether your worksheet has headings. Then click Next.
-
Specify whether you want to store the data in a new table or append it to an existing table (see Figure 3). You can append the data to an existing table if the fields in the Access table match the column headings in Excel. Click Next.
-
In the next dialog box, you can choose to exclude fields, rename them, or set indexing. When the settings are the way you want them, click Next.
-
Click Next again to let Access create a unique key for each record.
-
Enter a name for the table (or accept the default name that Access suggests), click Finish, and click OK.
)
Figure
2)
Figure
3You end up with a static Access table that has no live link to the original Excel worksheet.