DB size?

[Cosmos75]

From Pats Access Adviser Article

As in Pat Hartman?

What article is that? Got a link?

EDIT: Nevermind, I found it here.

 

[Mike375]

As they say, if a job's worth doing it's worth doing right

And that is basically what I have been saying throughout this thread. My business is the business of selling insurance, not making data bases.

My data bases does not need to "look right" behind the scenes. It simply needs to work and work to the extent that it does not interfere with my insurance business and that is something it achieves.

Just an hour ago I changed a couple of labels to onClick for Mile O Phile's code for changing record source. However, there has been no change. A click in either case achieves a record source change. But it took time to make the change, only minutes to be sure, but you know what I mean. However, the code is not really as good because with the macro I can change the query in the macro for record source change without closing down the data base. I asssume if the code was in a module then I could do the same thing, but then I would need to learn how to do that and at the end a "click" produces the same result, a change in the record source.

Mike

 

[MadMaxx]

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

Wouldn't you think it migh be better to do the program over in C++ ov VB. Just my opinion but that seems to be an awful lot of forms and such to be trusting to one Access Database.

 

[Pauldohert]

As they say, if a job's worth doing it's worth doing right

I would say its worth doing well - but still - kind of proves the point - there are many ways of doing things - not one right way!

Which do you disagree with either "if it works it works" - I suppose the argument is - If if works it doesn't work??
You may be able to tag a "but" on the end but thats it.

Or everyone can eventually improve themselves? I would find it extremely arrogant for someone to imply their own work couldn't be eventually be improved upon!

Its called progress and learning -- if thats not for you Rich you must be at your limitations already.

I will support Mike here - he has a life to lead, a business to run , friends and family to see - and presumably some sport to be watching - he hasn't the time or resources to improve everything he could undoubtably improve, - if it works - it works!

I do get the feeling if someone asked the question "how do I fix so and so problem and my Ford Fiesta" The answer would come back why did you have a ford? You should have a Mercedes! Not very helpful!

The problems called economics and it applies to everyone!

 

[Pauldohert]

Wouldn't you think it migh be better to do the program over in C++ ov VB. Just my opinion but that seems to be an awful lot of forms and such to be trusting to one Access Database.

We have already established the fact that 1/3 of it isn't used and has 20 forms etc where there is the need for one. So times everything by 1/30 and its size looks fine for access.

 

[Rich]

Its called progress and learning -- if thats not for you Rich you must be at your limitations already.

I will support Mike here - he has a life to lead, a business to run , friends and family to see - and presumably some sport to be watching - he hasn't the time or resources to improve everything he could undoubtably improve, - if it works - it works!

I'm both astonished and quite frankly insulted by the remark.
I run my own business too which has nothing to do with databases or even computing. I still come here because I still want to learn even though my own db does what I want. Those with the right attitude are willing to learn until the day they die, that's called progress! I started at roughly the same time as Mike with the same limitations you attribute to just Mike, oddly.

 

[KenHigg]

Should that be: Multiply by 2/3 ?

4200 * (2/3) = 2800 = Still too big.

 

[Pauldohert]

Should that be: Multiply by 2/3 ?

4200 * (2/3) = 2800 = Still too big.

No 2/3 is the size with all the unused stuff taken out, and 1/20 whats left is actually needed as the rest of this thread deals with.

Thats 1/30 - ( i hope)

 

[KenHigg]

I know I will regret chasing this rabbit, but...

4200 objects minus one third is 2800

Where did 1/20 come from and what does it refer to?

Where did 1/30 come from and what does it refer to?

 

[Mike375]

I know I will regret chasing this rabbit, but...

4200 objects minus one third is 2800

Where did 1/20 come from and what does it refer to?

Where did 1/30 come from and what does it refer to?

It has over 2000 macros, about 700 forms, 1200 queries and 300 tables. About 1/3rd is no longer used.

