Keeping track of size of database

[Bill]

Using A2000
I am using mini-databases residing on floppy disks for writing school
reports which can then be sent to a central db for electronic
collating. Occasionally the database has grown past the 1.38Mb
possible on disk and this has crashed the whole database into an un
recoverable state, losing data if not backed up. Is there access to
the size of the database within the db as a VB function so that I can
trigger a warning when moving from record to record if the db is
getting near the limit?

Thanks

Bill

[Trevor Best]

On 7 Jan 2004 13:50:07 -0800 in comp.databases.ms-access,
bi**@piperb.freeserve.co.uk (Bill) wrote:

Using A2000
I am using mini-databases residing on floppy disks for writing school
reports which can then be sent to a central db for electronic
collating. Occasionally the database has grown past the 1.38Mb
possible on disk and this has crashed the whole database into an un
recoverable state, losing data if not backed up. Is there access to
the size of the database within the db as a VB function so that I can
trigger a warning when moving from record to record if the db is
getting near the limit?

FileLen(CurrentDb.Name)

--
A)bort, R)etry, I)nfluence with large hammer.

[Tony Toews]

Trevor Best <bouncer@localhost> wrote:

FileLen(CurrentDb.Name)

The best kind of answer. Succinct.

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

[PC Datasheet]

FileLen gives the size of the file before it was opened - this is not what the
poster asked for. FileLen does not include the additional bytes added to the
file in the open session. The poster needs the LOF function.

From the Help file ---

Remarks

If the specified file is open when the FileLen function is called, the value
returned represents the size of the file immediately before it was opened.

Note To obtain the length of an open file, use the LOF function.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Trevor Best" <bouncer@localhost> wrote in message
news:4k********************************@4ax.com...

On 7 Jan 2004 13:50:07 -0800 in comp.databases.ms-access,
bi**@piperb.freeserve.co.uk (Bill) wrote:

Using A2000
I am using mini-databases residing on floppy disks for writing school
reports which can then be sent to a central db for electronic
collating. Occasionally the database has grown past the 1.38Mb
possible on disk and this has crashed the whole database into an un
recoverable state, losing data if not backed up. Is there access to
the size of the database within the db as a VB function so that I can
trigger a warning when moving from record to record if the db is
getting near the limit?

FileLen(CurrentDb.Name)

--
A)bort, R)etry, I)nfluence with large hammer.

[Trevor Best]

On Wed, 07 Jan 2004 23:42:03 GMT in comp.databases.ms-access, "PC
Datasheet" <sp**@nospam.spam> wrote:

FileLen gives the size of the file before it was opened - this is not what the
poster asked for. FileLen does not include the additional bytes added to the
file in the open session. The poster needs the LOF function.

From the Help file ---

Remarks

If the specified file is open when the FileLen function is called, the value
returned represents the size of the file immediately before it was opened.

Note To obtain the length of an open file, use the LOF function.

Well it doesn't do what it says on the tin because it works for me.

?filelen(CurrentDb.Name)
909312
docmd.CopyObject CurrentDb.Name,"test1",acTable,"Table1"
?filelen(CurrentDb.Name)
925696

--
A)bort, R)etry, I)nfluence with large hammer.

[Trevor Best]

On Wed, 07 Jan 2004 23:20:35 GMT in comp.databases.ms-access, Tony
Toews <tt****@telusplanet.net> wrote:

Trevor Best <bouncer@localhost> wrote:

FileLen(CurrentDb.Name)

The best kind of answer. Succinct.

I thought my internet connection was going to drop any moment :-)

--
A)bort, R)etry, I)nfluence with large hammer.

[Trevor Best]

On Thu, 08 Jan 2004 07:56:56 +0000 in comp.databases.ms-access, Trevor
Best <bouncer@localhost> wrote:

On Wed, 07 Jan 2004 23:42:03 GMT in comp.databases.ms-access, "PC
Datasheet" <sp**@nospam.spam> wrote:

FileLen gives the size of the file before it was opened - this is not what the
poster asked for. FileLen does not include the additional bytes added to the
file in the open session. The poster needs the LOF function.

