DB size?

[Pat Hartman]

Your comparison of converted macro code to code that might be written by a real developer is flawed. No one who knows how to write code would write anything like what you have posted.

I just picked a random sample of bad code. There is no reason to test the same form field multiple times. One test is sufficient.
BAD:

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

Better:

If (Forms!NamesUsed!CountOfParents > 0) Then
    DoCmd.RunMacro "ProspectCounter", Forms!NamesUsed!CountOfParents, ""
    DoCmd.OpenQuery "Query206", acNormal, acEdit
    DoCmd.OpenQuery "Query207", acNormal, acEdit
End If

Plus, that piece of code repeats numerous times along with other code around it. No programmer would EVER do that. The code is repeated in the sample you posted simply because YOU repeated it in the macro. A programmer would use a Do Loop.

If you would stop standing so tall in your ignorance, you might actually learn something. The two code samples above may do the same thing but they are in no way "6 of 1, half dozen of the other" as several posters in this thread might think. Sample 2 IS better no matter what you think.

I understand that recreating your db is a daunting task and I agree that it is not necessary simply to "make it conform to some semblance of good practice". I suspect though that if your partner had any clue what you have done, he would disagree strongly because of the jeopardy to your joint business should you become unable to continue as primary developer.

I can tell by your continued "yes, but" posts that you have no intention of modifying your approach when developing new items. You will continue to do what you have always done, rely on macros and recreate duplicates of all objects. I consider my time to be way too valuable to waste on you any longer.

 

[Rakier]

One other thing to consider Mike. Eventually, you will run out of room. I'm not sure, but I think Access has a limit on the number of objects that can be in a database. Again, I'm not sure about that...but I thought I saw it somewhere. Also, each object takes up space and even if there is no limit to the maximum number of objects, you will eventually push against the size limits for a dB in Access95 which I believe has some effect on performance and reliability.

Like Pat though, I can see that no number of arguments are ever going to change your mind or position. You are content with the way things are and have no desire to change them. I hope that you don't encounter any problems. I know that you are tired of all the nebulous warnings that have been given, but believe me....Pat, Miles, Rich, etc... know quite a bit more about Access than I do, and probably more than you will ever know. You may have the knowledge of the insurance industry, but when it comes to developing databases, these people are the best. I would advise you to heed their warnings and learn from them. Best of luck.

I know...I know... "Yes, but..."

 

[Mike375]

Pat Hartman,

That code was converted by Access, not me.

When that macro was made I made two sections then tested. When it worked then simply copy and paste and then run down the page and change the category number.

From your post:

I suspect though that if your partner had any clue what you have done, he would disagree strongly because of the jeopardy to your joint business should you become unable to continue as primary developer.

If you took the time to read what people say you would see that 99% of the work on this data base is entering policy wording to reflect changes by the insurance companies.

What you also fail to realise is that if something happens to me the business would no longer continue to exist and that has nothing to do with the computer program.

Another thing you fail to realise is that both of us will probably retire in 2 or 3 years and sell this business.

If you spent more time making data bases and using them and getting yourself out and about instead of make a mammoth number of postings you would know that a business like this when sold will be to a larger group and they could not care less whether there was Dloop or whatever. It is what the data base does and why it does and the screen designs.

I might also add that the obvious extreme frustrattion my data bases causes you only illustrates your limitations and if a potential customer was reading this thread they would not want your services.

Mike

 

[Rich]

Pat Hartman,

I might also add that the obvious extreme frustrattion my data bases causes you only illustrates your limitations and if a potential customer was reading this thread they would not want your services.

Mike

now you really are taking the piss

 

[Mike375]

Rakier,

One other thing to consider Mike. Eventually, you will run out of room. I'm not sure, but I think Access has a limit on the number of objects that can be in a database. Again, I'm not sure about that...but I thought I saw it somewhere. Also, each object takes up space and even if there is no limit to the maximum number of objects, you will eventually push against the size limits for a dB in Access95 which I believe has some effect on performance and reliability.

The books say 1GB. I have some here that are 300mb but they are only a bunch of tables that are storing a lot of records.

I don't know about the number of objects but I could easily knock out 1/3rd of them and export them to another .mdb file as they are no longer used except sometimes I refer back to them.

Like Pat though, I can see that no number of arguments are ever going to change your mind or position. You are content with the way things are and have no desire to change them.

The desire I have to make changes to the data base is small and that small desire is completely squashed by the time factor involved.

I hope that you don't encounter any problems. I know that you are tired of all the nebulous warnings that have been given, but believe me....Pat, Miles, Rich, etc... know quite a bit more about Access than I do, and probably more than you will ever know.

