How to Work with Access in Your Excel

Access and Excel have a lot in common. If you do not have Access on a computer, you can import your data into Excel so that you can work with your data. You can also link Excel workbooks to your database so that your worksheets are always up to date.

Many companies choose to use Microsoft Access for their databases. This can be a great solution for smaller projects, but there is a potential problem with it. Companies usually will not get enough licenses to put Access on every computer.

As long as you can access your MDB file, you still have access to your data. Even if the computer doesn’t have Access on it, it is probable that the computer has Microsoft Excel.

It is also possible to connect your Access data to Excel. By directly connecting a workbook to Access, you will always have the latest updates to your data. This is incredibly useful if you have to work with the data on a daily basis. It is also much simpler than having to regularly export the data from Access.

This article walks you through opening and working with your MDB file in Excel. Keep in mind that this is not the way to conduct a MDB database repair. If there are problems with your Access database file, you should consult an expert to restore it.

Importing Access Data into Excel

If you only need to go over the data, you will be able to do so through Excel. This is the same as simply importing the data from the source. It does not change the data in the database.

All you need to get started is the location of the MDB file.

1. Open Excel, then open a blank workbook.

2. Click on the DATA > Get External Data > From Access.

3. Select the file you would like to open. The choices will be from the different .accdb files. These files are the ones that contain your Access tables. Your tables house all of your data, making this a very targeted approach to retrieving specific data.

4. Click OK.

5. Select the view you would like from the window that displays. This will allow you to review the data in whatever form you need.

6. Save the file so that you can manipulate the data in Excel. This won’t change the database data, but will allow you to manipulate it for reporting or analysis.

Linking Access to Your Workbook

If you spend a lot of time manipulating data in Excel, having to export the data from Access is a part of your regular work. However, there is an easier way to work with your data. It even allows you to work with your data if a computer does not have Access. All you need is to have access to the MDB file.

The following steps walk you through connecting Access to your Excel workbook.

1. Click on the Data tab, then From Access on the Get External Data.

2. Find and select your database.

3. Select the query or table you want to link, then click OK.

4. Update any details for the connection using the Next

5. Click Finish.

6. Select where you want to add the data in your workbook. You will have three choices as to how the data displays:

• Table
• PivotTable report
• PivotChart

7. Click Properties if you need to provide advance connection properties. This includes refreshing the data connection.

8. Click OK.

Now all you have to do to update your workbook data is click on Data > Refresh All. Excel will automatically update the data to match changes made in the database.

Author Introduction:

Victor Ren is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Excel recovery utility and word recovery utility. For more information visit www.datanumen.com