DB size?

[Franknstuff]

I'm new at this and I was wondering what is considered a huge DB. Mine is only 7.8M and I just started?

21 forms
2 reports
4 querys
10 tables

 

[Cosmos75]

Try compacting your database.
Tools -> Database Utilities -> Compact and Repair Database.

See here...
http://www.access-programmers.co.uk/forums/showthread.php?t=59436&highlight=compact+delete

 

[Franknstuff]

Confused

It wasn't a DB question. I just wanted to know what is the average? I was hoping to get a baseline of where my DB was compared to everyone elses.
Qty: Forms
Query's
Reports
Tables

I didn't know that this was a general question. Sorry.

 

[namliam]

For any average app the numbers are not excessive, the size of the app is or may be. Everytime you create something and delete it access keeps the space in the db.

eg. you import a text file, size 10 megs, into a table. Then delete the table and reimport it. Your db is now 20! megs.
You need to do a compact and repair on a regular basis while developing.

Regards

 

[Mike375]

I'm new at this and I was wondering what is considered a huge DB. Mine is only 7.8M and I just started?

21 forms
2 reports
4 querys
10 tables

Mine has well over 2000 macros, about 700 forms, 1200 queries and 300 tables.

Compacted and with records it is around 90 mb. Probably a 1/3rd of the data base is no longer used. This in Access 95.

So you have a way to go

Mike

 

[Kodo]

2000 macros? ouch.. no wonder your db is so big.. how much of that is actual data

 

[Mike375]

2000 macros? ouch.. no wonder your db is so big.. how much of that is actual data

I think it was about 60 mb with all records deleted and then compacted.

I still have some stuff in it from when I first started with Access in 1996 and sometimes come across it when I might alter a macro. I always find it interesting to see how I made some of the stuff back then.

It only has 2 Reports and no modules. The only code that runs opens Word and sticks a lot of data into Word bookmarks and then copies the letters and pastes it back to an Access memo field. There is probably about 40 labels that run OnClick for that code.

Mike

 

[Franknstuff]

Wow

Thanks, well it seems like my database is nothing. Is this a typical database?

 

[Pat Hartman]

Mike375's database is not typical. It has huge numbers of objects because it is not normalized. There is a great deal of duplication and as he said about a third of the objects are no longer used and so should have been deleted.

You don't have enough querydefs. Every form and report should be based on a querydef so I would expect a minimum of 23 for your application. Forms and reports should not be based directly on tables and SQL strings as RecordSources contribute to database bloat so they should be avoided.

 

[Mike375]

Mike375's database is not typical. It has huge numbers of objects because it is not normalized. There is a great deal of duplication and as he said about a third of the objects are no longer used and so should have been deleted.

You don't have enough querydefs. Every form and report should be based on a querydef so I would expect a minimum of 23 for your application. Forms and reports should not be based directly on tables and SQL strings as RecordSources contribute to database bloat so they should be avoided.

Pat,

There is another reason why it is so large. Part of the data base is used for telemarketing and it caters for 20 category of prospects and does the statistics for each category and measures that against calling goals that were set. There are several tables, queries, forms and macros for each category and so that is all multiplied by 20.

The data base also does rate calulations for different insurance companies. It also runs a lot of comparisons between different insurance company's Income Replacement policies. So in many ways it is several data bases in one.

I keep the other third because (at least as far as I can tell) it does no harm and sometimes I have reason to resurrect some of the things. Sometimes it also handy for my instruction manual.

Mike

 

[Kodo]

and on a good day, it cleans the dishes too

 

[Mike375]

and on a good day, it cleans the dishes too

And on a real good day it even earns me some pocket money being hired out.

 

[Pat Hartman]

and so that is all multiplied by 20.

- That's my point! It doesn't need to be multiplied by 20 if you normalize it.

 

[Mike375]

- That's my point! It doesn't need to be multiplied by 20 if you normalize it.

But you have to handle the 20 categories.

There are basically three sections.

In one table all the prospects are strored and one field has a number from 1 to 20 for the category and another field has a number from 1 to 15 which identifies the salesman with the prospect. Thuis salesman number 4 might have prospect categories 4, 11 and 16.

There is another table which has the various calling and selling goals for each day of the week. There is another table that takes this data for each day so that a running total is obtained. The data is physically appended since that allows calling goals to be changed between a starting date and finishing date. Thus allowance can be made for holidays etc.