A prospect category has 22 queries, 12 macros, 2 tables (plus some common to all categories of prospects) and 13 forms. So there are 980 objects there for the 20 categories plus quite a lot more that is a common to each category.

It also has heaps of objects for product comparisons plus heaps more for various gov't compliance requirments. Then there are all the objects for premiun rate/quote system for different companies. The fact that we make ourt rate/quote systems rather than the ones the insurance company will supply might tell you something.

However there is more than one data base

One data base is used to reformat phone numbers on prospect lists we buy. It will remove any number spaces and any keyboard character. I have it set for the common ones that go with phone numbers such as ( ) - _ etc and I can enter any character in another field and will remove that character from the phone numbers.

Then there are other data bases that bring in client policy details each month from insurance companies. The reason these are all in differnt data bases is the process to bring each company is very similar but by adding another data base as a copy it only takes me a couple of minutes to make the DB for another insurance company. to it one data base I would of course need different form names etc. When the data base runs through the results are then put in the main data based.

Then there are other data bases which are simply a couple of tables and these hold bulk listings of prospects.

Then there is another set of data bases that brings in the commission statements from the insurance companies on Excel. After the data base converts it all over to the form we need then the data goes to the main data base.

Then there is another set of data bases that have propsects names which we have taken through the telemarketing/sales process but where we have not been successful. Many of these names are sold to people outside the life insurance business\nad the main data base put these names in various tables depending on the results of the phone call.

We also sell names back to the name list suppliers since our calling process has "cleaned up" the names.

And there is one other set of DBs. These are used for entering prospect names such as medical specialists, lawyers and dentists. These data bases allow for ease of entry with such things as the specialists qualifications. They also have quite a bit of text manipulation fucntions so that the person entering can type in

William J mcdonald as opposed to to entering in different fields. Same deal for Suburb, State and postcode. These data bases also fix things like mc into Mc and so on.

So in the main data base is really a processing unti and 99% of the records are either heading into the data base and then in most cases being tossed out the other end.

Mike

 

[Pauldohert]

Rich I apologise! I didn't say anything against you, just questioned your previous post, which I think you have succesfully answered - the way I would have done , and Mike would have done and everyone else. You just seemed to imply you did things right first time and that was that.

As we now agree the worlds unfortunatley not that simple.

Are you a Insurance Salesmen?

- or is your only reply (to date) to Mikes Oracle question as uphelpful and insulting as it seems.

If I am wrong - I apologise again.

Mike is here to learn too - like you and me and everyone else.

http://www.access-programmers.co.uk/forums/showthread.php?t=68229

Ken - I hope my calculation is right.

Paul

 

[Rich]

Mike is here to learn too - like you and me and everyone else.


Paul

now I think that's where we differ if Mike's replies to those who tried to offer help are taken into account

 

[Mike375]

now I think that's where we differ if Mike's replies to those who tried to offer help are taken into account

Rich,

You are quite wrong.

Firstly, I did not post on this thread with "my data base has lots of macros etc, how do I fix it"

Amost everyone is saying "fix it" and then Pat Hartman produces an article where the chosen author says it is better for the developer to start from scratch if encountering a data base of this type.

It has been said a few times it will "crash" but when I ask for elaboration none is posted.

If I become an overnight coding sensation then what do I do.....take a lot a of time out of my business and at the end of the process when I click on something the same data displays.

Yesterday I added another insurance company for the product comparison part of the data base and that took me about 10 minutes. Are you suggesting that I should have set aside a few days to learn the coding etc. and etc Or do you think I was better off doing it in 10 minutes and then spending the day entering the policy wording information so it is functional and can be used. But it would be much worse than that because I would need to change the product comparison part of the data base. After I had learnt all of that and then made it all I would still have the same output.

I do learn by looking at the site and have copied and kept some of the coding in a separate data base. A couple of days ago Mile O Phile gave me the code to duplicate a Setvalue macro for change of recordsource. I tried it and it worked. Now do you think I should take the time to change all the macros that run a recordsource SetValue. Well, to do that I would have to learn some more because several SetValue recordsource macros are run by other macros or the Setvalue actions are part of other large macros.

