DB size?

Mike-

Do you have referential integrity set up between your tables?

I'm guessing no and that what you have set up is more like 300 spreadsheets stored in an mdb file with macros moving records between spreadsheets...

Regardless -

I could take the time to site here and type out a million reasons why your system is flawed, I could try and explain why having a table for each company is wrong, how to make it work better, etc.. but in the end you will disagree - tell me I don't know what I'm talking about - tell me how its different for you in your case (which it is not by the way), tell me how its different in the insurance business (its not), and then state that I'm attacking you - so...

good luck to you with your endeavors - I wish you the best of luck...

Kevin

Kevin,

From your posting:

Do you have referential integrity set up between your tables?

I'm guessing no and that what you have set up is more like 300 spreadsheets stored in an mdb file with macros moving records between spreadsheets...

No, not at all. In all cases where there is a One to Many (of which there are heaps) I have a macro open the many form to match the ID number on main form and then a series of Setvalues run that put ID number in, persons name (no I don't need it but I just like to see it) If there is no exsisting record for the ID number then of course the form opens blank.

I have very few queries that join tables. For example, where mail outs are done i do not use a query to link policy holder and policy benefits. Rather I have a query (sometimes two) that are linked into a Word document and policy holder details insert via Bookmarks, for which I have to use Visual Basic. That stuff just managed to creep in

Regardless -

I could take the time to site here and type out a million reasons why your system is flawed, I could try and explain why having a table for each company is wrong, how to make it work better, etc.. but in the end you will disagree - tell me I don't know what I'm talking about

Not so at all. I already know that most of you know far more about Access than I will ever know. But at the end of the day changes take time to impliment and doubly and triply so when I would also have to learn about it. If changes are easy then I will do them. For example I was using this for some query criteria from a form

>=[Forms]![Attempts]![1] And <=[Forms]![Attempts]![2]

Mile O Phile suggested another way, from memory it was

Between >=[Forms]![Attempts]![1] And <=[Forms]![Attempts]![2]

I changed mine to his, but it took all of 5 minutes with a copy and paste into a few queries.


- tell me how its different for you in your case (which it is not by the way), tell me how its different in the insurance business (its not), and then state that I'm attacking you - so...

The facts are that there are differences, many are just because of my choice, the way I go about the business. Some will relate to the nature of the business. A simple example might be a diary for telemarketing Vs a diary for a medical specialist. For the medical specialist most appointments will be made with someone who is not already in the data base. In my case the names are already there so a facility to make entry of the name is of zero value to me. On the other hand my diary is being used in a sales situation whereas the Drs diary is totally opposite.

I think what you are saying (and others as well) is that if you are cooking steak then cooking fish will be similar and it is ideal if the pots and pans are clean etc and etc.

good luck to you with your endeavors - I wish you the best of luck...

Thank you

But something you might consider which is dollar and time etc.

Let's just pretend I could click my fingers and have the knowledge that you and other have and all by next Monday. If I nows sit down and start to make the changes the time will be very considerable. If I pay someone to do it it will cost plenty and unless I also learn all the coding I won't be able to make the on the run changes.

Now if the case was one where the data base was limiting what we could do then of course things would be different. As I said above 99% of time spent on this data base is changing policy wording entries as insurance companies change their policies and there is no way around that except on the keyboard.

Mike

Kevin,

A PS.

One reason I have a lot of forms is because many are variations for appearance.

Telemarketing results can be influenced by the screens. Thus I might have 20 forms and simply save as Export in the data base and with the appropriate form name.

Mike

First and last post to this thread:

At the risk of putting a figurative hand in the middle of a catfight:

Both sides on this 'debate' are right, and both are wrong.

Mike can make his application ANY way he likes, so long as he is content with the way it works. Although he may actually benefit in the medium to long run by 'doing things the right way', he chooses not to do this for reasons of expediency.

Everyone else can do things their own way, and smugly tell themselves that Mike is eventually going to run into an unfixable problem with his approach. They may be right. Only time will tell.

Mike can even sell his thing if he wants, but it is HIS reputation which suffers if it doesn't work for others. Any reasonably knowledgeable IT Pro working for the purchaser will look at his application and come to their own conclusions as to its 'correctness' and 'supportability'.

{I do have one caution for Mike: if the purchaser insists on a db compatible with the 'latest version' of Access, Micro$oft has threatened to abandon support for macros for a few years now, in future versions.}



What bothers me about this thread is:

1) The fact that many experienced forum members refuse to accept that Mike truely believes that 'his' way seems to work for him right now. Please let it go (and pray that you don't have to inherit a requirement to support his application someday).

2) Mike has taken much of the well-intended commentary (solicited or not) on his techniques as an attack on him, and attacked back, leading to a 'polite flame-war'. Please let it go.

