DB size?

[Mike375]

I really hate to chase this rabbit, but: For the benefit of other novices - This is just wrong. No explanation needed. If it were true, just have one big memo field for your data...

Not right. Firstly, at least on Access 95, you can't sort on a memo field.

Next, there are advantages that are only too obvious in having first name , last name etc in their own fields. However, there are advantages of making entries in entering Alan J Cooper in one field.

As I said to Mile O Phile I only had to make the query to break up the data once.

Entering the data is the key point.

Mike

 

[Mike375]

Sounds like a Saturday night over here.

Kind of like that

Actually, if were to look at how some of this data base is made you would think it was made on a Saturday, a late Saturday night

Mike

 

[Mike375]

Mike: I am a programmer, so NONE of the databases that I have set up are for my use. Also, I PREFER the name and address separated, but the info coming from taxes is not supplied that way.

But I do separate with a query.

Depending on who is doing the entering they can either enter the full name in one field and ditto for address or they can in separate fields. People who do a lot of data entry like entering in separate fields but typists generally prefer to type into one field.

Same principle with telemarketing. Some people prefer looking at a screen with name, address and phone in separate fields. Others, myself included, prefer looking at one field for the data. Our telmarketing screen allows the person the choice with a click. I think I have nearly worn out the Visible and Invisible properties

Mike

 

[KenHigg]

'Not right. Firstly, at least on Access 95, you can't sort on a memo field.' - Sorry, I yield to the professional Access developer.

 

[Mike375]

'Not right. Firstly, at least on Access 95, you can't sort on a memo field.' - Sorry, I yield to the professional Access developer.

Even if you wanted to enter the full name and address in one field (which I don't) why would you need a memo field.

But tell me, if I was to remove the query that separates out first name and last name and breaks up the suburb, postode and state and then have the data entered in separate fields, what would I gain. I know what I would lose.

Mike

 

[KenHigg]

Never mind.

Say, would you like to buy some insurance?

 

[Mike375]

Never mind.

Say, would you like to buy some insurance?

I am full up

 

[KenHigg]

Are you sure you have the proper coverages?

 

[Mike375]

Are you sure you have the proper coverages?

Perhaps I need a policy to cover things if this data base explodes in my face

I am heading to bed now, 11.45pm down here. I have been up for the last few nights entering policy wording data and have now finished.

Goodnight

Regards

Mike

 

[KenHigg]

Darn ...

 

[Mile-O]

Same principle with telemarketing. Some people prefer looking at a screen with name, address and phone in separate fields. Others, myself included, prefer looking at one field for the data.

It's not about what people prefer; it's about thinking logically and doing what's right. If they don't like it at first then they can get used to it.

 

[adam_fleck]

Lock up

mdemarte,

Corruption started about 2 years ago after the install of a management accounting system. The system backend ran on its own server, but the front end install required a heap of Windows patches. The patches may have had nothing to do with it, but we had a corruption storm almost immediately.

We learned to deal with this, and got very quick at fixing the thing (using jetComp.exe) - down to about 2 minutes.

With time, we noticed that corruption would go in cycles, there would be a month of hell (4 corruptions / day), then two or three months of easy use.

Eventually, we reckoned a corruption cycles would end once the database got bloated enough that Access was effectively locking individual records anyway. This is why we started restoring from a backup then piping missing records in. After that, we would get corruption maybe once every three months or so.

There are no doubt Access locking issues we haven't addressed. But dodgy network connections, one or two very slow PCs, users who have many applications open (8 + 10 Word documents + 3 Excel Workbooks), and Ctrl+Alt+Dels seemed possible cuplrits too.

Then we had a nasty server crash (air conditioning failure in the server room on a Sunday night). Although everything was up and running for first thing Monday morning, the database corrupted almost immediately. This seemed to be a particularly nasty corrupt, and JetComp.exe would no longer fix it. We restored a backup, managed after several attempts to rebuild a copy of the corrupted version (on the Citrix Server) and piped the missing records in.

Since then we've abandoned JetComp, and take at least one backup (automatically) every day.

 

[KenHigg]

I have no idea how come this discussion wound up in this thread but I do have an idea for the corruption issues. Given the scenario you have, I would create a log table and record when and where users log into the database. The, maybe, when the db seems to be in a series of corrupting cycles, you could isolate a user / workstation? (Just an idea)...

 

[Rich]

I am full up

we're fed up too

 

[Pauldohert]

To sort of go back to the original question - what kind of impact would the size of a access FE have on its performance.

How important is it to get rid of redundant forms , queries etc.


Thanks

 

[KenHigg]

(Just my humble opinion) I don't think the number or latent objects are that important (As evidenced by Mike375's db). What has crushed one of my db's was the number of relationships and the complexity of the active form. One form in particular had to work with data in about 20 related tables and it had numerous pages and subforms, etc. It was (is) a real dog. And there wasn't that many records; prob. 8-10k. But, another db has a form looking at two related tables with over 250k records with up to 10 user hitting it at the same time and it cruises... Go figure..

 

