CODEWITHSUNDEEP
excelms-access
Terence Chow
Terence Chow

Reputation: 11153

I have too much data for MS Access. How can I use the data?

I have 4 different excel tables. If I import them all into Access I get an error, related to the 2 gigabyte maximum for Access.

I can import the 4 tables as linked tables. However I cannot open them all at once because again, I'll get the same error. I've tried creating a union query shown below, but again I get the same problem.

Select * from table1 union all select * from table2 union all select * from table3 union all select * from table4;

I'm really at a loss on what I can do. Basically, the 4 tables relate to quarterly sales data and I want to be able to create a pivot table based on the year (either in Excel or Access).

I can combine the 4 tables into 1 excel file, but when trying to create a pivot table I get an error saying there isn't enough RAM on my computer.

What are my options to view this data?

Upvotes: 3

Views: 1048

Answers (1)

Mike A
Mike A

Reputation: 51

SQL Express has a limit of 10GB (and it's free). (from here: http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx )

If you store the data in SQL Express, you can query the data directly from Excel and forgo using MS Access.

Excel has a limit of # rows it can handle, but you can overcome that using the free PowerPivot add-in from Microsoft: http://office.microsoft.com/en-ca/excel-help/power-pivot-add-in-HA101811050.aspx

hope that helps

Mike

Upvotes: 2

Related Questions