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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> 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
 
 
Start new topic
Replies
Jeff B.
post Sep 26 2019, 12:03 PM
Post#2


UtterAccess VIP
Posts: 10,335
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#3



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#4


UA Admin
Posts: 36,171
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#5



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
 
GroverParkGeorge
post Sep 27 2019, 09:28 AM
Post#6


UA Admin
Posts: 36,171
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#7


UtterAccess VIP
Posts: 1,879
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#8



Posts: 333
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
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    7th December 2019 - 01:04 AM