DB size?

[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!

 

[Mile-O]

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.

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.

 

[Debased]

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!

 

[Mike375]

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.

 

[Mike375]

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

 

[KenHigg]

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...

 

[Rich]

If it were true, just have one big memo field for your data...

Hell no! now he'll do it and create another 300 Macros to separate it all

 

[Mile-O]

Yes, but entry is much easier and you only have to make the break apart stuff once.

Sounds like a Saturday night over here.

 

[adam_fleck]

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.

 

[mdemarte]

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.