There is another table that a new record is created when each call is made.

There is another table that holds summary data of the above table because the above table gets too big. The summary data makes one record for all the calls on a given day for each category.

Then there are 15 tables for a diary for each salesman. Appointments are stored in one table but the 15 tables display appointmenst and allow appointments to be made for the appropriate salesman.

There are 22 queries, 26 forms, 24 macros and 2 tables for each category. All of these replicated 20 times and of course with different names.

The data in each table is all different because the various calling goals are different for different categories of prospect as are the starting and finishing dates.

In addition there are another bunch of queries, tables etc that keep track of prospects being consumed and also replacing the names from a large holding table.

The actuall calling is done from one screen and when a category of prospect is selected then the record source is changed.

Even if you could integrate it into one that would not be successful because quite often a single category is hired out. I make a blank data base and just import the queries, macros etc for one of the categories plus the other objects that do the statistical results.

Mike

 

[namliam]

Pat is saying if you use code (VBA not macro) you could send the categorie to the code, handling it that way. So you would only need 1 peace of coding instead of multiplying by 20! Hence the words to the wize, "Use VBA if and where you can, leave Macro's alone allready...."

Regards

 

[Mike375]

Pat is saying if you use code (VBA not macro) you could send the categorie to the code, handling it that way. So you would only need 1 peace of coding instead of multiplying by 20! Hence the words to the wize, "Use VBA if and where you can, leave Macro's alone allready...."

Regards

But how would you make one piece of coding to open the forms associated with each category?

If I wanted to make an additional category it does not take long to do another set of macros, in fact that is the easiest part. The largest macros only need a name change. They have perhaps 60 SetValue actions but they run when the form is open so no form reference is required. Only the macro name changes because they are a RunMacro action.

Even the macros that are referencing forms and opening queries are quick to make because of the naming all being the same except for the number in front of the form or query name. For example, if I wanted a 21st category I would just make copies of the Category 2 and then run through and type a 1 between the 2 and name.

Mike

 

[namliam]

What can i say, if your stubburn your stubburn ...

In vba you should only need to call the function. Lets say i create the function DoIt(CatNR as integer)
Then i can do all categories by calling from the on click
DoIt(1)
or
DoIt(963496) (if there were up to a MILLION categories)
No muss no fuss as easy as that. The harder part is coding the function, but i get the idea you dont want to go there ....

Regards

 

[Mike375]

It is not a case ofd bing stubborn but a simple case of effort in and results out.

If this whole data base was converted to code the results out would be the same.

The other issue for me is that couple of people have had a go at doing code in this data base and there have been problems. Of course I am in no position to judge their knowledge on code except I do know their knowledge is much greater than mine.

I don't know if you saw a posting sometime ago on dBForums about changing the caption value of a label on a subform and the action being done from a another subform. I posted up a macro action which did the trick but the code would not work. I have tied a few times converting some huge macros to code but it has not worked. But I have no doubts at all that someone who really knows their stuff can do better with code.

One area where I really like macros is when they are placed on OnClick on labels or text boxes or OnCurrent, you can easily change them by just bringing the data base window fordward. As far as I am aware you could only do that in code if it was in module.

A conclusion I came to a few years ago is that making a data base and especially one that performs a very wide range of activities is similar to writing a sales letter or presentation. And English professor might have a vocabulary of 10,000 words but he might not be able to produce a good sales letter or presentation.

I think the average person posting on these forums would get much further and much quicker with macros and queries than with code. If you are in the computer business it will be quite different because you don't really control the environment of a client.

Mike

 

[Franknstuff]

oops

Well I didn't really want this to get out of hand. In Mikes defence he was the only one that really answered my original question. Thanks Mike.

 

[namliam]

Reread your thread... Mikes db is an out of control (sorry mike) database with an exessive amount of macro's, and to low a number of query's. It in no way resembles an average db.

In a sence there is no average db. Have DBs with 2 tables, 30 querys, 1 form (with 1 button) and some modules, that are up to 200 megs in size (data!). Each database is unique.... in every aspect thinkable.

Think of it, suppose mike would want to change 1 small thing in his macro. He would have to change it in 20 places, that might not sound so bad (yet). But what would happen if you go up, 30, 40, 50, 100 categories.... and then you need to change something.

Anything worth doing is worth doing it right (no offense)

Regards