MS Access - Data Import


Advertisements

In this chapter, we will be talking about importing data in Access and what kinds of data you can import using Access. Normally data is stored in various formats, files, and locations, which makes it hard to get and use it. If you have data in a spreadsheet, a SharePoint list, or some other format, you can import it into an Access database with just a few steps, making it much more, easily available in Access.

  • The Save As command is generally used to save a document in another format, so that you can open it in another program.

  • In Access you can’t use the Save As command in the same way, you can save Access objects as other Access objects, but you cannot save an Access database as a spreadsheet file.

  • To save Access as a spreadsheet file, you will need to use the import feature on the External Data tab.

Different Types of Data Access Can Import

To understand what kind of data you can import in the Access data, let us open your database and go to the External Data tab.

Import

In the import & Link group, you can see the different kind of options available for data import in Access. Following are the most commonly used data import formats.

  • Microsoft Office Excel
  • Microsoft Office Access
  • ODBC Databases (For example, SQL Server)
  • Text files (delimited or fixed-width)
  • XML Files

Example

Let us look at a simple example of data importing from an Excel file. Here is the data in Access file.

Access File

To import the data in Access, we first need to open the Access database and then go to the External Data tab as in the following screenshot.

External Data

In Import & Link group, you will see an option Excel. Let us click on that option.

Link Group

Browse the Excel file from which you want to import data and the then we have different options to store data. Let us select the first option and click Ok.

Spreadsheet

Here you will see the preview of your data. Now, click Next.

Preview Data

In the Preview, you can now see that the first row contains the column headings. Let us now check the check box and click Next.

Field Option

You will now see a dialog box where you can set the data type for each column/field. If you don’t want to import any field, just check the check box which says do not import field. Once you are done with the FirstName field, just click on the MiddleInitial field.

MiddleInitial

Let us now go through all the fields and then, click Next.

Select Option

Here are the different options for primary key. Let us select the first option and click Next.

TblContacts

In the last dialog box, you can enter the table name of your choice and click Finish.

Last Dialog Box

If you want to save all these steps, then check the checkbox and close the dialog box.

Let us now go to the Navigation pane. You will see a new table is added here and when you open the newly added table you will see all of your data in Access.

Data In Access

Example

Let us now look at another example of importing data from the Access database. Let us go to the External Tab again.

External Tab

In Import & Link group, click on the Access option.

Access Database

Browse the Access database from which you want to import the data and then select the first option which says Import tables, queries, form etc. Now, click Ok.

Import Objects

In the above dialog box, you can see different tabs for Tables, Queries, Forms etc. from where you can select what kind of data you want to import.

Import Reports

Let us go to the Reports tab and select any report you want to import; you can also select all the data by clicking on the Select All button. Let us select Projects and click Ok.

Select All

Now, close the dialog box. In the navigation pane, you will see that a new report is added. Let us open this report and you will see all the data in that report.

New Report
Advertisements