Remove .mdb databases in excel

Question

Hey,

In a workbook, I have created several pivot tables whose data source are imported from external .mdb files. Everytime I imported a .mdb database, the workbook size increases. Now the whole workbook is 700mb, which is too large for me.

I tried to remove the connections between pivot and .mdb databases since I only need the tables and background database is useless for me. However, the file size doesn't change at all---meaning the databases are still somewhere in the workbook, even you remove all the connections.

Does anyone know how to remove the background databases to reduce the file size?

Thanks!

Chris

Answer 1

Hey Parth,

Thanks for being willing to help. Maybe I did not do a good job in explaining my issues. Let me try to do it again:

Suppose right now I have 10 pivot tables: P1, P2,....P10. They are linking to the same file---"Data.mdb" in my computer. The thing is that, when you set up the 10 links independently, the data is loaded 10 times which is not necessary at all. So essentially I want the data to be loaded only once so that the 10 pivot are sharing the same one. By doing that, the filesize could be greatly reduced. 

Let me know if you have any questions.

Thanks!

Chris

Answer 2

Hey d0gknees,

Thank you for your reply! Let me give me an example on this:

Suppose right now I have 10 pivot tables: P1, P2,....P10. They are linking to the same file---"Data.mdb" in my computer. The thing is that, when you set up the 10 links independently, the data is loaded 10 times which is not necessary at all. So essentially I want the data to be loaded only once so that the 10 pivot are sharing the same one. By doing that, the filesize could be greatly reduced. 

Let me know if you have any suggestions.

Thanks!

Chris

Answer 3

If you are using Excel 2010 or above you should look into using PowerPivot.  With my experience once the data is loaded into a data model it will reduce file size by roughly 85%.  Just doing the math this would reduce your file size to a more manageable size of:  105 MB.

Here are a few good links to get you started:

PowerPivotPro

PowerPivot For The Data Analyst - Bill Jelen

Answer 4

If you were to remove the Access data from the file, your pivot tables would have no data to work with. Why is 700 MB too large?

Answer 5

Hello Chris,

For the query related to Pivot table and exporting data from external .mdb files, you may post your query in Excel IT Pro using the forum link below for better suggestion.

https://social.technet.microsoft.com/Forums/office/en-us/home?forum=excel

Hope this information is helpful. Please feel free to reply in case you face any other issues with Office in future.

Thank you.