Access Size Problem

  • Thread starter Thread starter Roy Goldhammer
  • Start date Start date
R

Roy Goldhammer

Hello there

I have huge accsss 2003 database: approx 180Mm of code only. and i'm using
Source safe

The database is sql server and it is 800Mb

The import and export of objects are failed in some times. If i work on the
database one day full i've reach to size of 1.6Gb

What i need to do save my database?
 
Obviously, as you work during the day, you will frequently compact your
database.

What is the size of your database after you do compact?

(further, you don't have a bunch of screen background graphics etc thrown in
there..do you?).

To give you an idea, here is a small application of mine, the numbers are:

lines of code in Modules = 8654
Lines of code in forms = 16654
Lines of code in reports = 2385
------------------
total lines of code = 27693

number of Forms = 163
number of Reports = 77
number of tables (total) = 63
number of tables (user) = 54
number of modules = 23
number of Queries = 187

The whole above application is LESS then 10 megs in size. And, for use by
users, after converting to mde, the size is only 6 megs.

So, when you talk about 180 megs in size, either somting is wrong!!..or it
is big! So, do you numbers work abou to be about 20 times more then what I
got above (ie: in place of 162 forms, you would have 1000+ forms to get
somting in the 180 meg size).
 
Thankes Albert

The size of my mdb is even double 15 more then your database. And i do have
almost 1000 forms and reports.

the 180Mb is after compact.

I've asked many times here before about using refarances in access and
nobody answer about it

Do you know about it something?
 
Well, I do not work on anything this size.
May be I shouldn't say anything.
I hate the "red face" scenario :-)

1) I am not sure what you mean re:
The import and export of objects are failed in some times.

2) I would probably try to "split" the frontend (even so I don't know
what effect this may have on SourceSafe). Surely there are several
distinct aspects to the database. Set them up as seperate mdb's (mde's)
and then link them into the master (in VBA, Tools.References). Possibly
a slight performance hit but much easier to manage.

Regards,
Andreas
 
Thankes Andreas

So the split is the referance i'm talking about.

I don't know what are the aspects of the spliting.

From the master MDB i can see the other moduls of the child mdb's. But i
can't reach them by opening them

The only reach to the child MDB's by building functions on the child MDB and
run them on the master by using Call

After i'm on the child i can't use any funcion in the modules of the Master
mdb. But i can use its forms. and that was the only way i could go back to
the master for opening other child

It looks very complecated and i only asking one question: is there a simple
way to do this?
 
Hi,

I never said it would be easy :-)
I have only done this once, a long time ago.
It took me a while to get it working just right.

The idea is, that you put everything that belongs to a particular group
of functionality into a seperate database. To "design" any of the
objects in the "child", you have to open that database.

Then you use the master to "execute" the functionality provided by the
child (Public Subs, Public Functions, Forms). This should be designed in
such a manner, that a child does not need to talk to another child. When
the "child" finishes, it returns to the master.

Without setting up a sample, I can't give any more advice.
But it all comes down to careful planning and lots of testing "stuff".
Considering the size of your application, and the problems you are
getting, may be the extra effort is worth it just for the sake of a good
organisational structure (a bit like proper normalization of tables).
I am sure there would also be plenty of proponents re moving to .Net.
Now there is a thought: Redo the entire application :-) Oh joy :-)

Regards,
Andreas
 
Roy,

"almost 1000 forms and reports" sounds like you are duplicating forms
and reports instead of parametrizing? If that's the case, this is where
I would start!

Nikos
 
Update:

1)
You could create the following structure:
Master - linking to Child1, Child2, Child3, Common
Child1 - linking to Common
Child2 - linking to Common
Child3 - linking to Common
Common
This way, all your common "stuff" is available to the Master and all
children. Master becomes an interface to your different groups of
functionality (essentially a menu). You could even move groups of
non-interlinked tables into the children (if you are linking).

2)
It appears (in XP) you can even edit the code of all the databases from
the Master - cool, even if a little confusing at first. Have not tried
the forms/reports.

Regards,
Andreas
 
Thankes Albert

The size of my mdb is even double 15 more then your database. And i do
have
almost 1000 forms and reports.

the 180Mb is after compact.
Click to expand...

Good stuff!. I always check the above, as often we see a application with
only 50-75 forms, and it is 300 megs in size. This is result of the
application not being compacted, or graphics being used inside...
I've asked many times here before about using references in access and
nobody answer about it
Click to expand...

Well, I not sure what you have asked for in the past.

You have to use mde's to share the code. If you do break this application up
into separate mdb code libraries, and then compile those code libraries into
mde's. You then can simply can set a reference to those code libraries using
the standard tools->references. Select "browse", and then change the file
type to "mde". Now, just browse to that mde you made..and add it to the
references. At that point, you can use all of the subs/functions in the mde
file. (that is all there is to using references).

I am not sure that simply moving out the code from your standard modules you
have now into a bunch of separate mde's is the best solution here. (it will
crate extra work to set this up, increase the chances of a references
problem when deploying the application. And, I am not sure it would reduce
the file size that much (I suppose this would depend on how much code you
have, and if that code is in standard modules).

So, using references and building up a "code library" of routines would be
helpful...but I not sure if that would be a "end all" solution for you. It
certainly might enable to your more easily break this system up into
separate pieces. And, also, you do seem to have a large number of forms
here. (I am not sure if the large number of forms is due to requirements,
lack of normalizing...or in fact it is just large and complex!!).
 
Roy Goldhammer said:
Thankes Andreas

So the split is the reference i'm talking about.
Click to expand...


As I mentioned in the other post, using references to "mde" code libraries
ONLY works well for code that you have in standard modules. The code in a
form cannot be shared this way. If you could/can move code out of a form
into a standard module, then of course, you could share the code more
effectively. On the other hand, likely if the code could have been put in a
standard module..it was...

I would actually NOT go to any trouble to pull code out of forms. (better to
keep code that belongs to a form in the form). This way, you can more easy
pull out, and put similar parts of the application (forms + or reports) into
a separate pieces of the application. So, for example, the code to prompt
users, and do most of the or could be made separate from the data entry
screens Further, as your application designs improve, then to add new
reports, and even add more parameter prompts, you will find that NO new
forms need to be built in theses cases (your report prompt screens will use
tables to "list" what report,a nd what options..and thus over time you don't
have to modify your menus systems every time a new report is added). You can
see some great examples of this concept here:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html
 
Back
Top