Mike

 

[Rakier]

It's been said before and I'll say it again.

If it works...it works. Doesn't matter how it get's done, as long as Mike is happy with it and it works for him then that is all that matters.

Mike, I think the article that Pat posted the link to is pretty much what I was trying to say. I redesigned my old dB because it was basically impossible to figure out/remember what I had done when I first designed it. I started from the ground up and rebuilt it over a period of a year. Like you, my job is not computer related and I built my dB to use to help me in my job. It has ended up being used by 12 different supervisory people too. It took me a long time to build because of my workload at work and the fact that I have 5 small kids at home didn't leave me much spare time. But I did rebuild it. It has been used for 3 years now with no problems. Besides that fact, I am currently building it again because I have learned much in those three years and expect the dB to be bigger, badder and faster than it is now when I am done.

I'm not doing this because it doesn't work...or because it's required, but because I want to improve upon my old design and make it better. In one of your earlier replies to my reply, you said "My database is not a piece of shit." I never said it was. It is your way of accomplishing a task that you needed to accomplish. It works...so that's great. You have probably done more with the program than 80% of the people out there. It's true that you did not ask anyone to "fix" it or "how can I fix it", but I've found that the people that populate this forum are usually quick to comment on how to do something different (better in their opinions ) and rarely mean it in a condescending or negative way.

That said, again, I do redesigns not because the dB doesn't work (or won't work in the future), but because I learn and like to learn new things about Access, VBA, etc... all the time. It is that hunger to learn and improve my skills that drives me. My dB may still be crap, but it keeps getting better all the time, mainly because of the information that I gain from this and other sites.

I think that most of my concerns with your dB lie in what Miles said regarding the upgrading from Access95. I know that I had some issues from 97 to 2000 because of macros (there are none in my current dB) so I'm sure that you may encounter some of those conversion errors.

In conclusion...keep on keepin' on.

 

[Rich]

but you're missing the point Mike, your db as is, is alread taking a lot of time out of your business, how mayn 10minutes a week do you spend on maintaining it? It takes me 13.2 seconds to add another customer to my db

 

[Rakier]

That is true Rich. Never thought of it that way. Takes me about that long to add items, vendor or customer information to my dB also. I agree that the time savings are huge.

Also Mike, another point about this that I think someone else brought up a while back. It's the issue of survivability. If something happened to you and you were unable to work for awhile, could someone else step in and keep the dB running. With mine, they could and do (when I'm on vacation, etc...) Just a thought.

 

[Mike375]

but you're missing the point Mike, your db as is, is alread taking a lot of time out of your business, how mayn 10minutes a week do you spend on maintaining it? It takes me 13.2 seconds to add another customer to my db

Rich,

As I said on another posting 99% of the time I spend doing something to this data base is entering policy wording.

But I don't think you have the correct picture on the 10 minutes for the other insurance company to be added. That is table with about 30 records. Basically each record has a field for Policy Feature, then a memo field for the wording on that feature for Company A and another memo field for Company B and then a memo field for Key Differences. There are some other fileds for times, dates ID numbers etc.

Now if I had one table with 20 companies and the 600 records then I will need to select with a query when a particular comparison is done and so I eliminate that query. In reality the product compartitor uses one table but deletes and appends to that table from one of the insurance company tables. I select the company to be used for the comparison from a list and then runsa macro that runs the correct append query. It does that way because sometimes we a change in the Key Difference for different one off circumstances...but that does not change the original.

For me, it would also be messier adding the records to one table rather than just copying one of the other tables and giving it the appropriate name and then hooking it up to the system and then changing the policy wording for the other insurance company.

But the bottom line is that to add that the other company is about two days to get the final result and 47 hours and 50 minutes is the data entry and doing the key differences on policy wording and checking different things with some legal people.

Mike

 