[Mike375]

To sort of go back to the original question - what kind of impact would the size of a access FE have on its performance.

How important is it to get rid of redundant forms , queries etc.


Thanks

I will tell you the negatives mine has because of size, but remember this is Access 95 so your mileage might differ. Actually, I think it is not size but number of objects.

Firstly, and this is no real big deal, if you attach a macro to say the OnClick of a text box or label and if the computer is low speed, then it takes a while for the macro to "appear". The reason is simply that the drop down list in my case has 2000 of them.

In the expression builder box you can't bring up the pre packaged functions like IFF etc. That caused me a bit of inconvenience some years ago. What I use to do was to make the macro or query in a new .mdb file. If I was altering one then I would export the query and its table of the macro. These days when I make the odd changes to the DB that does not affect me because about any and every function I need has already been made in the DB so I just open a query or macro in deisign view and copy one out and then just change the field names or other details.

The other problem with the large .mdb file, but probably not an issue these days, is copying it onto Zip drives etc. I use to have Zips that were 100 mb and I always had to compact my DB to fit the thing on.

I agree with the points that KenHigg has made in that some things seem to happen without reason.

I don't think performance is altered except that the data base window opens a tad slower opening. I have several .mdb files that have extracts from my main DB and there is no performance difference when running on the small .mdb (some are only 2 or 3 mb ands with a couple of tables, a few queries and a few macros) as compared to running in the full data base.

In my humble opinion I think when you are starting out you are better to leave a lot of the objects that have been discarded. Some of them can be handy a bit later as an "instruction manual". My data base went through a virtual remake in 199 but I still kept the DB that had been developed between 1996 and 1999. On the odd time or two I return to it see how I did something.

One thing I would advise is to keep making copies of what you are making. Access will on first copy will create a file Copy of DBName then do copy again and you will get Copy (2) of DBName and so on. By doing this you can never slip back behind where you were.

Ultimately, I think the size of your DB is determined by your own personality and of course uses. A friend of mine in the insurance business is quite competent with Access and also Excel. He has lots and lots of smaller .mdb files as in my opinion he is very spreadsheet minded and a very compartmentalised person.

Mike

 

[Mile-O]

A friend of mine in the insurance business is quite competent with Access and also Excel. He has lots and lots of smaller .mdb files as in my opinion he is very spreadsheet minded and a very compartmentalised person.

In our opinion, you are very spreadsheet minded.



And have to be taking the piss.

 

[Mike375]

In our opinion, you are very spreadsheet minded.



And have to be taking the piss.

I understand why you say that.

However, while I have many tables the data does funnel back to very few tables. I consider my data base to be like a restaurant that serves all types of foods and you can get all the foods at your table. However, the restaurant has a separate kitchen for each of the different categories of meals.

But I was not referring to a spreadsheet mentality in terms of how you make something but rather lots of.mdbs for lots of different things. Sticking with the restauarant analogy this friend of mine needs to go to a different restaurant for each category of meal.

Mike

 

[Rich]

I understand why you say that.

However, while I have many tables the data does funnel back to very few tables. I consider my data base to be like a restaurant that serves all types of foods and you can get all the foods at your table. However, the restaurant has a separate kitchen for each of the different categories of meals.


Mike

don't ever become a chef
where do you eat at anyway?