Are you willing to store some or all of your data from one or more Excel spreadsheet in Access? If yes, then you are at the right place. As this tutorial is written with the main prospective of guiding you on how to import MS Excel Data INTO MS Access 2016. So, that you can manage the complete database more efficiently and easily. So what are you waiting for..!
Let’s start conquering how it is to be done!
With the vast growth of organization it’s quiet common for the organizations to starts maintaining it’s database in Excel spreadsheet. Switching between your excel and access database for report can be confusing and tiresome work style. So, what’s your opinion about importing Excel spreadsheet into Access. Isn’t it cool…!
As we all know data has a way of accumulating in various formats, file and location which makes tough task to actually get any use out of it. But, if it is kept in one place then its quiet easier to access them. Isn’t it….? If you have data in a spreadsheet you can import it into an Access database with just few simple steps and surely this process will making your database accessing much better, Accessible.
Well Access has some great wizards that make the importing process easy. Want to know how it is to be done? Then start following these mentioned steps and easily import your Excel data into Access 2016 for more convenient way of working style. But before that you need to understand what this importing data from excel is actually?
Understand Importing Data From Excel
If you want to store some of your data from one or more Excel spreadsheet in access then you should import the content of worksheet into a new or existing Access database. When you import data Access creates a copy of data in a new or existing table without making any changes to the source Excel worksheet.
- There is no such way to save an Excel workbook as an Access database. Excel doesn’t have such options to create Access database from Excel data.
- When you open an Excel workbook in Access (in the File Open dialog box, change the Files of Type list box to Microsoft Office Excel Files and select the file you want), access only creates a link to the workbook not imports its data. Linking to workbook is fundamentally different from importing worksheet into a database.
You may also like:
Common Scenarios For Importing Excel Data Into Access:
- You use Access to manage your data but the weekly report which you are getting from your team worker is actually in Excel workbook. At that time you may like to streamline the import process to ensure that data is imported every week at a specific time into your database.
- You are using Excel from long time but going forward you need to use access to work with this data. In such case you need to move the data in your Excel worksheet into one or more new Access databases.
- Your workgroup or department uses Access but you occasionally receive data in excel format that must be merged with your Access database. In such situation you want to import these Excel worksheet into your database as you receive them.
Steps To Import MS Excel Data Into MS Access 2016 Database
If you don’t have any idea regarding Import Excel Into Access then don’t get worried. Just follow down the complete steps to know How To Import Data From Excel To Access.
Step 1: Begin the Excel Import Process
- First of all open the access database that will hold the imported data and click the External Data tab on the Ribbon.
- The Import And Link Group of buttons appear on the ribbon. Each buttons is connected to a wizard that walks you through the process:
File format such as Excel, Access , ODBC database etc. have their own specific button; you can also find other common file format using the more button.
- Double click the Excel button icon from that Import And Link Group ribbon to begin the excel import process.
Step 2: Select the Source and Destination
- Get External Data dialog box specific to the selected file format appears onscreen.
- Here click to the browse option to search for the file you would like to import. Once you have located the correct file, click the Open button.
- On the bottom half of your screen, you will get the option of import destination.
- We need to import an existing Excel spreadsheet to a new access database so we will choose “Import the source data into a new table in the current database.” Option.
Other options are also there in this Get External Data dialog box that allows you to:
- Link your database to an Excel so that any changes in the source sheet will also reflect in both database.
- Import data into an existing database table.
After selecting the correct file and option click the OK option to continue.
Step 3: Selection for the data storage
In this section you need to select the method of data storage. By this way you are telling the access whether to import or link the data.
Step 4: Complete the Remaining steps
Follow the prompts carefully and complete the remaining steps in the get external data dialog box, at last click to the Finish option. The worst that happen is that you get an imported table full of gibberish. If you do then check the format of the source file.
Step 5: Name Your Table
You need to provide access with a name to reference your table. Enter this into the appropriate field and click on the Finish button.
Step 6: Save the import
To save the import click Save Import as clicking to this option will complete the import process and save the import for future use.
The checkbox will be located on the last screen of the wizard. After you check the box, you will be prompted for a name for your import. If you import this data on a regular basis, click the Create Outlook Task check box. Access will set up an outlook task for you that include a button to run the task from outlook. Access will set up an Outlook task for you that include a button to run the task from Outlook.
You will then go back to the main database screen where you can view your data by simply double clicking on the table name in the left panel. Congratulation now you must have get the idea on how to import your data from Excel into Access!
Linking An Access Database With An Excel Spreadsheet
Linking an access database to data in another program, you can make use of the querying and reporting tools that Access provides without maintaining a copy of the excel data in your database. So when you link your Access Database to an Excel worksheet, it creates a new table that is linked to source cells. If any modification done to the source cell of excel appear in linked table. Though you can’t edit the content of corresponding table in access. In order to do any modification in data, you need to make changes in the source file.
Why you need to Link Access To An Excel Worksheet?
Generally, you link to an excel worksheet for following of the two reasons:
- When you want to keep your data in Excel worksheets, but along with that also wants to use the powerful queries and reporting features of Access.
- Chances are also that your workspace uses access, but data from external sources that you work with is in excel worksheets. You don’t want to make more copies of external data, but want to be to work with it in access.
How To Link An Access Database With An Excel Spreadsheet?
- At first open your Access Database and look for the import/link wizard in your respective Access Database. And the location of this wizard in each of the Access version may differ.
- In latest version of office 365 subscription version of Access, on external data tab, import and link group, tap to the New Data Source> From File > Excel.
- If you are an Access 2016, Access 2013, or Access 2010 user then, on the External Datatab, in the Import & Link group, tap to the Excel option.
- For Access 2007 user, go to the External Data tab, in the Import group, tap to the Excel option.
- Now on the Get External Data – Excel Spreadsheet dialog box, in the File name box, don’t forget to specify the name of the Excel source file.
- Choose the Link to the data source by creating a linked table, after then click OK.
The Link Spreadsheet Wizard starts and helps you throughout the linking process.
- In the opened page of wizard, make selection of the worksheet or a named range and tap to the Next option.
- If first row of source worksheet or range contains field names, choose First row contains column headings. Access uses these column headings to name fields in table, if column name include certain special characters, it can’t be used as field name in access. In such case error message is displayed that tells you access will assign a valid name for fields. Hit Ok to continue further.
- At last, assign name for the linked tables and click to the Finish If the table name already exists, you are asked to overwrite the existing table or query. You can tap to the overwrite option if you want to overwrite the existing table or query. Hit the Yes option, to overwrite the table or query, or else press the No option and assign some different option.
- Access creates the linked table. If the operation succeeds, Access displays the finished linking table message. Open the liked table and review the fields and data to ensure you see the correct data in all fields.
If in case you are encountering some error values or incorrect data, you must troubleshoot the source data.
Now you must have understood very well that how to Import MS Excel Data Into MS Access easily. So, try that steps and easily resolve those importation issues of your Excel data into Access. If you are getting stuck at any issue while handling your Access Database and looking for some helpful Access tutorial then check out our tutorial section.