I am very aware that they know a 100 times more about Access than I do but there is nothing I can do about the problems they say will come, at least not at the present time. However, the message does not seem to get across that is a data base that is basically static.

I know...I know... "Yes, but..."

That keeps getting said because some people are only focused on what they want to say and appear to be unable to grasp the simple idea that time is a totally prohibitive factor and that this data base is not one that is under development.

All this lose talk about.....use code etc is all fine but how long would it take me to learn what is required and then how long would it take to change not just this data base but all the smaller surrounding data bases, all of which would need to be changed.

It is not a case of me not accepting what they say but a case of not being able to do it due to time.

Mie

 

[Mike375]

now you really are taking the piss

I simply reponded in like

 

[WayneRyan]

Hey Mikey,

I knew from reading the start of this thread that it wasn't going to be very
productive with respect to software development.

HOWEVER, if I had a company that needed an application developed., I
would SURELY contact Pat/Rich. They would definitely provide a software configuration that they could LEAVE. They wouldn't have to be on retainer
or much less a full time employee.

This is an educational forum. The threads here serve to enhance the
member's knowledge of Access and the nuances that certain applications
require.

Granted, your software works for you, and you are willing to constantly
modify it to keep it in "synch" with the real world. GREAT!

It has been an interesting thread (Location should have been WaterCooler),
but I've had enough.

Wayne

p.s. The guidance provided by the people you slammed is invaluable to the
members here. I don't appreciate (respect or condone) you trying to
degrade them because of your deficiencies.

 

[Mike375]

Dear Wayne,

You will note that my role on this thread has been one of response. You will also see it is Pat Hatrman who has raised the stakes at different points, including her first posting on the thread.

From your posting:

Granted, your software works for you, and you are willing to constantly
modify it to keep it in "synch" with the real world. GREAT!

Well I would really like to see how a Pat Hartman data base needs nothing done when:

1) Policy wording must be changed when the insurance company changes its policies and or policy wordings.

2) When the insurance company changes its premium rating methods and especially the larger policy discounting system and switches from a discount based on pemium size to a discount based on sum insured or benefit size. Or it changes the combination in which various benefits can be combined.

3) When an insurance company changes the format in which it sends commission statements on Excel.

4) When the insurance company changes completely the format in which they send the base premium rate for the various policies.

5) When one of the companies that we buy lists of names from changes their format or we buy from another supplier.

Now here is a question or a scenario.

At present if the insurance company does any of the above then I can make the changes here myself, on the spot and quickly. Policy wording is an exceptions as that can involve a lot of typing and time with legal people.

Now lets say we can click our fingers and instantly my data base looks like a Part Hartman special. What do I do when the insurance company makes some of the above changes. Well I will tell you what I would have to do and that is what other groups have to do....."we won't be able to use that section of our software for a while as the ABC insurance company has made some changes and the computer people will be coming down next week etc. and etc."

Now why would I want to put myself through all of that. Tell me what I am missing?

She accuses me with a look down her nose attitude that will I go on using macros. So if I am a victim of one of the changes I listed above do I:

1) Use my dreaded macros and be in business the day of the day of the change or do I

2) Not use macros etc and then learn all the coding she or yourself knows and let the data base sit idle while I learn.

Mike

 

[Pauldohert]

I hope we all continue to offer Mike the benefit of our experiences however valuable they may be. Whether or not Mike heeds the advice (for whatever reason) is not really the point - only Mike can decide what is best for him to do.

- Mike should be congratulated with getting such an unconventional database to work, and what an impact he has made since he arrived.

