What constitutes a "large" front end?

[Kevin Witty]

I have an application front end with 129 tables (mostly Jet linked, some
local, soon to be SQL rather than Jet), 429 queries, 235 forms, 81 reports,
and 51 modules (mostly class modules). The mdb file is around 25mb after a
complete rebuild (start a new database, import everything from the old one
in, reset references, compact and repair). I periodically have to do this as
I continue development when I get to a point where I can no longer create an
mde file from it. Am I likely to get to a point sometime soon when the
rebuild no longer works, and I can no longer create an mde at all? Is this
getting to be a "too large" front end?

Thanks,

Kevin

 

[Guest]

based on what you just said, i don't think you are close
to large yet. in access help type specifications.
max db size in 1 gig(for 97). you are still at a dinky
25mb which is what....2.5%?

-----Original Message-----
I have an application front end with 129 tables (mostly Jet linked, some
local, soon to be SQL rather than Jet), 429 queries, 235 forms, 81 reports,
and 51 modules (mostly class modules). The mdb file is around 25mb after a
complete rebuild (start a new database, import everything from the old one
in, reset references, compact and repair). I

periodically have to do this as

 

[Kevin Witty]

The front end has handled 300+mb back ends... that's not the part I'm worried
about. It's the front end size that concerns me.

 

[Guest]

hi again,
I still see no problem unless you are taxing the locals
resources. so long as the local will handle it i see no
size problem. Remember the front ends are technically
independent and still have the 1 gig constraint. the
relationship to the back end are linked tables.

 

[Brendan Reynolds]

I used to have lots of problems with large application MDBs in Access 2000.
When using Access 2000, I would have said that anything over 8MB was large.
But Access 2002 and 2003 seem to be significantly improved in that regard -
I'm having no particular problems with 20MB+ application MDBs in Access
2003.

You mentioned that there are some local tables in the MDB. Depending on how
much data is in those tables, you might be able to reduce the size
significantly by using linked tables. If you're using local tables for
(semi) static data to reduce network traffic, you can still do that while
using linked tables, just use a separate MDB on the same PC as the
application MDB.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

['69 Camaro]

Hi, Kevin.

Pardon me, but you're focusing on the wrong problem. You're focusing on the
database size, instead of the brick wall that's right in front of you. You
are getting excellent answers to the question stated in your subject line,
but the advice you get won't keep you from slamming into the brick wall.
The advice you receive will help you slam into it even faster.

The question you should be asking is, "At what point will my database hit
the brick wall because Access cannot create an MDE file from it?"

The answer is, "When Access uses all 2048 TableID's for the database objects
while creating the new MDE file, but still needs more TableID's to finish
the job." (The limit is 1024 TableID's for Access 95 and Access 97
pre-SR1.)

There is no way to calculate the precise number of TableID's that Access is
using, but one can come up with a ballpark figure. One TableID is used for
each table, each form, each form's module, and each table listed in a query.
At least one TableID is used for each DAO Recordset, domain function, and
combo box and list box with a RowSource set to a table or query. Two
TableID's are used for each linked table. Multiple TableID's are used for
each bound subform with master and child links (depending upon the
complexity of the query used), and so on. Access uses some TableID's as
overhead for the current database file, the new database file, and the MDE
utility. Probably a few hundred TableID's would be my guess.

So, reducing the size of your front end by placing the local tables in
another database and linking to them will increase the number of TableID's
used the next time Access is attempting to create the MDE database file.
The fact that you've already hit the brick wall several times -- but you
were able to back away far enough to start over -- tells you that you are
very, very close to the point where you won't be able to back away from the
brick wall ever again.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Greg Allen via AccessMonster.com]

Hi Kevin,

Quite agree with Gunny, I suggest you split your front end into section ie Customer, Reports, Accounts etc and save these as seperate databases linking into your original data.

You could then create a simple front end which willl open at the click of which ever area you wont in the click of a button.

Gunny, would this work as far as your concerned ?

Regards

Greg