Access tables

kgamfo

New Member
Joined
Oct 26, 2011
Messages
2
I have an access database which has over grown to 2.5g it keeps on craching when ever i am runing queries can some one help with any susgestions
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,913
Office Version
  1. 365
Platform
  1. Windows
Which version of Access are you using? I can't speak for the latest versions, but in most version, 2.0 GB is the size limit for Access databases. It sounds like you have exceeded that.

So you may need to re-visit how you are storing your data. You may need to investigate:
- storing the data in a different back-end, like SQL, mySQL, or Oracle
- split the data into multiple databases and link to it

If you already haven't, you should probably split your database into a back-end holding the data only, and a front-end with all your Queries, Forms, and Reports. Access has a built in utility that will do that for you (see Access' built-in Help files).
 

HiTechCoach

Well-known Member
Joined
Aug 29, 2010
Messages
1,008
To get around the 2 gig limit you can also use multiple back ends. The downside is that you can't set the database engine to enforce RI between tables in separate databases.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,913
Office Version
  1. 365
Platform
  1. Windows
To get around the 2 gig limit you can also use multiple back ends. The downside is that you can't set the database engine to enforce RI between tables in separate databases.
Yes, that is what I was recommending with my second option ("split the data into multiple databases and link to it"), but could see how that might not have been too clear.

One thing I have seen some people do is archive old data to another database. So they two back-ends, almost identically structured (same tables), but one has old archived data and one has new data. However, over time, you may need multiple archived databases if those get too big too (maybe an archive for each year).

If your database is really that big, I suspect you may have experience some performance issues as well. So you may want to investigate using something else to hold your data, like SQL or Oracle. You can continue to use Access as your front-end.
 

boblarson

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964

ADVERTISEMENT

I can't speak for the latest versions, but in most version, 2.0 GB is the size limit for Access databases.
Yep, still 2 Gb (including system tables) maximum.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,913
Office Version
  1. 365
Platform
  1. Windows
Thanks Bob.
Do you think they will ever increase it or are there fundamental reasons why they do not want to go past this limit?

Just curious to see if you have any insights or opinions on that...
 

boblarson

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
Thanks Bob.
Do you think they will ever increase it or are there fundamental reasons why they do not want to go past this limit?

Just curious to see if you have any insights or opinions on that...
I know they told us the reasons why they couldn't do it at one of the MVP functions or at the Access Developers Kitchen for Access 2010 when I went. I can't remember the reasons though. I do remember that it wasn't just because they didn't want to. There was some technical reasoning behind that.
 

HiTechCoach

Well-known Member
Joined
Aug 29, 2010
Messages
1,008
Thanks Bob.
Do you think they will ever increase it or are there fundamental reasons why they do not want to go past this limit?

Just curious to see if you have any insights or opinions on that...

I think part of the reason is support for 32-bit OS's and FAT32.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,334
Messages
5,595,567
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top