From the Help file ---

Remarks

If the specified file is open when the FileLen function is called, the value
returned represents the size of the file immediately before it was opened.

Note To obtain the length of an open file, use the LOF function.

Well it doesn't do what it says on the tin because it works for me.

?filelen(CurrentDb.Name)
909312
docmd.CopyObject CurrentDb.Name,"test1",acTable,"Table1"
?filelen(CurrentDb.Name)
925696

Works as advertised if you open the file yourself:

Function testfilelen()
Const cstrFile As String = "test.tmp"
Open cstrFile For Output As #1
Debug.Print FileLen(cstrFile), LOF(1)
Print #1, String(1024, "x")
Debug.Print FileLen(cstrFile), LOF(1)
Print #1, String(1024, "x")
Debug.Print FileLen(cstrFile), LOF(1)
Print #1, String(1024, "x")
Debug.Print FileLen(cstrFile), LOF(1)
Close #1
End Function

0 0
0 1026
0 2052
0 3078

BTW, if anyone wants to open the current database and check LOF() then
open it for input (or binary), not output, that will fail, it will
also fail if the database is opened exclusively.

e.g.
Open CurrentDb.Name For Binary Access Read Shared As #1

--
A)bort, R)etry, I)nfluence with large hammer.

[Bill]

Thanks for all the help - will try it out.

Bill

Trevor Best <bouncer@localhost> wrote in message news:<b2********************************@4ax.com>. ..

On Thu, 08 Jan 2004 07:56:56 +0000 in comp.databases.ms-access, Trevor
Best <bouncer@localhost> wrote:

On Wed, 07 Jan 2004 23:42:03 GMT in comp.databases.ms-access, "PC
Datasheet" <sp**@nospam.spam> wrote:

FileLen gives the size of the file before it was opened - this is not what the
poster asked for. FileLen does not include the additional bytes added to the
file in the open session. The poster needs the LOF function.

From the Help file ---

Remarks

If the specified file is open when the FileLen function is called, the value
returned represents the size of the file immediately before it was opened.

Note To obtain the length of an open file, use the LOF function.

Well it doesn't do what it says on the tin because it works for me.

?filelen(CurrentDb.Name)
909312
docmd.CopyObject CurrentDb.Name,"test1",acTable,"Table1"
?filelen(CurrentDb.Name)
925696

Works as advertised if you open the file yourself:

Function testfilelen()
Const cstrFile As String = "test.tmp"
Open cstrFile For Output As #1
Debug.Print FileLen(cstrFile), LOF(1)
Print #1, String(1024, "x")
Debug.Print FileLen(cstrFile), LOF(1)
Print #1, String(1024, "x")
Debug.Print FileLen(cstrFile), LOF(1)
Print #1, String(1024, "x")
Debug.Print FileLen(cstrFile), LOF(1)
Close #1
End Function

0 0
0 1026
0 2052
0 3078

BTW, if anyone wants to open the current database and check LOF() then
open it for input (or binary), not output, that will fail, it will
also fail if the database is opened exclusively.

e.g.
Open CurrentDb.Name For Binary Access Read Shared As #1

[Bill]

Tried but failed!
My database is called remote reports.mdb
I am working within that on a form and have tried to use
filelen("remote reports.mdb") as the control source for a textbox so
that it will display the current size of the db but this results in
#Name? in the textbox. Have I got the wrong end of the stick? If I try
using LOF(n) what do I put for the n if I want to refer to the db
itself?

Have also tried coding behind the form in VBA but not getting
anywhere.

Sorry to be such a durr brain.

Bill
bi**@piperb.freeserve.co.uk (Bill) wrote in message news:<aa*************************@posting.google.c om>...

Thanks for all the help - will try it out.

Bill

Trevor Best <bouncer@localhost> wrote in message news:<b2********************************@4ax.com>. ..

On Thu, 08 Jan 2004 07:56:56 +0000 in comp.databases.ms-access, Trevor
Best <bouncer@localhost> wrote:

