2 gb limit and linked tables

[SJ]

I have an Access app that allows users to point to an Access database with
the source data and links the tables. For example Table1, Table2, Table3
(etc) of the mdb file the user selected are linked to the front end
database.

However, some of the datafiles are over 2GB in size and are broken in to two
databases. I need to combine the data of the two separate files that
exceed the 2GB threshold into one and link into my front end database.

I have some ideas, but am open to suggestions.

 

[John W. Vinson]

However, some of the datafiles are over 2GB in size and are broken in to two
databases. I need to combine the data of the two separate files that
exceed the 2GB threshold into one and link into my front end database.

The 2GByte limit applies only to the database containing the actual tables.
Links take up only a tiny amount of space, and the limit would not apply.

You can use File... Get External Data... Link from a table-less frontend to
link to two separate backends; you can use a UNION query in your frontend to
stitch the two huge tables together into one recordset. It won't be updateable
though; if you need both one (virtual?) table AND need it updateable... and
probably in ANY case... you should consider moving these tables to SQL/Server.

 

[Larry Linson]

I have an Access app that allows users to point to an Access database with
the source data and links the tables. For example Table1, Table2, Table3
(etc) of the mdb file the user selected are linked to the front end
database.

However, some of the datafiles are over 2GB in size and are broken in to
two databases. I need to combine the data of the two separate files that
exceed the 2GB threshold into one and link into my front end database.

I have some ideas, but am open to suggestions.

If you have _Tables_ that would exceed 2GB, you'll need to store your data
in a different kind of database (e.g., MySQL and PostgreSQL are two free,
open-source server databases; MS SQL Server Express Edition is a free, but
not open-source, server database which can hold up to 4GB, twice as much as
the Jet and ACE database engines that come with Access. The nice part about
that is that you can still use Access as the client application, and link to
these or any other ODBC-compliant database.

Larry Linson
Microsoft Office Access MVP