Access tables

[kgamfo]

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

 

[Joe4]

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]

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]

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]

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]

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]

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]

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.