• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

Access Problem....

Hi all,
         I have a simple database with 5 tables.... Out of these, 4 tables contain static data i.e they donot change....
One table is most important and contains huge amount of data. I have designed it as
5 Single fields, 3 Integer fileds and 1 Text Field(Max size is 16 characters).....

This makes it

5*4 + 3 *2 + 1 * 16 (Unicode Compression) = 42 bytes per record....

Now to test this I created the database and with the static tables the size of my database was about 500 kB which is normal.... Now I inserted about 300,000 records into my table and then I examined the size of my database. It was roughly
36 MB... SO I calculated that each record of mine is taking 36 * 1024*1024 /300000 = 126 Bytes / Record (Note that for such large data 500kB is not a big factor, So I havenot minus'ed it).... This is something I cant understand.... Shouldn't it be nearly 42 bytes/record??? Even if there is some overhead involved then still it should be somewhere near to 42 bytes and not almost triple sized..... I know this is not the proper way to check the record size but it gives a rough estimate.....

Can anyone point out where is my mistake? I have also tried to Compact and Repair my database but still the size remains the same..... Please guide me as I have very little knowledge about Databases....

Imran Arshad
0
imarshad
Asked:
imarshad
  • 5
  • 3
  • 2
  • +2
5 Solutions
 
BillystyxCommented:
Do you have any pictures in the db - embedded into forms, perhaps?

Billystyx
0
 
BillystyxCommented:
And are there definitely no memo fields (doesn't look so from your descrption but I thought it might be an idea to ask...)

Bilystyx
0
 
imarshadAuthor Commented:
I am just using it as a storage place for my large data..... There are no forms or any other thing.... I  have the frontend in VB6....

Imran
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
BillystyxCommented:
0
 
BillystyxCommented:
does any of the points in the accepted answer highlight or fix your problem?

Billystyx
0
 
imarshadAuthor Commented:
>>Indexing/not indexing a text field defined as 50 chars but containing an average of 45 chars makes a difference of
>>200Mb

I have a textfield of 16 characters with each record having 16 characters( i.e max and min characters in the field are 16) ..... So Indexing it or not might not be an issue...

>>Removing an index from a long integer field reduces the file size by 20mb.

I dont have a long integer field....

Imran
0
 
BillystyxCommented:
goto tools->analyze->table or performance and see what access has to say about your db. There may be some suggestions there...

Billystyx
0
 
peter57rCommented:
Hi imarshad,

I can understand that you might want the sizing explained, but in the end, what value is it.  You can't change it.

There are other overheads to table size which were published for A97 but
I don't believe these figures are published for A2000 onwards.

For A97 you would have an additional:
7 bytes per record
1 byte per field per record  (there is more overhead for memo or OLE fields)

So in A97 each of your records would have an additional 7 + 9 bytes = 16 bytes.

I don't know whether these overheads doubled in A2000+ or not.

And indexes can also be significant.

I appreciate this is not definitive, but as I say, even if you had perfect knowledge, what then?

Pete
0
 
stevbeCommented:
I'm with Pete ... it is what it is, you can't do much better than Reapir/Compact.
You *might* be able to make it slightly smaller if the table / field creation itself is done with code rather than the Access interface (it adds some custom properties for display purposes).

300,000 records at 36 MB ... roughly translates to ... hmmm ... 16-17 million records before the database becomes *full*, how long do you think this will take?

You could also split the data into seperate databases and link the tables.

Steve
0
 
Arthur_WoodCommented:
whay are you worrying about this as Access can support an MDB file which is 2 GIGABYTES in size.  If 300,000 records only makes your file 36 MG, then you can have up to 16 MILLION records - is that number anywhere close to the number of records you anticipate having in your database?  If so then you should consider upsizing to MSDE ( the FREE desktop verion of SQL Server).

AW
0
 
imarshadAuthor Commented:
>>I can understand that you might want the sizing explained, but in the end, what value is it.  You can't change it.
>>I appreciate this is not definitive, but as I say, even if you had perfect knowledge, what then?

I donot want the size being explained but because of my non-IT qualification I want to make sure that it is not my fault in designing the database and it is the fault/Design Issue in Access.... Since I have almost no experience in Databases I thought that a record of 42 bytes should be stored in the database taking only 42 bytes + some overhead.... I never knew that the Overhead for a record will be almost twice the size of a record......

********** Someone please confirm that I cant do anything else and it is because of the design of Access that an MDB file that according to my calculations should have been some where between 13-14 MB is now 36MB **********

>>16-17 million records before the database becomes *full*, how long do you think this will take?
>>consider upsizing to MSDE

I have spend many days to device a method to compress my data so that all my info can be stored in Access and we donot need to convert to Oracle or SQL-Server.... According to my rough calculations my Database will reach its limits in almost 25 days(Roughly 8 Million Records before it reaches the limit of 1GB for a table)..... I want to stretch it to 1 month..
Once I have been confirmed that I cant reduce the size of my database I will search for some more options.....

Imran
0
 
peter57rCommented:
Well the limit is 2GB as has been said a couple of times already.
You might consider putting this table into a database on its own and linking to it from the 'main' file (where you do your processing).  That will give you the full 2gb for that one table.

Pete
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now