On Wed, 07 Jan 2004 23:42:03 GMT in comp.databases.ms-access, "PC
Datasheet" <sp**@nospam.spam> wrote:

>FileLen gives the size of the file before it was opened - this is not what the
>poster asked for. FileLen does not include the additional bytes added to the
>file in the open session. The poster needs the LOF function.
>
>From the Help file ---
>
>Remarks
>
>If the specified file is open when the FileLen function is called, the value
>returned represents the size of the file immediately before it was opened.
>
>Note To obtain the length of an open file, use the LOF function.

Well it doesn't do what it says on the tin because it works for me.

?filelen(CurrentDb.Name)
909312
docmd.CopyObject CurrentDb.Name,"test1",acTable,"Table1"
?filelen(CurrentDb.Name)
925696

Works as advertised if you open the file yourself:

Function testfilelen()
Const cstrFile As String = "test.tmp"
Open cstrFile For Output As #1
Debug.Print FileLen(cstrFile), LOF(1)
Print #1, String(1024, "x")
Debug.Print FileLen(cstrFile), LOF(1)
Print #1, String(1024, "x")
Debug.Print FileLen(cstrFile), LOF(1)
Print #1, String(1024, "x")
Debug.Print FileLen(cstrFile), LOF(1)
Close #1
End Function

0 0
0 1026
0 2052
0 3078

BTW, if anyone wants to open the current database and check LOF() then
open it for input (or binary), not output, that will fail, it will
also fail if the database is opened exclusively.

e.g.
Open CurrentDb.Name For Binary Access Read Shared As #1

[TC]

On the previously stated principle of succinctness, your answer surely
should have been:

LOF

:-)
TC
"PC Datasheet" <sp**@nospam.spam> wrote in message
news:fP*******************@newsread3.news.atl.eart hlink.net...

FileLen gives the size of the file before it was opened - this is not what the poster asked for. FileLen does not include the additional bytes added to the file in the open session. The poster needs the LOF function.

From the Help file ---

Remarks

If the specified file is open when the FileLen function is called, the value returned represents the size of the file immediately before it was opened.

Note To obtain the length of an open file, use the LOF function.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Trevor Best" <bouncer@localhost> wrote in message
news:4k********************************@4ax.com...

On 7 Jan 2004 13:50:07 -0800 in comp.databases.ms-access,
bi**@piperb.freeserve.co.uk (Bill) wrote:

Using A2000
I am using mini-databases residing on floppy disks for writing school
reports which can then be sent to a central db for electronic
collating. Occasionally the database has grown past the 1.38Mb
possible on disk and this has crashed the whole database into an un
recoverable state, losing data if not backed up. Is there access to
the size of the database within the db as a VB function so that I can
trigger a warning when moving from record to record if the db is
getting near the limit?

FileLen(CurrentDb.Name)

--
A)bort, R)etry, I)nfluence with large hammer.

[Trevor Best]

On 8 Jan 2004 15:17:42 -0800 in comp.databases.ms-access,
bi**@piperb.freeserve.co.uk (Bill) wrote:

Tried but failed!
My database is called remote reports.mdb
I am working within that on a form and have tried to use
filelen("remote reports.mdb") as the control source for a textbox so
that it will display the current size of the db but this results in
#Name? in the textbox. Have I got the wrong end of the stick?
Did you put the equals sign in the control source, e.g.

=filelen(...

mind you I wouldn't bank on the function running from the same
directory that the database is in, you might need to code in an
absolute path but then that wouldn't work if you moved the database..
If I try
using LOF(n) what do I put for the n if I want to refer to the db
itself?
You'd need to open the database using the "Open" statement, as I said
before this will fail if you have the database opened exclusively, so
I wouldn't bother with that.
Have also tried coding behind the form in VBA but not getting
anywhere.

Try setting setting the controlsource to:

=DbSize()

Then in a public module:

public function DbSize()
DbSize=filelen(currentdb.name)
end function
--
A)bort, R)etry, I)nfluence with large hammer.

[Squirrel]

