About this lesson
Import data from Excel spreadsheets into tables in Access.
Download this lesson’s related exercise files.Services_08_start.accdb
1.1 MB 08_ExcelToImport.xlsx
14.3 KB Services_08_Import_Excel.accdb
1.2 MB RelationshipsDiagram_08.docx
Import Data from Excel
Import from Excel
Most Access databases start out in Excel. The data then grows to the point where it is more efficient to work with it in Access. To import data from Excel, choose Excel from the Import & Link group on the External Data ribbon. When linking, data is read-only.
When importing from Excel, a Worksheet or Range (Named Range) can be specified.
Be sure to check the "First Row Contains Column Headings" box. If column headings in Excel contain illegal characters for field names, or they are too long, Access will create ambiguous fieldnames that you can later rename.
If you choose to let Access adds a Primary Key, it will be an AutoNumber, which is a special form of Long Integer. This is a good idea so you can refer back to original data by storing an ImportID to tables where data is moved.
When naming newly imported tabled, preface the names with "Import_" so they are easier to find.
Select Multiple Records
To select multiple records in Access, Click on the first record. If you can see the last record you want to delete, you can click and drag in the record selector box. If you cannot see all the record you want to select, Click on the first record, then scroll, and Shift-Click on the last record.
To delete one record in Access, you can right-click on the record selector box and choose DELETE from the shortcut menu. You can also click in the record selector box to select the record then press DELETE on your keyboard.
If you have multiple records selected, press the DELETE key on your keyboard to delete them all at once.
Printing the Relationships Diagram is a great way to document your database. Press the Print Screen Key to put an image of your screen on the Windows Clipboard so that you can paste into another Windows application for printing.
Import from Excel
- Click on the External Data ribbon
- Choose Excel from the Import & Link group
- Browse to the Excel file you want to import from
- When the wizard comes up, pick the objects that you want
- Open each table and delete blank columns and rows you do not want
Lesson notes are only available for subscribers.