PLS HELP! every year run out of space.. data only (3 tables) = 2gb

[sandy2011]

Hi

every year around this time... I run out of space.. data only (3 tables) = 2gb -- Its a call log system that uses a lot of information...

is there any way I can squeeze a little more in? or set up structure so that rather than getting 11 months from database.. I can get 12 months or more?

I split the information between two databases - 1 data 2 system.. I isolated the 3 main data tables and they equal 2gb in total... so these 3 tables are were the majority of data is... one is main table and the others subform information.....

any ideas?

 

[MSAccessRookie]

Hi

every year around this time... I run out of space.. data only (3 tables) = 2gb -- Its a call log system that uses a lot of information...

is there any way I can squeeze a little more in? or set up structure so that rather than getting 11 months from database.. I can get 12 months or more?

I split the information between two databases - 1 data 2 system.. I isolated the 3 main data tables and they equal 2gb in total... so these 3 tables are were the majority of data is... one is main table and the others subform information.....

any ideas?

It is very hard to make any observations with so little information to consider. For instance, what are the structures in your Tables?

If your table structures that are not normalized and have repeated data such as names and ID Codes, the repeated items could be moved to additional tables, and using an FK to access the new Tables will normally save some space.

Show off the data structures and perhaps it will be easier for people to make additional comments

-- Rookie

 

[pr2-eugin]

Only three tables for a call log system? sounds like you are trying to squeeze in a lot of information in one table.. Just remember this.. A table that has fields defined, but never used will still take up memory..

The way I see it, when you create a table with the following fields..

tbl_SampleOnly
sampID - PK
sampName
sampDOB
sampNotes1
sampNotes2
sampNotes3

The structure by itself is DeNormalized, as the information is stored across each record and not in a related table.. So when you add a new record, although you do not use the three fields for notes then; the space required by these fields are allocated.. This is where the unwanted occurs.. If you had something like..

tbl_SampleOnly
sampID - PK
sampName
sampDOB

tbl_SampleNotes
noteID - PK
sampID - FK
notesTxt

Only when you add notes to the second table, the space is accessed/created.. Until that the space remains free so you can enter the required information..

 

[sandy2011]

thanks access rookie!

see attached! mdb with 3 main tables

 

[sandy2011]

thanks pr....

no.. there are more than 3 tables.. but I understand what you are saying.. I did create a subform for the notes.. but there are a lot of notes.. a lot of toing and froing!!!... (he said/she said etc.) -- this is prob where the majority of data is..

it is the 3 main tables behind the call log input form that holds the majority of data....

 

[pr2-eugin]

I did look over the table, as imagined, you have so many fields which you should as in my second post should be in another table.. Few examples..

• Picture1, Picture2 not to say just repetitive but also OLE object.
• Date_Reported, Date_Logged IMO repetitive
• nature of complaint, comments, complaint typeID - needs to be in a seperate table.
• Special notes - still needs to be in Table Notes.

It can go on and on and on.. So sort the structure out.. Then you might be able to do something about the issue at hand..

 

[MSAccessRookie]

thanks access rookie!

see attached! mdb with 3 main tables

I suspect that your biggest offender is the embedded OLE Objects. If you take pr2-eugin's advice you could create a related Table to store the pictures in, you could save space on any record that does not have two pictures. in addition, you could also arrange to accomodate a record that requires three or more pictures.

on the same subject, is there a reason that the OLE Objects are stored in hte database? You can always create an OLE Objects Folder to store them in, and use Hyperlinks to access the pictures as they are required for Forms or Reports. I suspect that this would save a substantial amount of space.

Lte us know what you think.

-- Rookie

Once again that pr2-eugin guy is just a little faster than me. While my suggestion is a Quick Fix and could be the start of how to get past your issues, everything he says is true and should be considered, since each suggestion will create additional free space where optional information is concerned.

 

[sandy2011]

thanks pr

sorry .. my mistake.. didn't explain fully..
there are 22 tables in total..



Picture1, Picture2 not to say just repetitive but also OLE object.
they are only linked not stored..


Date_Reported, Date_Logged IMO repetitive
the date fields are different dates and need to be logged individually... not repetitive

nature of complaint, comments, complaint typeID - needs to be in a seperate table.
they all in separate tables

Special notes - still needs to be in Table Notes.
call log subform contains special notes..


however you have given me an idea.. I might separate the subform with the notes into a different database.. however, not sure how it will link up.. something to think about..

 

[namliam]

You could try limiting the text fields to a proper maximum length instead of the 255, dont think this actually saves space....

CallRef seems to be your (foreign) key in your tables why have an auto number field?

Do you add/delete data regularly? If so is compacting any option?

make one database per table?

I have a database some place that consumes > 1 gig per month of data, I simply (automaticaly) save and rename the backend so I have nice "handleable" 1 gig databases without having to worry about the size if you want I can dig up the code which isnt all that complex to be honest

 

[pr2-eugin]

Once again that pr2-eugin guy is just a little faster than me.

Faster ? Ha Ha ! still a long way to go to do what you do

they all in separate tables

I am a bit lost.. you say they are all in different tables, yet they are all in one single table CALL LOG for the DB you have attached..

Anyway, I would say +1 for hyperlinks. I do that in most of my applications. Very rarely use OLE, Attachments or Memo types..

however you have given me an idea.. I might separate the subform with the notes into a different database.. however, not sure how it will link up.. something to think about..

Create a New DB in the same backend, and create link to the backend file as normal, one of my application has two backend linked to one front end.. It is possible.. Although some measures like persistent connection needs to be maintained to make it "run" faster..

 

[CJ_London]

If these are pictures, rather than word documents etc, you are better to keep them in a directory, suitably named and just have a text field with the path name. Then in your form/report use an image control with the text field as its control source.

You can also split the backend into 2 or more dbs to spread the tables about a bit.

But does sound like you need to normalise the data

 

[ButtonMoon]

however you have given me an idea.. I might separate the subform with the notes into a different database.. however, not sure how it will link up.. something to think about..

How about upsizing the database to SQL Server Express. 10GB is the limit there and if you need more you can consider upgrading to SQL Server Standard.

 

[jdraw]

I totally agree with the comments from others.
Research database design
Normalize your data
Consider SQL Server Express as ButtonMoon suggests, but do the Normalization first.

Question for you--
Do you/Did you run Compact and Repair to recapture any wasted space?

 

[PMJ]

Hi, I would suggest the use of linked tables to increase database space. House your 3 tables in separate Access databases. Each database should contain just a single table. Thereafter create a 4th database as the main database where you will keep your data entry forms. Finally, in the 4th database create links to the tables that are in the other databases. In this way it would seem that you can increase your storage space threefold in your situation.