how can I get the Access 2003 Database size in VBA

[Guest]

I want to write VBA code to check my current MS Access 2003 database size so
that I can compact and repair automatically if the size exceeds a certain
threshold, and I need to know how to go about getting the size of the
database using code.

 

[Ofer Cohen]

FileLen - will return the size of a file
CurrentDb.Name - Will return the current mdb name and path

So, try:
FileLen(CurrentDb.Name)

 

[Tony Toews [MVP]]

I want to write VBA code to check my current MS Access 2003 database size so
that I can compact and repair automatically if the size exceeds a certain
threshold, and I need to know how to go about getting the size of the
database using code.

What happens when the database grows normally as more and more data
gets put into it? Then you might end up compacting every few hours
when it exceeds your now too small threshold.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Guest]

Thank you Ofer for your solution, which worked fine.

Thanks Tony Toews for your comments; I take your point, but my DB is a
relatively small one to keep a track of a club membership and it doesn't get
updated too often.
I will store the last compacted date and remind the user to compact either
every x days or when the DB exceeds a threshold size by more than 50%. The
compacted DB with all member data loaded is around 6MB, so I intend setting
the threshold at 10MB and then suggest a compact if/when it exceeds 15MB.
This will meet my requirement, but I can see that it wouldn't suit everyone.

 

[John W. Vinson]

I intend setting
the threshold at 10MB and then suggest a compact if/when it exceeds 15MB.
This will meet my requirement, but I can see that it wouldn't suit everyone.

These are *very modest* databases. I'm dealing daily with databases of 300 to
1200 MBytes. I think you may be overly concerned, though a good regular
compaction policy is A Good Thing in general.

You *are* making current backups before every compaction, I hope?

John W. Vinson [MVP]

 

[Tony Toews [MVP]]

These are *very modest* databases. I'm dealing daily with databases of 300 to
1200 MBytes. I think you may be overly concerned, though a good regular
compaction policy is A Good Thing in general.

I only compacted one clients 300 Mb database every few months.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Guest]

My DB is "small potatoes" by comparison to the stuff you guys are dealing
with, but I enjoy finding out how things should be done properly just in case
I ever get around to doing a big DB..

thanks again..

 

[Tony Toews [MVP]]

My DB is "small potatoes" by comparison to the stuff you guys are dealing
with, but I enjoy finding out how things should be done properly just in case
I ever get around to doing a big DB..

Keeping in mind that one persons proper is another persons improper.
And you can read anything you like into that statement. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/