[Mike375]

That is true Rich. Never thought of it that way. Takes me about that long to add items, vendor or customer information to my dB also. I agree that the time savings are huge.

Also Mike, another point about this that I think someone else brought up a while back. It's the issue of survivability. If something happened to you and you were unable to work for awhile, could someone else step in and keep the dB running. With mine, they could and do (when I'm on vacation, etc...) Just a thought.

The one part that needs me is the entering of policy wording but that is not a computer issue.

Adding another insurance company as I mentioned in my previous posting is a once in a blue moon deal.

Adding a new record is also 10 seconds like yours. You click a label New Record and then you are given the screen and the usual stuff with required fields etc. But that is only a very odd time. Most records in this data base are placed in a holding table in bulk and taken from other .mdb files. As the calls are make then mames are consumed. A macro runs and knows which category of prospect and how many of those names need to be placed in the MainTable.

Just for fun, i converted that macro to code and here it is. This runs 32 categories of prospects. 12 of these categories are treated differently the group of 20 I have mentioned earlier.....Mike

PS. I cut the middle out of it as it is long for the system to accept. It is 26682 characters

'------------------------------------------------------------
' GetNewNamesQuery
'
'------------------------------------------------------------
Function GetNewNamesQuery()
On Error GoTo GetNewNamesQuery_Err