Suggest do not use this code:
filelen("remote reports.mdb")

Use this statement instead:
filelen(CurrentDb.Name)

See if that will work.
"Bill" <bi**@piperb.freeserve.co.uk> wrote in message
news:aa**************************@posting.google.c om...

Tried but failed!
My database is called remote reports.mdb
I am working within that on a form and have tried to use
filelen("remote reports.mdb") as the control source for a textbox so
that it will display the current size of the db but this results in
#Name? in the textbox. Have I got the wrong end of the stick? If I try
using LOF(n) what do I put for the n if I want to refer to the db
itself?

Have also tried coding behind the form in VBA but not getting
anywhere.

Sorry to be such a durr brain.

Bill
bi**@piperb.freeserve.co.uk (Bill) wrote in message

news:<aa*************************@posting.google.c om>...

Thanks for all the help - will try it out.

Bill

Trevor Best <bouncer@localhost> wrote in message news:<b2********************************@4ax.com>. ..

On Thu, 08 Jan 2004 07:56:56 +0000 in comp.databases.ms-access, Trevor
Best <bouncer@localhost> wrote:

>On Wed, 07 Jan 2004 23:42:03 GMT in comp.databases.ms-access, "PC
>Datasheet" <sp**@nospam.spam> wrote:
>
>>FileLen gives the size of the file before it was opened - this is not what the >>poster asked for. FileLen does not include the additional bytes added to the >>file in the open session. The poster needs the LOF function.
>>
>>From the Help file ---
>>
>>Remarks
>>
>>If the specified file is open when the FileLen function is called, the value >>returned represents the size of the file immediately before it was opened. >>
>>Note To obtain the length of an open file, use the LOF function.
>
>Well it doesn't do what it says on the tin because it works for me.
>
>?filelen(CurrentDb.Name)
> 909312
>docmd.CopyObject CurrentDb.Name,"test1",acTable,"Table1"
>?filelen(CurrentDb.Name)
> 925696

Works as advertised if you open the file yourself:

Function testfilelen()
Const cstrFile As String = "test.tmp"
Open cstrFile For Output As #1
Debug.Print FileLen(cstrFile), LOF(1)
Print #1, String(1024, "x")
Debug.Print FileLen(cstrFile), LOF(1)
Print #1, String(1024, "x")
Debug.Print FileLen(cstrFile), LOF(1)
Print #1, String(1024, "x")
Debug.Print FileLen(cstrFile), LOF(1)
Close #1
End Function

0 0
0 1026
0 2052
0 3078

BTW, if anyone wants to open the current database and check LOF() then
open it for input (or binary), not output, that will fail, it will
also fail if the database is opened exclusively.

e.g.
Open CurrentDb.Name For Binary Access Read Shared As #1

[Bill]

Hoorah!

Thanks very much - this has worked and I can now flash up a warning
msgbox if the size gets critical

Another happy customer.

Bill

Trevor Best <bouncer@localhost> wrote in message news:<ve********************************@4ax.com>. ..

On 8 Jan 2004 15:17:42 -0800 in comp.databases.ms-access,
bi**@piperb.freeserve.co.uk (Bill) wrote:

Tried but failed!
My database is called remote reports.mdb
I am working within that on a form and have tried to use
filelen("remote reports.mdb") as the control source for a textbox so
that it will display the current size of the db but this results in
#Name? in the textbox. Have I got the wrong end of the stick?

Did you put the equals sign in the control source, e.g.

=filelen(...

mind you I wouldn't bank on the function running from the same
directory that the database is in, you might need to code in an
absolute path but then that wouldn't work if you moved the database..

If I try
using LOF(n) what do I put for the n if I want to refer to the db
itself?

You'd need to open the database using the "Open" statement, as I said
before this will fail if you have the database opened exclusively, so
I wouldn't bother with that.

Have also tried coding behind the form in VBA but not getting
anywhere.

Try setting setting the controlsource to:

=DbSize()

Then in a public module:

public function DbSize()
DbSize=filelen(currentdb.name)
end function