Importing from Another Access Database
You can import objects (for example, tables, queries, reports) from one Access database into another. When you import an object, you are making a copy of the object. Any changes you make to the imported object do not affect the original object.
Import an Access Table
To see how to import an Access table object, follow these steps:
- Open the database into which you want to import the table.
- While viewing the list of tables, right-click anywhere within the Navigation Pane and select Import. (Alternatively, you can select Access from the Import & Link group on the External Data tab of the Ribbon.)
- Select Access Database from the flyout menu. The Get External Data - Access Database dialog box appears.
- Select the folder where the Microsoft Access database you want to import is located.
- Double-click the database file that contains the object you want to import.
- Specify how and where you want to store the data in the current database (whether you want to import or link to the table).
- Click OK. The Import Objects dialog appears.
- Select the Table tab.
- Select the table from the list of tables.
- Click the Options button. The Import Objects dialog appears.
- Select the desired options. Options include whether you want to import relationships, menus and toolbars, and import and export specifications. You can also designate whether you want to import just the table definitions, or the table definitions and the data. Finally, you can opt to import the queries as either queries, or as tables (the result of executing the queries). Generally, you will leave all these options at their default values, although you might want to modify them for specific applications.
- Click OK to complete the process.
Importing Spreadsheet Data
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.
- With Tables selected as the object type, right-click anywhere in the Navigation Pane and choose Import from the context menu. (Alternatively, you can select Excel from the Import & Link group on the External Data tab of the Ribbon.) The flyout menu appears.
- Select Excel from the flyout menu. The Get External Data - Excel Spreadsheet dialog appears.
- Use the Browse button to select the Excel file that you want to import.
- Specify how and where you want to store the data in the current database (for example, Import the Source Data into a New Table in the Current Database).
- Click OK. The Import Spreadsheet Wizard appears.
- 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 continues.
If you plan to import spreadsheet data on a regular basis, it is helpful to define a named range in the Excel spreadsheet, containing the data you wish to import. You can then easily opt to import the named range in step 6 each time that you execute the import process.
Finally, you shouldn't give a control the same name as its control source. Access gives a bound control the same name as its field, and you need to change this name to avoid problems. If you fail to do so, and you reference the field in a formula for the control, #error# will appear on the report in the place of the data for that field. Following these simple warnings will spare you a lot of grief.
- Select First Row Contains Column Headings, if appropriate. Notice that the first row appears as column headings rather than data. Click Next. The wizard appears.
- 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. The wizard appears.
- If your data has a column that is appropriate for the primary key, select Choose My Own Primary Key. Otherwise, select Let Access Add a Primary Key.
- If you opted to choose your own primary key, select the field from the drop-down box 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.
- Click Finish.
- Click OK.
In this tutorial:
- Sharing Data with Other Applications
- What Is External Data?
- Looking at Supported File Formats
- Exporting to an Excel Spreadsheet
- Exporting to ASCII
- Importing from Another Access Database
- Importing ASCII Data
- Linking to Tables in Another Access Database
- Linking to Another Type of Database
- Linking to SQL Server Databases
- The Linked Table Manager