DoCmd.Echo True, ""
DoCmd.SetWarnings False
DoCmd.OpenForm "CatSelector", acNormal, "", "", acEdit, acNormal
Forms!CatSelector!CatSelect = 1
DoCmd.DoMenuItem 0, 5, 4, 0, acMenuVer70 ' Form, Records, Save Record
Forms!CatSelector.RecordSource = "Query209"
DoCmd.OpenForm "NamesUsed", acNormal, "", "", acEdit, acNormal
DoCmd.SelectObject acForm, "CatSelector", False
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.RunMacro "ProspectCounter", Forms!NamesUsed!CountOfParents, ""
End If
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.OpenQuery "Query206", acNormal, acEdit
End If
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.OpenQuery "Query207", acNormal, acEdit
End If
DoCmd.Close acForm, "NamesUsed"
DoCmd.Echo True, ""
DoCmd.OpenForm "CatSelector", acNormal, "", "", acEdit, acNormal
Forms!CatSelector!CatSelect = 2
DoCmd.DoMenuItem 0, 5, 4, 0, acMenuVer70 ' Form, Records, Save Record
Forms!CatSelector.RecordSource = "Query209"
DoCmd.OpenForm "NamesUsed", acNormal, "", "", acEdit, acNormal
DoCmd.SelectObject acForm, "CatSelector", False
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.RunMacro "ProspectCounter", Forms!NamesUsed!CountOfParents, ""
End If
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.OpenQuery "Query206", acNormal, acEdit
End If
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.OpenQuery "Query207", acNormal, acEdit
End If
DoCmd.Close acForm, "NamesUsed"
DoCmd.Echo True, ""
DoCmd.OpenForm "CatSelector", acNormal, "", "", acEdit, acNormal
Forms!CatSelector!CatSelect = 3
DoCmd.DoMenuItem 0, 5, 4, 0, acMenuVer70 ' Form, Records, Save Record
Forms!CatSelector.RecordSource = "Query209"
DoCmd.OpenForm "NamesUsed", acNormal, "", "", acEdit, acNormal
DoCmd.SelectObject acForm, "CatSelector", False
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.RunMacro "ProspectCounter", Forms!NamesUsed!CountOfParents, ""
End If
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.OpenQuery "Query206", acNormal, acEdit
End If
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.OpenQuery "Query207", acNormal, acEdit
End If
DoCmd.Close acForm, "NamesUsed"
DoCmd.OpenForm "CatSelector", acNormal, "", "", acEdit, acNormal
Forms!CatSelector!CatSelect = 29
DoCmd.DoMenuItem 0, 5, 4, 0, acMenuVer70 ' Form, Records, Save Record
Forms!CatSelector.RecordSource = "Query209"
DoCmd.OpenForm "NamesUsed", acNormal, "", "", acEdit, acNormal
DoCmd.SelectObject acForm, "CatSelector", False
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.RunMacro "ProspectCounter", Forms!NamesUsed!CountOfParents, ""
End If
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.OpenQuery "Query206", acNormal, acEdit
End If
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.OpenQuery "Query207", acNormal, acEdit
End If
DoCmd.Close acForm, "NamesUsed"
DoCmd.OpenForm "CatSelector", acNormal, "", "", acEdit, acNormal
Forms!CatSelector!CatSelect = 30
DoCmd.DoMenuItem 0, 5, 4, 0, acMenuVer70 ' Form, Records, Save Record
Forms!CatSelector.RecordSource = "Query209"
DoCmd.OpenForm "NamesUsed", acNormal, "", "", acEdit, acNormal
DoCmd.SelectObject acForm, "CatSelector", False
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.RunMacro "ProspectCounter", Forms!NamesUsed!CountOfParents, ""
End If
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.OpenQuery "Query206", acNormal, acEdit
End If
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.OpenQuery "Query207", acNormal, acEdit
End If
DoCmd.Close acForm, "NamesUsed"
DoCmd.OpenForm "CatSelector", acNormal, "", "", acEdit, acNormal
Forms!CatSelector!CatSelect = 31
DoCmd.DoMenuItem 0, 5, 4, 0, acMenuVer70 ' Form, Records, Save Record
Forms!CatSelector.RecordSource = "Query209"
DoCmd.OpenForm "NamesUsed", acNormal, "", "", acEdit, acNormal
DoCmd.SelectObject acForm, "CatSelector", False
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.RunMacro "ProspectCounter", Forms!NamesUsed!CountOfParents, ""
End If
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.OpenQuery "Query206", acNormal, acEdit
End If
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.OpenQuery "Query207", acNormal, acEdit
End If
DoCmd.Close acForm, "NamesUsed"
DoCmd.OpenForm "CatSelector", acNormal, "", "", acEdit, acNormal
Forms!CatSelector!CatSelect = 32
DoCmd.DoMenuItem 0, 5, 4, 0, acMenuVer70 ' Form, Records, Save Record
Forms!CatSelector.RecordSource = "Query209"
DoCmd.OpenForm "NamesUsed", acNormal, "", "", acEdit, acNormal
DoCmd.SelectObject acForm, "CatSelector", False
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.RunMacro "ProspectCounter", Forms!NamesUsed!CountOfParents, ""
End If
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.OpenQuery "Query206", acNormal, acEdit
End If
If (Forms!NamesUsed!CountOfParents > 0) Then
DoCmd.OpenQuery "Query207", acNormal, acEdit
End If
DoCmd.Close acForm, "NamesUsed"
DoCmd.OpenQuery "Query62Append", acNormal, acEdit
DoCmd.OpenQuery "Query62Delete", acNormal, acEdit
DoCmd.OpenForm "SRCancel", acNormal, "", "", acEdit, acNormal
DoCmd.RunMacro "Macro135SR", , ""
DoCmd.Close acForm, "SRCancel"
DoCmd.OpenForm "TransferNumbers", acNormal, "", "", acEdit, acNormal
DoCmd.RunMacro "Macro910", , ""


GetNewNamesQuery_Exit:
Exit Function

GetNewNamesQuery_Err:
MsgBox Error$
Resume GetNewNamesQuery_Exit

End Function

 

[Rich]

your converted macro shows some of the limitations of macros and their conversion. Oh and DoMenuItem should be replaced with the RunCommand equiv.

 

[Mike375]

your converted macro shows some of the limitations of macros and their conversion. Oh and DoMenuItem should be replaced with the RunCommand equiv.

Isn't the DoMenuItem as a macro replaced by Runcommand macro action in later versions of Access.