MS Access - Data Import

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 pst, 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.

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 (depmited 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.

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.

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

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 cpck Ok.

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

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

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 cpck on the MiddleInitial field.

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

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

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

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.

Example

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

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

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, cpck Ok.

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.

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

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.