=================================================

On the subject of the original poster's question, I have made Access Db's with only 1 table, 3 queries, 1 form, 1 report, and 0 modules; and I have made db's with 22 tables, 140 Queries, 28 main forms, 11 main reports, and 9 modules (I have a habit of jamming many routines into one module), c/w FE/BE structure.
The size varies with literally every application, and I suspect that two 'expert' programmers can arrive at slightly different db sizes to solve exactly the same case.

I don't think that there is any such thing as 'typical' when it comes to Access db's

The nice thing about Access is that it accomodates a large range of db scales well, comes with an almost automatic upgrade path to a fancier MS product, and can serve well as an interface via ODBC with almost any 'full-blown' RDMS such as Oracle. All that, and it is easy to learn and use.

Mile,

From your posting:

Do you not even feel that Access 95 is lacking so many features compared to the late{r/st} version{s} of Access?

I am extremely resistant to changing software. I have used A97 a little bit and have done some things with A2000 over the phone. But I guess the bottom line is that I am not walking around thinking "if only I could do this". The one problem I do have with A95 is that there are some conflcicts with Windows XP, but I get around those problems.

What about support? Should your database [no will; just should] pack in and die then who are you going to call to fix it? This is a question of technological support too.

I have a large number of copies of the .mdb file and they are never more than a day old so I guess that offers me some protection.

And, then there's size. A database does have a maxium size (2GB in A97 upwards - I don't know about A95) and all these extra object definitions will be contributing to that size.

The books say 1GB. It was about 80mb compacted for the last 3 or 4 years and has recently grown to about 90mb compacted. It never has a real lot of records in it as the bulk of prospects names are held in other .mdb files and as prospect's names are consumed those people that don't become clients are moved to other .mdb files.

Apart from copies of the data base I probably have about 15 other .mdb files and they all store prospects names and also do a few things with those names. For example, one of them changes all phone numbers to the same formats, removes spaces, brackets etc. We do this so we can check for duplicate numbers.

We also sell a lot of names to other people after we have called them. That is another reason why there are lots of tables etc because used names are output to different tables and then exported to other .mdb files.

So as you can see the main data base remains fairly static in size because it is in reality a processor rather than a holder of records. Perhaps you could think of it as being like the kicthen sink in that 1000s of gallons of water pass trough it each year but there is never more than a gallon of water in the sink at any given time.

Mike

Micro$oft has threatened to abandon support for macros for a few years now, in future versions.}

Click to expand...

along with DAO, ADO, DoMenuItem....................................

Rich said:

along with DAO, ADO, DoMenuItem....................................

Click to expand...

Does anyone know if Access XP has all the macro functions?

Mike375 said:

Does anyone know if Access XP has all the macro functions?

Click to expand...

are you thinking of an upgrade?

Rich said:

are you thinking of an upgrade?

Click to expand...

We are considering getting Office XP, maybe in a couple of months or so when some business issues drop off. We have all new computers that have Windows XP and my business partner and I figure that if we got near 10 years from 95 then XP would see us out

I do know (at least with parts of this data base) that conversion to Access 2000 is much better then to Access 97 so who knows with XP.

Mike

Mike375 said:

I do know (at least with parts of this data base) that conversion to Access 2000 is much better then to Access 97 so who knows with XP.

Mike

Click to expand...

That suprises me since a2k was one of the most bug ridden versions ever

Rich said:

That suprises me since a2k was one of the most bug ridden versions ever

Click to expand...

I was just thinking that the reason my have been that we "converting" to A 97 whereas with A2000 we imported into a blank A2000 mdb. Only part of the data base was involved but it did not contain quite a variety of stuff from the main data base

