MS Access data import

In this chapter, we'll discuss importing data in Access, and what types of data you can import with access. U sually the data is stored in different formats, files and locations, which makes it difficult to get and use it. I f you have data in a spreadsheet, SharePoint list, or other format, you can import the Access database in Access just a few steps away to make it more accessible.

• Save as a command is typically used to save a document in another format so that you can open it in another program.

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

• To save Access as a spreadsheet file, you'll need an external data tab that uses the import feature.

Access different types of data import

To find out what kind of data you can import into Access data, open the database and go to the External Data tab.

In import and link groups, you can see different types of options in Access that you can use to import data. The 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's look at a simple example of importing data from an Excel file. This is the data in the Access file.

To import data in Access, we first need to open the Access database and then go to the external data tab, as shown in the screenshot below.

In the Import and Link group, you'll see an option Excel. Let's click on that option.

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

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

In the preview, you can now see that the first row contains the column title. N ow let's check the box and click Next.

In the preview, you can now see that the first row contains the column title. Now let's check the box and click Next.

Now let's go through all the fields and click Next.

The following are the different options for the primary key. Let's select the first option and click Next.

In the last dialog box, you can enter the selected table name and click Done.

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

Let's go to the navigation pane now. You will see a new table added here, and when you open the newly added table, you will see all the data in Access.

Example

Let's look at another example of importing data from the Access database. Let's go to the external tab again.

In the import and link groups, click the access option.

Browse the Access database from which you want to import data, and then select the first option for importing tables, queries, forms, and so on. Now, click OK.

In the dialog box above, you can see different tables, queries, forms, and other tabs from which you can choose which data to import.

Let's go to the Reports tab and select any reports you want to import; Y ou can also select all the data by clicking the all-select button. Let's select the project and click OK.

Now, close the dialog box. I n the navigation pane, you'll see that a new report has been added. Let's open this report and you'll see all the data in that report.