Importantly the answers serve as a resource for all, and I am sure we have all learned something from this thread ( though I don't know what! )

I look forward to Mike starting a new thread and learning from him and the replys!


paul

 

[The Stoat]

Please, I actually do know Russian (I am currently learning) and from an alphabet with 33 letters I think I am more than capable of selecting the 24 that constitute Greek and making words from them.

I wasn't questioning your knowledge of the Russian Alphabet or anything else for that matter. I was simply asking what the result of a transposition translation would be. Even when you do transpose the Greek Alphabet the words are obviously not going to be English. Are you able to translate them through Russian to the English?

 

[Mile-O]

Na! ..............

 

[Rich]

Na! ..............

well why not? look all you need is a Macro for each word in the Russian alphabet, then on the after update event of your textbox you open each Macro with another macro and then use another macro to save the translations in each table for each word and then use the conversion wizard to make a silk purse from a sow's ear..............you can then sell the db to teachers.........and then come here and impress us all with it

 

[Mile-O]

Сласибо, Рыч.

 

[KenHigg]

When this insurance business is sold, this database will be a liability. To bad, sounds like it could be a tremendous asset...

 

[Denniz79]

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

Thanks for the great tip!!! I had some importcases myself, and now it went fomr 2.14 megs to 0,84 megs! Thanks!

 

[Mile-O]

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

Just to return to this question: what is your database for?

 

[Franknstuff]

Database

I'm making a DB for manufacturing. Inventory tracking, Ordering, Pick & assembly reports, Datasheets, Mod's, Vendor lists, Cost tracking, P.O.'s. That anybody can use. I have the structure in place, I'm just getting all the forms and Reports together. It's a lot smaller since I Compact & Repaired it (Thanks to this forum). My boss has a way of wanting things to look and do exactly what they think it should.

 

[mdemarte]

Franknstuff

Tried to put this in a nice table, like Pat did, but no luck for me. I have posted some sample database figures below.

My databases have no modules, currently. I hope to take a VB class within a year, so that may change.

AG: 6 Forms, 12 Macros, 18 Queries, 25 Reports, 4 Linked Tables, 4 Local Tables, 13Mb, used for 6 months.

Proj: 13 Forms, 11 Macros, 89 Queries, 18 Reports, 15 Linked Tables, 0 Local Tables, 50Mb, used daily and grows about 10Mb ever 3 months .

Dirt: 1 Forms, 0 Macros, 1 Queries, 0 Reports, 2 Linked Tables, 0 Local Tables, <1Mb, used infrequently.

Dog: 2 Forms, 3 Macros, 8 Queries, 3 Reports, 2 Linked Tables, 2 Local Tables, 4Mb, will be used daily with few records input daily.

NYS: 14 Forms, 5 Macros, 48 Queries, 28 Reports, 13 Linked Tables, 0 Local Tables, 50Mb, used during the winter heavily.

I work for a small, largely rural county. Most of my databases are used by a single user -- not the same one ;^). The differences to me are how frequently are they used and how much data is input, which is why I included that information.

Mike: I have converted data from users that did a pretty good job with simple databases. Others have stored complete names together, complete addresses (street, city, state, zip), have all numbers stored as text (dates, fees). Those are frustrating. I give credit where credit is due and I GENTLY point out problems. In the long run, if my databases save the users time and give them what they need, then I consider them to be successful. Occasionally, I even break an Access 'rule of thumb', like storing totals.

 

[Mike375]

mdemarte

What sort of macro actions do you have this one

AG: 6 Forms, 12 Macros, 18 Queries, 25 Reports, 4 Linked Tables, 4 Local Tables, 13Mb, used for 6 months.

Your mention on first and last name together brings up a point about putting user efficiency ahead of computer efficiency. For example we enter Alan J McDonald in one field and same for suburb, postcode and state. If the person wants to type alan j mcdonald that is fine. I then have a query with a whole series of Mid, Left, Right InStr IIF and so on so as to capitalise and get Mc and aslo to get the names, suburbs postcodes into their own fields.

Storing calculaterd values...you will be banned from this site

Mike

 

[mdemarte]

Mike: Ag deals with AG District data. Every 8 years, the four districts (one at a time) are reviewed. Farm Header records are related one to many Parcel records. The Farm Header record has part of the Parcel info listed in a sub-form.

Autoexec macro runs at startup and opens the switchboard and maximizes it (learned from this forum). Another macro finds the last Farm Header record and adds one to the Farm number for a new header record. (The user can override the farm number if they want to reuse one -- the farm number is the main key for the Farm Header record, but the users may delete a Farm Header if the Parcel(s) are attached to a different Farm Header). One macro copies the Farm Header owner name and address and creates a new Parcel record (parcels may have a different mailing address, but be part of a farm). Find All Records is a macro that I have in many of my databases, because the users like to push a button for that. There is a macro to show the specific Parcel record connected to the Farm Header record, and one to show the Farm Header record of the Parcel. I have 2 that do specific finds, then allow the user to ADD to that find (if they want) and then run a report. I have one that changes a blank single character field to X. I have a few that helped convert/set data after importing.

FWIW, it's easier to do finds on names when they are broken up -- but sometimes (as in the case of this database) the name is listed in one field. But then, the owner could be Buttermilk Farms or it could be Joe Smith.

I only have ONE database (out of about 50) where I store a total, but in that one I need the calculated value in order to make a decision about how to gather data for reporting. Yeah, I got razzed about needing it. I survived!