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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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).
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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
Back
Top