- 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
If you want to work with data in Access, but still maintain the data in Excel, you need to link to the data rather than import it. This process is a bit shorter:
-
Create a blank database or open an existing file in Access.
-
Select File, Get External Data, Link Tables.
-
Select Microsoft Excel as the file type.
-
Select a worksheet or named range to import (see Figure 4), and then 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.
-
Enter a name for the table (or accept the default name that Access suggests), click Finish, and click OK.
)
Figure
4Now you have an Access table that looks almost exactly like the imported table. The advantage is that it maintains a live link to the Excel worksheet and can be edited in either application.
But there are some caveats.
While it's possible to have the same data open in Excel and Access at the same time, it's not a good idea. If the table is open in Access and you try to open the original Excel workbook at the same time, Excel will say that it can't access the file. If you have the Excel file open, you can open the Access table to which the Excel data is linked. But if you edit a cell in the Excel workbook, the data disappears from the Access table and you get errors where the data should be.
If you have the linked Excel worksheet and Access table open on the same computer, you may get an error saying that Excel is busy (see Figure 5). As soon as you cancel the cell edit, however, the error should go away and the data should appear correctly in Access.
)
Figure
5If you close the Excel workbook while the linked table is open in Access, all of the data is replaced by errors and Access reports that the connection is lost (see Figure 6). If you click OK several times, however, and then close the table and reopen it, the link is restored.
)
Figure
6Another problem you may encounter is datatype mismatch. In our inventory example, the serial number field has some entries that are a mix of letters and numbers, and other entries that are just numbers. If you leave the number format set to General in Excel, the application has no trouble dealing with a mix of data. But when the table is linked into Access, you get a #Num! error (see Figure 7). The easiest way to fix this problem for existing Excel data—although this method isn't quick if you have a large number of records—is to place a single quote (') in front of each number in Excel. This tells Excel to treat the cell as text regardless of the cell contents. If you plan ahead, you can format a column as text before entering the data, but once the data is entered, changing the format has no effect on the data itself.
)
Figure
7You can add or edit records directly in linked tables, but not delete them. If you add data in Access, it won't necessarily be formatted the same way in Excel as previously entered rows (see Figure 8). You can fix this problem in Excel by copying formatting with the Format Painter, or use a data-entry form to control formatting as the data is entered. Forms also allow you to view the data one record at a time. I'll talk about forms shortly.
)
Figure
8