UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Database Object Size, Access 2013    
 
   
Marto
post Sep 26 2019, 10:05 AM
Post#1



Posts: 402
Joined: 13-January 03
From: Dublin, Ireland


Hi

Is there any way to determine the size of each object in a database .i.e. forms, reports, tables, queries

I have been working on a database recently and the front end size has more than doubled in size to 70mb
I have been changing many things forms, queries and cannot identify which is causing the problem. I have done nothing (as far as I know) that would cause this.

I had some module in the past that could go thru the tables and determine the size. Is there something similar for tables, forms etc?

TIA
Martin

--------------------
There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers price only is that man's lawful prey.
Go to the top of the page
 
DanielPineault
post Sep 26 2019, 10:11 AM
Post#2


UtterAccess VIP
Posts: 7,010
Joined: 30-June 11



Firstly, have you compacted your db?
Secondly, normally bloating is cause by images.

As for determining the source of bloating the only way is to export individual object to individual databases and then you can get a relative size for each.

I created a tool a while back http://cardaconsultants.com/ms-access-database-analyzer/ but it is commercial.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
GroverParkGeorge
post Sep 26 2019, 10:26 AM
Post#3


UA Admin
Posts: 36,194
Joined: 20-June 02
From: Newcastle, WA


"...more than doubled in size to 70mb"

If it helps to put this into perspective, 70mb is not a particularly large accdb for a front end.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Jeff B.
post Sep 26 2019, 12:03 PM
Post#4


UtterAccess VIP
Posts: 10,336
Joined: 30-April 10
From: Pacific NorthWet


If you're looking for a tool that provides sizing information for each object, you may need to purchase one.

On the other hand, if you're just curious and/or strapped for cash, you could create a new (empty) db and import one object, check the size, delete the object and import the next, check the size, rinse & repeat... Remember that the Access db "container" will take up a certain amount of space, but that would/should be the same regardless of the object. You might want to run the C & R after deleting each object.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
akn_39
post Sep 26 2019, 12:38 PM
Post#5



Posts: 102
Joined: 9-June 09
From: Parksville, BC, Canada


Hello Everyone: I have a MS Access front-end which also grows over time, not to the extent that Marto describes, but enough to be annoying. In trying to find a solution, I came across this post, and I would be interested in opinions on what is proposed i.e. setting the front-end to Read Only. I tested it briefly and the front-end size did not increase, and I couldn't find any database functions that didn't work as a result of this setting, but I have not had a chance to test it more thoroughly.

Is this a known technique and are there considerations/potential problems with doing this?

Cheers. . .

Tony N.
Go to the top of the page
 
GroverParkGeorge
post Sep 26 2019, 01:47 PM
Post#6


UA Admin
Posts: 36,194
Joined: 20-June 02
From: Newcastle, WA


You want to make the accdb for the Front End Read Only?

That's an interesting idea, but not one I've every tried. What's the rationale for doing that?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
akn_39
post Sep 26 2019, 10:06 PM
Post#7



Posts: 102
Joined: 9-June 09
From: Parksville, BC, Canada


Hi George: The post I was referring to states:

Growing Front-End? Too stupid to be true but it works. My database is used (through the cloud) by several companies and therefore the application can hardly ever be closed for compression (The last one to leave puts the lights out: compresses the database). My customers need to be online in the database all of the time. In less than one week the front-end used to grow from 16Mb to over 2Gb! This was scaring the [censored] out of me. Solution: In the file explorer, simply right-click the front-end database, click 'properties' and check the 'read-only'-box. Access will try to write the enlarged front-end but does not crash on the read-only flag. Again: just too simple to be true! Best regards, Jaap Schokker, miniPLEX B.V., Wageningen, Holland

I was curious if any UtterAccess users have heard of this or tried it, and if there would any undesirable side effects.

Regards. . .

Tony N
Go to the top of the page
 
DanielPineault
post Sep 27 2019, 06:24 AM
Post#8


UtterAccess VIP
Posts: 7,010
Joined: 30-June 11



Bloating occurs for a reason, and if your db bloats as described "16Mb to over 2Gb", then you need to examine why your db is bloating and fix the underlying issue. Band aid fixes are not proper longer term production solutions! There is a reason the file is supposed to be full read/write!

A small bit a growth is perfectly normal, so say your compacted db if 50MB and it were to grow to 70MB, I wouldn't be worried in the least. Now if that same database grew from 50MB to 200MB, then yes, I'd be figuring out why this was happening and addressing the underlying root cause. In most cases this has to do with either repetitive Insertions/Deletions and/or embedding attachments within a database.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Marto
post Sep 27 2019, 07:04 AM
Post#9



Posts: 402
Joined: 13-January 03
From: Dublin, Ireland


I did the individual import of the items in the database and identified a report with and embedded image.
I removed it and db now back at 24mb.

Thanks to all for the advice thumbup.gif


--------------------
There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers price only is that man's lawful prey.
Go to the top of the page
 
GroverParkGeorge
post Sep 27 2019, 09:28 AM
Post#10


UA Admin
Posts: 36,194
Joined: 20-June 02
From: Newcastle, WA


Thanks, that's probably an uncommon approach to the accdb bloat problem.

For one thing, the back end with your data would normally be stored in a shared location on a LAN. And that means you can't make it read-only. After all, people must write to it to add new records.

For another thing, bloat is most commonly the result of the processes Daniel describes. And that would be limited to the copy of the front end accdb which you give to each user. Each user, therefore, would be bloating only their own copy of the FE, whichyou can address by setting the option to compact the accdb every time the user closes it.

Attached File  COmpactOnClose.jpg ( 25.31K )Number of downloads: 0


In the quote you posted, the implication is that that person has ignored the standard advice NOT to share a single FE accdb among multiple users. An option that is not popular among experienced developers.

That means, as far as I can see, that under normal circumstances no one really benefits from making an accdb read-only. That said, if you want to implement it in a production environment, I would be quite curious to learn how it works out.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
isladogs
post Sep 27 2019, 11:13 AM
Post#11


UtterAccess VIP
Posts: 1,895
Joined: 4-June 18
From: Somerset, UK


Coming back to the original question, almost all of the space occupied by a database is due to the tables ….unless you have lots of embedded images in your forms/reports.
Obviously attachment fields will be particularly bad in terms of space taken up
In contrast, queries occupy almost no space.

If you want to get an idea of the space filled by each non system table, you may find this utility by vkl interesting Table Size Analysis
It uses a similar idea to that suggested by Jeff B but instead copies each table into a new database in turn, measuring the increase in size after each table is added.
When done the external database is deleted. Its fast and seems to work well.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
FrankRuperto
post Oct 27 2019, 03:04 PM
Post#12



Posts: 353
Joined: 21-September 14
From: Tampa Bay, Florida, USA


I have seen frontend bloating after making several mods to objects like forms and reports, so I usually compact after making mods.
Since my frontend makes use of temp tables, which can also bloat, when launching my app I always use a shortcut that runs a batch file that makes a copy of the frontend master and then launches the frontend copy. This way my users are always running a fresh frontend with no bloating or corruption. This also makes it easier to distribute updates, and makes sure everyone is working with the same version.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 01:46 AM