2 gb limit and linked tables

SJ · External Since: Oct 26, 2009 Posts: 1

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 · External Since: Jan 29, 2004 Posts: 2505

On Mon, 26 Oct 2009 16:40:01 -0400, "SJ" <sj RemoveThis @noMail.com> wrote:

>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.
--

John W. Vinson [MVP]

Larry Linson · External Since: Jun 24, 2004 Posts: 1875

"SJ" <sj RemoveThis @noMail.com> wrote in message
news:Ounu9xnVKHA.5368@TK2MSFTNGP02.phx.gbl...
>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