Importing forms won't work on A95 when it is on XP. There are 3 bugs, at least for me and importing forms is the one I can't get around.

I wasn't going to contribute any more to this insanity but I was reading this month's issue of Access Advisor and I couldn't resist.
Program Responsibly

Pat Hartman said:

I wasn't going to contribute any more to this insanity but I was reading this month's issue of Access Advisor and I couldn't resist.
Program Responsibly

Click to expand...

Pat,

From your posted article:

Ultimately, it takes much less time to properly and consistently name objects, apply relationships, and structure code behind forms and reports than it does to figure out how a poorly built database is constructed. I'm sure you can spend as much time figuring out a badly constructed application as building it in the first place.

So in light of that consider the following:

My data base to day works and does all that is required.

The methods used to make the data base between 1996 and now are in the past. What is done is done.

If I was to adopt some or all of the various changes suggested the data base would become even messier as I would not learn enough about the coding and it would go through a long period of being half and half. If I can quickly learn all the coding to run a data base like this and learn quickly then it must be very easy to become a professional Access developer.

As your posted article says it is easier for the developer to make the data base from scratch than try and fix. So if the professional developer has to write up the data base rather than fix the data base then what chance would an amateur such as myself have of "fixing" it, especially given time restraints.

As to the data base "crashing" I still have not seen that explained except for the few points Mile O Phile made.

If for some reason in the future it becomes necessary to have something added but it can't be done because of the data base structure, then I am in the hands of the professional developer and according to your posted article he will make it from the beginning.

If there was any chance of the professional developer "fixing" it then the chances would be greatly reduced if I had gone in full steam trying to fix it myself.

Now considering all of that and the fact that you and some others pointed out my data base was a piece of crap what is the solution, bearing in mind I am in the business of selling insurance not Access development. Just how much time would I need away from the insurance business to:

1) Learn all that was necessary to make the data base the way you would make it and

2) How long once I had all this new found knowledge would it take to either remake it from scratch or fix it.

Mike

Slowly getting there.

Thanks KKilFoil for your DB count. Looks like I'm going to be around the same size. So cool!

but ... but ...

Sorry, everyone ... but I cannot help this reply (I tried to resist ... but ... but), especially after 134 relpies and 1685 views ... ... "each to their own" ... if it works then ... so be it (and I'll learn the new way .. because I have too ... but... but... I will recall this thread) ... go mike...go mike, go mike... I really appreciated the differences ... isn't that what being an individual means ... and yep, I will learn to code... at the same time I really love differences. Plus, an additional bit (I have no idea where to post it ... but ... but ... but ), this is a great forum... I have learnt heaps...I don't know much... yet... but ... but... but... give me time, pls and everyone is appreciated ... ty mike and all

Regards
Oz

Franknstuff said:

Thanks KKilFoil for your DB count. Looks like I'm going to be around the same size. So cool!

Click to expand...

Out of interest, what does it matter how your database compares to others? They are modelling something completely different to you and therefore may require more or less objects to achieve this.

From Pats Access Adviser Article -

"Users can't judge the quality of the applications we build" -- eh - who can then?

If its well structured with nice clean code but doesn't work the user will say its C***, they wont use it and won't pay for it, and they are correct!!

At least Mike has happy users and earns some pocket money!!!

If it works - it works. But everything can eventually be done better - not just Mikes stuff.

Pauldohert said:

"Users can't judge the quality of the applications we build" -- eh - who can then?

Click to expand...

Users - at least in my experience - are generally diabolical at using computers and so they're quite happy if they click something and it does what they want.

I'd say an independant and experienced eye/mind is qualified to judge.

Ok

I wasn't sure how envolved this DB venture was going to be, so I asked what is a Average DB size? When Mike answered 3000 tables I was having doubts about continuing. Then Pat gave me a nice average of her DB's then things starting to look better and when KKilFoil gave about the same general answer as Pat. Well, it doesn't seem like it's going to be so bad? It's nice that where on the original question. (Sorry Mike)

Pauldohert said:

If it works - it works. But everything can eventually be done better - not just Mikes stuff.

Click to expand...

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