Programmatically Accessing Database Size

[GPO]

Access 2000
I want my database to check it's own size (say with an on-
click event associated with a cmd button). If it is over a
certain size, it want a msgBox to notify the user that
they should compact and repair (via the normal menu
selections). The database is designed to only ever hold
data temporarily. It imports text, checks it for certain
features, and then prepares a report for the user. The
data is then deleted. This means that after several runs
it attains a pretty hefty size. It compacts on close which
is a partial solution, but prolonged use without closing
might be a problem. It would be good to warn the users
before they run their data through, that the database is
starting to be a bit swollen.

In other words (pseudocode),

Private Sub cmdRunDataChecks_click()
FileSize = GetFileSize() 'I don't get this bit...
If FileSize > 50mb then
msgBox "Database should be compacted..."
Else
'continue with normal processes
End If

End Sub

Regards

GPO

 

[GPO]

I've just realized that I can probably do it with the
FileSystemObject in the Microsoft Scripting Runtime.

This raises a different question though. This db will
potentially be deployed to about 40 users all with
different Windows OSs and hardware. The only "non-
standard" reference I've got selected at the moment is DAO
3.6. What does Access do if the references (dlls, ocxs or
whatever) are not in the same path (i.e.
c:\blah\blah\blah\)on the destination machine as they are
on mine?

Also, what are the odds that some of these machines won't
have the DAO 3.6 or Scripting Runtime at all?

Regards
GPO

Regards

GPO

 

[Dirk Goldgar]

Access 2000
I want my database to check it's own size (say with an on-
click event associated with a cmd button). If it is over a
certain size, it want a msgBox to notify the user that
they should compact and repair (via the normal menu
selections). The database is designed to only ever hold
data temporarily. It imports text, checks it for certain
features, and then prepares a report for the user. The
data is then deleted. This means that after several runs
it attains a pretty hefty size. It compacts on close which
is a partial solution, but prolonged use without closing
might be a problem. It would be good to warn the users
before they run their data through, that the database is
starting to be a bit swollen.

In other words (pseudocode),

Private Sub cmdRunDataChecks_click()
FileSize = GetFileSize() 'I don't get this bit...
If FileSize > 50mb then
msgBox "Database should be compacted..."
Else
'continue with normal processes
End If

End Sub

Regards

GPO

This should work for an .mdb file:

FileSize = FileLen(CurrentDb.Name)

FileLen() is a built-in function.

 

[Jon]

Access 2000
I want my database to check it's own size (say with an on-
click event associated with a cmd button). If it is over a
certain size, it want a msgBox to notify the user that
they should compact and repair (via the normal menu
selections). The database is designed to only ever hold
data temporarily. It imports text, checks it for certain
features, and then prepares a report for the user. The
data is then deleted. This means that after several runs
it attains a pretty hefty size. It compacts on close which
is a partial solution, but prolonged use without closing
might be a problem. It would be good to warn the users
before they run their data through, that the database is
starting to be a bit swollen.

In other words (pseudocode),

Private Sub cmdRunDataChecks_click()
FileSize = GetFileSize() 'I don't get this bit...
If FileSize > 50mb then
msgBox "Database should be compacted..."
Else
'continue with normal processes
End If

End Sub

Regards

GPO

GPO

VBA has a built-in function, FileLen, that returns the size of a given
file in bytes. You can therefore use:

FileLen(CurrentDb.Name)

Jon

Access tips & tricks - http://www.applecore99.com
Microsoft Access webring -
http://a.webring.com/hub?ring=microsoftaccess

 

[John Spencer (MVP)]

I wonder why you don't just build a temporary database and tables and attach to
them. Tony Toews' site has some sample code for doing this. This way, your
worry about bloating and the needed compacting is pretty well taken care of.
Less hassle for your users, more hassle for you in doing the coding.

Look under the Downloads section at
http://www.granite.ab.ca/accsmstr.htm

 

[GPO]

Thanks all for your good suggestions. John, I was thinking of something
similar to your suggestion (but not quite as ingenious, although yours
raises the bar a bit for me, there are dozens of tables to create/convert
each time).
I wondered about splitting the database into a front end and two backends.
One backend would contain about 30 lookup tables (call it reference.mdb for
argument's sake). The other backend would contain all the (temporary)
operational data (say data.mdb). Two backends seem desirable because from
time to time the lookup tables will change, (eg new postal code etc) and it
would be simpler just to replace the whole reference.mdb file when this
happens. The other advantage is that I can automatically compact the
data.mdb (I think) from the front end using JRO, each time the checking
program is run. It should be very quick, because it will just contain a
handful of empty tables.

However, all my code currently refers to the CurrentDb. Am I going to have
to go through and change every reference to the location of the other dbs?
Or does currentdb still work for linked tables?

Lastly JRO means another reference which brings me back to the question
posted earlier in this thread. What are the odds that other users will have
this reference installed, and if they do, how does Access cope if it is not
in the same location as mine?

Thanks again,

GPO

 

[Douglas J. Steele]

Why bother with JRO if you're running from within Access? You can compact
the back-end from the front-end using the CompactDatabase method of the
DBEngine. Yes, this requires you to have a reference to DAO, but if you're
using Access, you're guaranteed it's there.

If you're using linked tables, CurrentDb should still work. CurrentDb refers
to your front-end, which has a linked table object that works, for all
intent and purposes, the same as a table. One difference is that you can't
use the Seek method on a linked table (although there's a work-around in
http://www.mvps.org/access/tables/tbl0006.htm at "The Access Web")

If you're concerned about references, you can always use Late Binding, and
trap the error when you create the object. Check what Tony Toews has at
http://www.granite.ab.ca/access/latebinding.htm

 

[Larry Linson]

Recreating tables is a "no-brainer"... include a(n empty) "template table"
in your front end DB, create the temporary database, then use CopyObject to
make a copy of the "template tables" in the new temporary database...
creating all the tables and all their fields and setting their properties is
"too much like work".

Larry Linson
Microsoft Access MVP