capturing the size of the mdb file with VBA

[Paul]

Is there a way to capture the size of the current mdb file in VBA? That is,
the Size that's displayed in the Database Properties window that appears
when you right-click on the database window title bar (in Access 2003).

Thanks in advance,

Paul

 

[Daniel Pineault]

You could use a function such as:

'---------------------------------------------------------------------------------------
' Procedure : GetFileSize
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Determine the size of a specified files (in Bytes)
' Copyright : The following may be altered and reused as you wish so long as
the
' copyright notice is left unchanged (including Author, Website
and
' Copyright).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strFile Path & Full Filename of the file to retrieve the file info about
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' GetFileSize("C:\Temp\Dataset.xls")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2008-Oct-09 Initial Release
'---------------------------------------------------------------------------------------
Function GetFileSize(strFile As String)
Dim oFSO As Object
Dim oF As Object

On Error GoTo Error_Handler

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oF = oFSO.GetFile(strFile)

GetFileSize = oF.Size

Set oF = Nothing
Set oFSO = Nothing

Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: GetFileSize" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Exit Function
End Function

and use it like:
GetFileSize(application.CurrentDb.Name)
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Stuart McCall]

Is there a way to capture the size of the current mdb file in VBA? That
is, the Size that's displayed in the Database Properties window that
appears when you right-click on the database window title bar (in Access
2003).

Thanks in advance,

Paul

Debug.Print FileLen(CurrentDb.Name)

 

[Jack Leach]

Debug.Print FileLen(CurrentDb.Name)

hey thats pretty cool. New one for me.

 

[Stuart McCall]

hey thats pretty cool. New one for me.

Jack

Well it's been in MS's BASIC language, which VBA evolved from, since way
before Access, but seems to be largely forgotten in these days of fso (which
I dislike).

Have you spotted GetAttr and SetAttr? Also v useful at times.

 

[Jack Leach]

I have seen and used SetAttr and GetAttr

I've actually never used fso before... it's always seemed like a roundabout
way to do things to me when VBA offers a few basic functions (and a few APIs)
to handle the core and you can custom code the rest without having to worry
about it breaking on a version change.

I guess I never had to check a file's size before, therefore never googled
it, else I probably would have found the function. But then, that's a large
part of why I'm here!

Thanks,

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Paul]

Nice function, Daniel. I hadn't seen it before, so thanks for sharing it.

I use the File System Object extensively for copying, moving, renaming, and
deleting files and directories, so this will fit in nicely with what I'm
doing.

I like to display file sizes in megs with one decimal point, so I added a
format function:

GetFileSize = Format(oF.size / 1000000, "#,###.#")

Best regards,

Paul

 

[Paul]

Nice shortcut to display the file size, Stuart. Thanks for sending it
along.

While the File System Object in some cases may seem like having one guy hold
the light bulb while two others spin the ladder around, I use it extensively
for moving, copying and renaming files and folders becuase I've found that
the simpler commands like FileCopy Source Target don't always work. I've
never figured out exactly what causes it not to work, but I suspect it has
something to do with the permissions on the network folders. Whereas the
FSO commands seem to work all the time. So rather than experiment with each
situation, I've just taken to using the FSO.

In case anyone is interested, here is a cheat sheet I compiled for the FSO
commands where sourceFile and targetFile are strings consisting of the full
path and filename. This list demonstrates how to:

'copy a single file,
'copy all files in a folder,
'create a folder,
'copy a folder and its contents
'confirm a file exists
'confirm a folder exists
'rename a file
'rename a folder
'delete a file
'delete all files in a folder
'move a file

'copy a file
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile sourceFile, targetFile
Set fso = Nothing

'another way to copy one file
CreateObject("Scripting.FileSystemObject").CopyFile sourceFile, targetFile
'to copy all files in a folder
'Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile "C:\MyFolder\*", "C:\targetFolder\"
Set fso = Nothing

'create a folder
MkDir "c:\eraseme"

'copy a folder and its contents - backslashes must be exactly as shown
'Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFolder "M:\sourceFolder", "M:\targetFolder"
Set fso = Nothing

'confirm a File exists
'Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("M:\myFile") Then MsgBox "yes"
Set fso = Nothing

'confirm a Folder exists
'Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FolderExists("M:\myFolder") Then MsgBox "yes"
Set fso = Nothing

'rename a file, use the MoveFile method
'Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
fso.MoveFile "M:\MyFile_A", "M:\MyFile_B"
Set fso = Nothing

'rename a folder, use the MoveFolder method
'Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
fso.MoveFolder sourceFolder, targetFolder
Set fso = Nothing

'delete a file
Kill "C:\temp2\file1.xls"

'delete all files in a folder
Kill "C:\temp2\*.*"

'move a file
'Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
fso.MoveFile "c:\evision\eraseme.txt", "p:\backup\eraseme.txt"
Set fso = Nothing

 

[Stuart McCall]

Nice shortcut to display the file size, Stuart. Thanks for sending it
along.

While the File System Object in some cases may seem like having one guy
hold the light bulb while two others spin the ladder around, I use it
extensively for moving, copying and renaming files and folders becuase
I've found that the simpler commands like FileCopy Source Target don't
always work. I've never figured out exactly what causes it not to work,
but I suspect it has something to do with the permissions on the network
folders. Whereas the FSO commands seem to work all the time. So rather
than experiment with each situation, I've just taken to using the FSO.

<snip>

