Access 2010: Import Worksheet From Excel 2010

You can share data between Access 2010 and Excel 2010 in many ways. Excel worksheet consists of cells that are organized into columns and rows, Access  recognize them as fields and records. Access 2010 provides an easy way to import Excel worksheets, this post will explain in detail how to import worksheet in Access from Excel 2010.

Open Excel worksheet, make sure that table is in a list format; each column has a title/label in first row and contains similar data types (similar facts), and there is no blank rows and columns.

For Instance: I have created a work sheet, with columns containing title in it’s respective first row, and left no blank cells.

Launch Access, create database. Navigate to External Data tab and click Excel button.

You will see Get External Data dialog box, it shows three different options of how and to where database will be stored. Go through the options (with details) and select one. We need to create a database in which table will be created by Access automatically.

Hit Browse to select Excel worksheet you want to import and click Open.

Now the Import Worksheet Wizards will open up where you can select the desired worksheet from the list you want to import and click Next. A sample data of selected worksheet will be shown as seen in the screenshot below.

Enable the First row Contains Column Headings checkbox. As mentioned earlier, your worksheet must have column’s label in first row, an example can be seen in the screenshot below.

Now specify information about each of the fields you are importing. Select field/column in the area below and  specify corresponding Field Options. We will select ID field and select Data Type as Integer (number only), and from Indexed drop-down menu we will choose No. You can however also choose Yes (Indexed), or Yes (Not Indexed) from the list, depending on your situation. Click Next.

Now choose another field/column to specify Field options.

Next step provides different options of selecting primary key (Unique Identifier). You can enable Let Access add primary key, or choose your own primary key by selecting column/field label from drop-down box. We will select the default option, i.e, Let Access add the primary key. Click Next.

You can enable Save import steps to save the import steps for later use. When done, hit Close.

Excel worksheet has been successfully imported in Access 2010, as you can see from the screenshot below.

To check if data types are correctly weaved with column’s label, right-click on imported table (Addictivetips Example) and click Design View. In this view you can apply different constraints over fields and specify data types.

Read Full Bio

Usman Javaid

Follow me on Google+

Latest from Author

How To Create A Portable Windows 8 Enterprise To Go Workspace How To Install Windows 8 As Guest OS Using VMware Player