This Paper A short summary of this paper 0 Full PDFs related to this paper You can bring the data from an Excel workbook into Access databases in many ways. You can copy data from an open worksheet and paste it into an Access datasheet, import a worksheet into a new or existing table, or link to a worksheet from an Access database. This topic explains in detail how to import or link to Excel data from Access desktop databases. If your goal is to store some or all of your data from one or more Excel worksheets in Access, you should import the contents of the worksheet into a new or existing Access database. When you import data, Access creates a copy of the data in a new or existing table without altering the source Excel worksheet.
The steps in this section explain how to prepare for and run an import operation, and how to save the import settings as a specification for later reuse. As you proceed, remember that you can import data from only one worksheet at a time. You cannot import all the data from a whole workbook at the same time.
See Save the details of an import or export operation as a specification to learn how to save your save your specification details. See Run a saved import or export specification to learn how to run your saved import or link specifications. See Schedule an import or export specification to learn how to schedule import and link tasks to run at specific times. If you receive the message An error occurred trying to import file, the import operation completely failed. Conversely, if the import operation displays a dialog box that prompts you to save the details of the operation, the operation was able to import all or some of the data. The status message also mentions the name of the error log table that contains the description of any errors that occurred during the import operation.
Important: Even if the status message indicates a completely successful operation, you should review the contents and structure of the table to ensure that everything looks correct before you start using the table.
The following table describes the steps that you can take to correct missing or incorrect values.
Tip: While you are troubleshooting the results, if you find just a few missing values, you can add them to the table manually. Conversely, if you find that entire columns or a large number of values are either missing or were not imported properly, you should correct the problem in the source file. After you have corrected all known problems, repeat the import operation.
In addition, you might want to review the error log table (mentioned in the last page of the wizard) in Datasheet view. The table has three fields — Error, Field, and Row. Each row contains information about a specific error, and the contents of the Error field should help you troubleshoot the problem.
Top of Page By linking an Access database to data in another program, you can use the querying and reporting tools that Access provides without having to maintain a copy of the Excel data in your database. When you link to an Excel worksheet or a named range, Access creates a new table that is linked to the source cells. Any changes that you make to the source cells in Excel appear in the linked table. However, you cannot edit the contents of the corresponding table in Access. If you want to add, edit, or delete data, you must make the changes in the source file. Typically, you link to an Excel worksheet (instead of importing) for the following reasons:
You are now ready to start the linking operation.
Top of Page Even if you receive the message Finished linking table, you should open the table in Datasheet view to ensure that the rows and columns show the correct data. If you see errors or incorrect data anywhere in the table, take correct action as described in the following table, and then try linking again. Remember that you cannot add the values directly to the linked table, because the table is read-only.
|