You're welcome. Well one of the reasons I don't use fso is that if the
sysadmin has disabled scripting, the fso is non-existant AFAICT. Also if
you're correct re the folder permissions thing, I *certainly* won't use
something which can circumvent it. Folder permissions are imposed for a good
reason (usually
Personally I've never known FileCopy to fail if the arguments you supply are
correct. Mind you, if there are hundreds of files to be processed, or the
file sizes are huge, I prefer to use the built-in "Flying paper dialogs" :

http://www.smccall.demon.co.uk/Downloads.htm#FileSys

That way the user can have some feedback on the running process, which you
can't do with either FileCopy or fso (AFAIK).

 

[Paul]

Stuart,

No problem with sysadmin people because we work closely together.

Where have you been hiding that great Web page? You've got a number of
things in there that I can use in addition to the file system modules - the
SQL formatting function, the progress bar subform, the "busy" form and the
one that lets Access accept file paths from Win Explorer.

Could I get you to say a few words about how that last item could be used?

It's of special interest to me because my current project provides users a
way to click on a command in a custom shortcut submenu menu to open a
document from a template folder, populate the known fields with data from
the current record and save the document to a specific project folder.
Previously, people had to open the files from the template folder, select
File-Save As and navigate to the specific project folder and populate each
individual field from the keyboard, repeating the same data over and over
again in multiple forms. (We have conservatively projected that this group
of 100 people will be saving the time equivalent of over $500,000 a year
just by this one process alone, because they need to complete tens of
documents for each project).

While this process populates and saves documents from template folders to
the project folders, when the users want to copy files from other places on
the network into their project folders, they have to open Win Ex and
manually drag them to the destination. Would I be able to use your applet
to streamline that process as well?

Thanks for sharing all the helpful information,

Paul

 

[Stuart McCall]

Comments inline:

Stuart,

No problem with sysadmin people because we work closely together.

Where have you been hiding that great Web page? You've got a number of
things in there that I can use in addition to the file system modules -
the SQL formatting function, the progress bar subform, the "busy" form
and the one that lets Access accept file paths from Win Explorer.

Great. I hope they serve you well. Any problems, you know where I am.

Could I get you to say a few words about how that last item could be used?

It's of special interest to me because my current project provides users a
way to click on a command in a custom shortcut submenu menu to open a
document from a template folder, populate the known fields with data from
the current record and save the document to a specific project folder.
Previously, people had to open the files from the template folder, select
File-Save As and navigate to the specific project folder and populate each
individual field from the keyboard, repeating the same data over and over
again in multiple forms. (We have conservatively projected that this group
of 100 people will be saving the time equivalent of over $500,000 a year
just by this one process alone, because they need to complete tens of
documents for each project).

While this process populates and saves documents from template folders to
the project folders, when the users want to copy files from other places
on the network into their project folders, they have to open Win Ex and
manually drag them to the destination. Would I be able to use your applet
to streamline that process as well?

It doesn't sound like it, no. The only purpose it serves is to supply a list
of selected files and/or folders selected in Explorer to the running
application. It's up to the developer to decide what to do with the list
from that point. You could store them in a table, do some file processing of
some sort using the list directly. etc. etc.

But from your description, your users are already dragging files around, so
I can't see an advantage.

Thanks for sharing all the helpful information,

You're welcome. Thanks for the positive comments re my site. I appreciate
it.

 

[Paul]

Stuart, after reading your last message, your app sounds even more
interesting.

If if supplies a list of files or folders selected in Explorer to the
running application which can be stored in a table, then it would seem those
same file or folder names could be supplied to a procedure that copies or
moves those same files or folders, which is exactly what I'm trying to do.

Am I right in thinking your app could be used in that way?

Also, if more than one instance of Win Ex is open, is it able to recognize
which one was last active, and look to that one for the selected files(s) or
folder(s)?

Paul

 

[Stuart McCall]

Stuart, after reading your last message, your app sounds even more
interesting.

If if supplies a list of files or folders selected in Explorer to the
running application which can be stored in a table, then it would seem
those same file or folder names could be supplied to a procedure that
copies or moves those same files or folders, which is exactly what I'm
trying to do.

Am I right in thinking your app could be used in that way?

Well yes it could be a 'man-in-the-middle' for such an operation, but why
would you need Access to do the job Explorer can do anyway?
Also your file locations would need to remain the same as when you stored
them (or your app would have to cover that by error trapping)

Also, if more than one instance of Win Ex is open, is it able to recognize
which one was last active, and look to that one for the selected files(s)
or folder(s)?

The file/folder list supplied by windows will always have come from the
'last active' Explorer. You have no choice in the matter. Think of it as a
copy & paste operation.
Study the code and comments in the code behind the form sfrmDragDrop. Things
should become clear(er).

 

[Paul]

Well yes it could be a 'man-in-the-middle' for such an operation, but why

would you need Access to do the job Explorer can do anyway?

This part of my application is all about saving time. To do the job in
Explorer, the user has to first navigate to the source file or folder, then
navigate to the target folder in order to do a copy and paste or drag and
drop. Since the database knows where the target folder is by virtue of the
data in a field, the user would only have to navigate to the source file or
folder, then click a command in the form to have the source object moved or
copied to the destination, thereby eliminating the need to navigate to the
target folder. True it only takes a few seconds, but when you have many
people doing it many times, the time savings can become significant.

Also your file locations would need to remain the same as when you stored
them (or your app would have to cover that by error trapping)

Again, our files are organized by protocols related to known data in fields
like Company, City and ProjectID, so we always know the exact location of
the target folder.

Study the code and comments in the code behind the form sfrmDragDrop.
Things should become clear(er).

Will do.

Thanks for your help in going over this, Stuart.

P