Architectural Issue

[NevilleT]

I have a split application (tables in one database and apps in another) that
is now up to 13Mg and contains some 150 reports and 50 screens. I am
considering adding a whole new set of functionality which will probably add
another 5Mg. It will have some new tables, but much of the data is in
existing tables in the back end database.

My question is how would be the best way to add this new functionality. One
option I was considering was to create a separate Access file for the new
functionality and open it from the main application. I am concerned with the
manageability, stability and performance of an Access application reaching
20Mg. Does anyone have any experience with such a large file, and how have
you approached it?

 

[Jeff Boyce]

I suspect you'll find that you end up with more overhead and more
maintenance and more coordination needed by using two front-end portions
than just putting it all in one.

Have you done any searching on line on these topics? You realize that you
posted your "front-end" size question in a "back-end"/tables newsgroup,
right?

If your tables are well-normalized, and indexed properly for the mix of
data-entry and data reporting that your application needs to support, I
don't believe you'll see performance issues.

If your new functionality is not needed by all users, consider encapsulating
the procedures in a module of their own. That way, that portion won't load
until the feature set is called for.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Seth Schwarm]

The size your db will grow to - does not sound out of line. This is what
happens when you build a more robust application.

I agree with Jeff Boyce, I certainly would not create another front-end.
Can you say nightmare?

Since you are asking the question you are, you may be aware of this - have
you Compacted your front and back end db files? I personally would set the
feature in Tools -> Options, then select the General tab and select 'Compact
on Close'. This will compact your front-end every time your user closes
their copy of the front-end. If you also use the feature in your back-end db
you may find it beneficial.

To see what type of impact the Compact would have - check your front-end
file size, then go to Tools -> Database Utilities and select to Compact the
Database. After you have compacted the db, check the size again and see what
your benefit is.

Something to ponder when you have nothing else going on - in Access 97 file
sizes were small. Then came Access 2000 and along with it, bloated file
sizes. Many of my applications became bloated hogs as I converted them to
2000. So unfortunately since 2000 MS has stck it to us, but what can you do?

Hope this helps you.

Seth Schwarm

 

[Jeff Boyce]

A note to add...

Since Compact & Repair is working with your (?production) database, the
chicken's way is to first make a backup copy (in case C&R suffers an
"inadvertancy"), then run C&R.

If you set this to run automatically in the Options, it might make doing the
backup copy more difficult.

Regards

Jeff Boyce
Microsoft Office/Access MVP