Can Jet Handle Data From Separate Databases With a Combined Volume > 2Gb?

[Mark Andrews]

I understand that an mdb file can not be greater than 2Gb in size, so... if
there were several databases with, say, nearly 2Gb in each then I guess
there is no problem linking a front end to each of the mdbs, but... can jet
handle a query in the front end that links some of those tables together to
produce a recordset containing the equivalent of far greater than 2gb worth
of data..??? (Assuming adequate system resources.)

A more direct example would be trying to union 2gb of data from two or more
databases - is the limitation purely on the mdb size or ultimately with the
volume of data jet can handle regardless of how you spread the data out.

I have a short term issue and an extremely complex access system that makes
upsizing to SQL server a non-starter. Can I effectively spread more than 2Gb
of data over several mdbs but combine it with queries / recordsets?

Uncharted territory perhaps but needs must!

Any thoughts/experience greatly appreciated.

Mark Andrews

 

[Eric Butts [MSFT]]

Hi Mark,

A quick answer to your question is no. JET uses temporary tables (in
cache) when running data manipulation and inquires (i.e., select queries).
In the event your query exceeds the 2 gig limit JET will fail.

Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights

--------------------
| Reply-To: "Mark Andrews" <[email protected]>
| From: "Mark Andrews" <[email protected]>
| Subject: Can Jet Handle Data From Separate Databases With a Combined
Volume > 2Gb?
| Date: Thu, 10 Jun 2004 20:43:10 +0100
| Lines: 23
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.access.setupconfig
| NNTP-Posting-Host: host81-130-115-215.in-addr.btopenworld.com
81.130.115.215
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.setupconfig:30345
| X-Tomcat-NG: microsoft.public.access.setupconfig
|
| I understand that an mdb file can not be greater than 2Gb in size, so...
if
| there were several databases with, say, nearly 2Gb in each then I guess
| there is no problem linking a front end to each of the mdbs, but... can
jet
| handle a query in the front end that links some of those tables together
to
| produce a recordset containing the equivalent of far greater than 2gb
worth
| of data..??? (Assuming adequate system resources.)
|
| A more direct example would be trying to union 2gb of data from two or
more
| databases - is the limitation purely on the mdb size or ultimately with
the
| volume of data jet can handle regardless of how you spread the data out.
|
| I have a short term issue and an extremely complex access system that
makes
| upsizing to SQL server a non-starter. Can I effectively spread more than
2Gb
| of data over several mdbs but combine it with queries / recordsets?
|
| Uncharted territory perhaps but needs must!
|
| Any thoughts/experience greatly appreciated.
|
| Mark Andrews
|
|
|
|

 

[Mark Andrews]

Eric, thank you for your response. I was kind of expecting that to be the
case... but hoping it wasn't!

Do I therefore assume that changing the source of the data may get around
the "storage" problem (e.g. use ADO to get more data from a large SQL Server
Table) but Jet will still fail if data >2gb gets returned...?

Thanks
Mark