Maximum database size - linked tables in a second database

[Brad]

The maximum database size for Access 2007 is 2GB.
It is possible to link tables from more than one database.
Can I use this linking feature to get around the 2GB limit?
I may need to store 3 GB total (many tables). Can I simply put some tables
in one database and the remaining tables in a second database in order to
store more than 2GB?

Or, does the 2GB limit apply to the total of the two linked databases?

Thanks for your help, I really appreciate it!

 

[ruralguy via AccessMonster.com]

The linking idea you have will work just fine. The 2GB limit is only for one
mdb.

 

[John W. Vinson]

The maximum database size for Access 2007 is 2GB.
It is possible to link tables from more than one database.
Can I use this linking feature to get around the 2GB limit?
Yes.

I may need to store 3 GB total (many tables). Can I simply put some tables
in one database and the remaining tables in a second database in order to
store more than 2GB?

Yes; the downside is that you can't enforce referential integrity between
tables in different databases.

Given the scale of this data you REALLY REALLY should look at SQL/Server. I
believe that the (free) SQL/Server Express has the same 2GByte limit, but
there are many licensing options that should be quite affordable.

If the size is due to storing images (which are large and can cause major
bloat) consider just storing path and filenames in your table, rather than the
whole picture.

 

[Brad]

RG and John,

Thanks for your quick reply. I really appreciate the help. John, you
mentioned that I should take a serious look at SQL-Server. I plan to do so.
This project is for a very small firm with a very limited budget and very
small IT staff. This firm has some older Access tables which have served
them well for several years. I am struggling with the idea of introducing
SQL-Server at this time due to the increased costs and complexity. I was
thinking about using Access for 2-3 years and then introducing SQL-Server.
It is my understanding that migrating the data to SQL-Server while retaining
the Access application layer is a fairly easy task.

Thanks again,
Brad

 

[John W. Vinson]

RG and John,

Thanks for your quick reply. I really appreciate the help. John, you
mentioned that I should take a serious look at SQL-Server. I plan to do so.
This project is for a very small firm with a very limited budget and very
small IT staff. This firm has some older Access tables which have served
them well for several years. I am struggling with the idea of introducing
SQL-Server at this time due to the increased costs and complexity. I was
thinking about using Access for 2-3 years and then introducing SQL-Server.
It is my understanding that migrating the data to SQL-Server while retaining
the Access application layer is a fairly easy task.

Check out Tony Toews' thoughts on the subject:

http://www.granite.ab.ca/access/sqlserverupsizing.htm

It may well be worth using SQL/Express (which is free) as a test bed.