Jet database engine

I designed an access database using Access 2003. The limit of the capacity of
access database is 2 GB. But my database size is yet to be increased very
soon more than 2 GB. So I splitted the database into backend and frontend.
Backend is kept in sql server. For this sql server 2005 express edition is
used. Backend database contains only tables and the frontend database
contains rest of the objects like queries, forms, reports etc. I want to know
whether the jet database engine can functions properly if the size of
database crosses the limit of 2 GB. If it cant do so, what should I do. Early
response would be very much helpful.
With regards,
--
nandini

The front end only holds 2GB. If your forms, reports, vba code, and queries
take up so much space you're worried about exceeding the db file size limit,
you're in trouble. Redesign the front end.

Chris


Nandini wrote:
I designed an access database using Access 2003. The limit of the capacity of
access database is 2 GB. But my database size is yet to be increased very
soon more than 2 GB. So I splitted the database into backend and frontend.
Backend is kept in sql server. For this sql server 2005 express edition is
used. Backend database contains only tables and the frontend database
contains rest of the objects like queries, forms, reports etc. I want to know
whether the jet database engine can functions properly if the size of
database crosses the limit of 2 GB. If it cant do so, what should I do. Early
response would be very much helpful.

--
Message posted via http://www.accessmonster.com

If your back end data is on sql server, then the file size is not related to
JET in any way.....

So, no, the back end data size limit does not apply at all.....

What was the size of your back end access file after you compact it?

Unless you file size was in the millions of rows, 2 gig is a lot of
records....(5 million customers records will easily fit in that size).

Regarelss, there is no real lmits on the access side, all limits will be
that of the database server you are using.....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

On Tue, 9 Jun 2009 23:02:01 -0700, Nandini
wrote:

I designed an access database using Access 2003. The limit of the capacity of
access database is 2 GB. But my database size is yet to be increased very
soon more than 2 GB. So I splitted the database into backend and frontend.
Backend is kept in sql server. For this sql server 2005 express edition is
used. Backend database contains only tables and the frontend database
contains rest of the objects like queries, forms, reports etc. I want to know
whether the jet database engine can functions properly if the size of
database crosses the limit of 2 GB. If it cant do so, what should I do. Early
response would be very much helpful.
With regards,

Access will NOT be able to handle a JET database over 2Gbyte.

Since your data isn't *in* a JET database, that is irrelevant as far as the
data is concerned. The size of your data is constrained only by the SQL/Server
Express limit (4GByte as I recall). As noted elsethread, that is a LOT of
data; unless you're storing large binary objects, there will be space for tens
of millions of rows.

If your frontend (after Compacting) is approaching 2GByte, then you *are* in
trouble and either have an ENORMOUSLY complicated application (which should
probably be moved to a full client/server app), or you have a lot of graphical
images in the frontend. Access doesn't handle such very efficiently; you might
want to consider removing or at least minimizing the size of graphics on your
forms and reports.
--

John W. Vinson [MVP]

Thanks to all of you for your early and helpful responses.
The size of backend database is 1.5 GB after compacting. The size of the
front end database is 160 MB after compacting. The queries and the forms for
required user interface are present in the frontend access database and the
jet database engin perfoms the function of data retrieval process now
properly. Just I want to know when the backend database of sql server will
exceed 2 GB in size, then whether jet database engin can perform data
retrieval process properly in the same manner? If not what should I do.
With regards,
--
nandini


"John W. Vinson" wrote:

On Tue, 9 Jun 2009 23:02:01 -0700, Nandini
wrote:

I designed an access database using Access 2003. The limit of the capacity of
access database is 2 GB. But my database size is yet to be increased very
soon more than 2 GB. So I splitted the database into backend and frontend.
Backend is kept in sql server. For this sql server 2005 express edition is
used. Backend database contains only tables and the frontend database
contains rest of the objects like queries, forms, reports etc. I want to know
whether the jet database engine can functions properly if the size of
database crosses the limit of 2 GB. If it cant do so, what should I do. Early
response would be very much helpful.
With regards,

Access will NOT be able to handle a JET database over 2Gbyte.

Since your data isn't *in* a JET database, that is irrelevant as far as the
data is concerned. The size of your data is constrained only by the SQL/Server
Express limit (4GByte as I recall). As noted elsethread, that is a LOT of
data; unless you're storing large binary objects, there will be space for tens
of millions of rows.

If your frontend (after Compacting) is approaching 2GByte, then you *are* in
trouble and either have an ENORMOUSLY complicated application (which should
probably be moved to a full client/server app), or you have a lot of graphical
images in the frontend. Access doesn't handle such very efficiently; you might
want to consider removing or at least minimizing the size of graphics on your
forms and reports.
--

John W. Vinson [MVP]

On Wed, 10 Jun 2009 12:04:08 -0700, Nandini
wrote:

Thanks to all of you for your early and helpful responses.
The size of backend database is 1.5 GB after compacting. The size of the
front end database is 160 MB after compacting. The queries and the forms for
required user interface are present in the frontend access database and the
jet database engin perfoms the function of data retrieval process now
properly. Just I want to know when the backend database of sql server will
exceed 2 GB in size, then whether jet database engin can perform data
retrieval process properly in the same manner? If not what should I do.
With regards,

You'll be fine. The 2GByte limit does NOT apply to a SQL/Server backend;
Access neither knows nor cares how big that backend might be.

--

John W. Vinson [MVP]

Thank you so much.
--
nandini


"John W. Vinson" wrote:

On Wed, 10 Jun 2009 12:04:08 -0700, Nandini
wrote:

Thanks to all of you for your early and helpful responses.
The size of backend database is 1.5 GB after compacting. The size of the
front end database is 160 MB after compacting. The queries and the forms for
required user interface are present in the frontend access database and the
jet database engin perfoms the function of data retrieval process now
properly. Just I want to know when the backend database of sql server will
exceed 2 GB in size, then whether jet database engin can perform data
retrieval process properly in the same manner? If not what should I do.
With regards,

You'll be fine. The 2GByte limit does NOT apply to a SQL/Server backend;
Access neither knows nor cares how big that backend might be.

--

John W. Vinson [MVP]

=?Utf-8?B?TmFuZGluaQ==?= wrote
in :

I designed an access database using Access 2003. The limit of the
capacity of access database is 2 GB. But my database size is yet
to be increased very soon more than 2 GB. So I splitted the
database into backend and frontend. Backend is kept in sql server.
For this sql server 2005 express edition is used. Backend database
contains only tables and the frontend database contains rest of
the objects like queries, forms, reports etc. I want to know
whether the jet database engine can functions properly if the size
of database crosses the limit of 2 GB. If it cant do so, what
should I do. Early response would be very much helpful.

Did you delete the old tables from the front end after you moved the
tables to SQL Server? If so, did you then compact the front end? If
you haven't done either of those steps, you need to do so, and
you'll likely find that your front end is now orders of magnitude
smaller.

The 2GB limit applies only to Jet, i.e., the database engine, and
Access doesn't care how much data is in a server back end. SQL
Server Express has a 4GB limit, but that's enforced by SQL Server,
not by Access or Jet.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/