DB size?
- Thread starter Franknstuff
- Start date
Whew!
I think Mike could have redesigned and recreated an entirely new database in the time he has spent defending the one he has currently! And it would have been time much better spent because believe me, if he tries to convert it, it is going to crash big time! Good luck! This thread has been quite a ride!
I think Mike could have redesigned and recreated an entirely new database in the time he has spent defending the one he has currently! And it would have been time much better spent because believe me, if he tries to convert it, it is going to crash big time! Good luck! This thread has been quite a ride!
M
Mike375
Guest
Mile-O-Phile said:Just chipping in again: that's why its recommended to store each component of a person's name separately. It's easier to concatenate than to break down.
Yes, but entry is much easier and you you only have to make the break apart stuff once.
M
Mike375
Guest
I only have ONE database (out of about 50) where I store a total
Are those 50 data bases ones you have made for other people or do you have several that are for your own personal use.
Mike
Are those 50 data bases ones you have made for other people or do you have several that are for your own personal use.
Mike
KenHigg
Registered User
- Local time
- Today, 04:35
- Joined
- Jun 9, 2004
- Messages
- 13,327
Mike375 said:Yes, but entry is much easier and you you only have to make the break apart stuff once.
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...
R
Rich
Guest
KenHigg said:
Hell no! now he'll do it and create another 300 Macros to separate it all
adam_fleck
Registered User.
- Local time
- Today, 09:35
- Joined
- Apr 30, 2004
- Messages
- 85
another monster
I maintain and am partly responsible for the design of a mission critical front-end/back-end monster with a puzzling quirk.
The backend (data only) is ~570MG, but compacts down to ~50MG. The front-end .mde (linked tables, forms etc) is about 3MG. 80 people will be using the thing concurrently (via their local mde front end or via Word) on a typical working day.
The puzzling thing is that a compacted version of the backend will last for about 5 minutes before it corrupts again. Corruption used to be fairly regular, and we used Jet Comp successfully to repair it, but after a while this stopped working. When the thing goes corrupt now - about once every two months - we move the corrupt version, replace it with last night’s back up and then pipe in data that has been input so far today. This takes about 5 minutes. With luck, this huge fat, un-compacted backend will stay haemorrhoid-free for another two months. We haven’t been able to get to the bottom of this.
Some stats :
Back End
Tables: 35 (coming down to ~25)
Nothing else
Front End (Access mde moving to adp)
Forms: 36
Queries\Views: 10 (going up to ~200 , though this will take a little time)
Stored Procedures: 10 (going up to ~50)
Reports: 4
Macros: 1
Modules: 15 (coming down to 10?)
Front End (Word)
Forms: 30 (coming down to ~10)
The database is mostly normalised, though there’s one place where normalisation was just too slow, so duplication of non-crucial data was allowed. A text field that captions data in a very long detail table (~500,000 records) was duplicated in the detail table instead of being pulled in by a query, as were two other fields that determine dataType, and pop-or-not - will a pull down list appear when the user edits the data field?
80 concurrent users is daft, I know, but we're moving the thing to SQL server in ~2 weeks time. The SQL back end is now in an almost usable form, and the Access and Word front ends are just about working too.
The database, in it’s Access guise, has ~20,000 records in the main table (7000 were entered in the last 2 years and most of the records in the detail tables are associated with them). There are ~40,000, 500,000, and 80,000 records in the three main detail tables, caseContact, caseDetail, and caseDocument, plus ~20,000 records in the table that stores the entries that appear in pull down lists.
The move to SQL server, if it works as planned – and it seems to be looking good at the moment - will be very welcome.
I maintain and am partly responsible for the design of a mission critical front-end/back-end monster with a puzzling quirk.
The backend (data only) is ~570MG, but compacts down to ~50MG. The front-end .mde (linked tables, forms etc) is about 3MG. 80 people will be using the thing concurrently (via their local mde front end or via Word) on a typical working day.
The puzzling thing is that a compacted version of the backend will last for about 5 minutes before it corrupts again. Corruption used to be fairly regular, and we used Jet Comp successfully to repair it, but after a while this stopped working. When the thing goes corrupt now - about once every two months - we move the corrupt version, replace it with last night’s back up and then pipe in data that has been input so far today. This takes about 5 minutes. With luck, this huge fat, un-compacted backend will stay haemorrhoid-free for another two months. We haven’t been able to get to the bottom of this.
Some stats :
Back End
Tables: 35 (coming down to ~25)
Nothing else
Front End (Access mde moving to adp)
Forms: 36
Queries\Views: 10 (going up to ~200 , though this will take a little time)
Stored Procedures: 10 (going up to ~50)
Reports: 4
Macros: 1
Modules: 15 (coming down to 10?)
Front End (Word)
Forms: 30 (coming down to ~10)
The database is mostly normalised, though there’s one place where normalisation was just too slow, so duplication of non-crucial data was allowed. A text field that captions data in a very long detail table (~500,000 records) was duplicated in the detail table instead of being pulled in by a query, as were two other fields that determine dataType, and pop-or-not - will a pull down list appear when the user edits the data field?
80 concurrent users is daft, I know, but we're moving the thing to SQL server in ~2 weeks time. The SQL back end is now in an almost usable form, and the Access and Word front ends are just about working too.
The database, in it’s Access guise, has ~20,000 records in the main table (7000 were entered in the last 2 years and most of the records in the detail tables are associated with them). There are ~40,000, 500,000, and 80,000 records in the three main detail tables, caseContact, caseDetail, and caseDocument, plus ~20,000 records in the table that stores the entries that appear in pull down lists.
The move to SQL server, if it works as planned – and it seems to be looking good at the moment - will be very welcome.
mdemarte
Computer Wizard
- Local time
- Today, 08:35
- Joined
- May 8, 2001
- Messages
- 138
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.
Adam: Sounds like a good idea to move to SQL, with the number of users and records. One of my databases MAY end up there if it gets expanded. Regarding your corruption problem, how long has it been going on? Could it have been from the last update? Or, do you have a user with a PC lockup problem that ends up causing the corruption? A few of the Approach databases that I had would get locked up and need to be compressed every 3 months. When I converted to Access, I realized that Approach did not require a primary key, so I had to add one. So far (knock on wood), the Access DBs have rarely locked up.
Adam: Sounds like a good idea to move to SQL, with the number of users and records. One of my databases MAY end up there if it gets expanded. Regarding your corruption problem, how long has it been going on? Could it have been from the last update? Or, do you have a user with a PC lockup problem that ends up causing the corruption? A few of the Approach databases that I had would get locked up and need to be compressed every 3 months. When I converted to Access, I realized that Approach did not require a primary key, so I had to add one. So far (knock on wood), the Access DBs have rarely locked up.
M
Mike375
Guest
KenHigg said:
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
M
Mike375
Guest
Mile-O-Phile said: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
M
Mike375
Guest
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
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
M
Mike375
Guest
KenHigg said:
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
M
Mike375
Guest
KenHigg said:
I am full up
M
Mike375
Guest
KenHigg said:
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
