Importing Data From Access To Excel

I spend a great deal of time moving data from one application to another. A good example of this is when I need to import data from Access into Excel.


Now I could of course copy my data then paste special CSV format my data from Access to Excel. I do this quite often when my dataset is less than one or two thousand rows. But what if my dataset is quite large (say 500,000 rows)? The clipboard only will hold around 64 thousand rows.


Well Excel allows you to easily import data from Access with just a few steps. Best of all, you can record these steps into a macro for future use (such as a weekly report).


To start you need to make sure the data is in a list with no blank rows or columns within the list. This is easily done with Access as basic datasheets and queries in Access are already formatted this way.


Next from the Data tab, select From Access.

You will be prompted to select the data source (i.e. the Access database with the information you want). I select the drive and folder where my database is and then double click on the database. This opens the Data Link Properties dialog box.

Now this has four tabs (provider, Connection, Advanced and All).

Ensure Microsoft Office 12.0 Access Database Engine is selected on the Provider Tab.

The Connection tab will display your data source (i.e. database name and location). In my example the database is called test.mdb and is located on my C drive.

If you need a user name and PW that is different from the server you are on, you can enter it on this tab. The Connection tab also has a Test Connection button. Click on the button to ensure that Excel can get to the data. If Excel can, you will get a “Test Connection Succeeded” message. If it does not succeed, you will get a “Test connection failed message”. Now depending on how your network security is configured, you may need to tweak a setting or two to make this work. Also if the Access database is protected by a password, you will be prompted to enter it.

Out of the gate my connection never works. However, since I am only interested in “Reading” data from Access, I click on the Advanced tab and check Read in Access permissions. I also uncheck all other boxes. I find that this one step resolves 99 percent of my connection issues.

I can click back on the Connection tab and test again. This time with my Read Access permissions I am successful.

Once I have a successful test, I click on the OK button at the bottom of the Data Link Properties Dialog Box.

The Select Table Dialog Box will appear. Now this will display all queries and tables that you have the right to use.

Clicking the OK button will cause the Import Data Dialog Box to display. Here you can choose how you want to display the imported data and where you want the data to go in your worksheet.

Clicking the OK button will complete the importing of the Data.

However if you need to have the data refreshed at a given time interval you can select the Properties button. Here you can choose how often the data is updated. There are also options on how to handle the data on the Connections Properties Dialog Box.

So importing data from Access to Excel is not difficult at all. Give it a try the next time someone on your team wants to copy and paste large amounts of data from Access to Excel and “Wow” them with your mad crazy Excel skills.