Can anyone tell me if this is possible and if so How? (1 Viewer)

rshaw

New member
Local time
Yesterday, 23:44
Joined
Feb 10, 2020
Messages
21
Hello everyone,

I was wondering if there was a way to place a text box bound to visual code that displays the current size of the database?

History I developed an application to perform daily tasks for construction projects. Recently while entering data I discovered that Access limits the file size to 2 Gigs which i discovered that i can not add any more data to that database now. How sad to have limits but such as it is. I think in the future if i can have a text box that displays the current database size i can solve the having to redo it by knowing when its full. Any Help is greatly appreciated.
 

rshaw

New member
Local time
Yesterday, 23:44
Joined
Feb 10, 2020
Messages
21
Maybe I should expand on this a bit to help. there are a few linked tables in this database but i would like to display the total database rather than individual table sizes.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:44
Joined
Oct 29, 2018
Messages
19,201
Hi. Not sure exactly what you are looking for. If the tables are linked, then they must be stored outside of the database, which means its size is separate from the first database.
 

rshaw

New member
Local time
Yesterday, 23:44
Joined
Feb 10, 2020
Messages
21
Hi. Not sure exactly what you are looking for. If the tables are linked, then they must be stored outside of the database, which means its size is separate from the first database.
thanks for the response I learned that Access caps the total size of the database to 2 gigs. is there a way to create a bit of code that displays the total size of that database? I did some searching and found some code that displays file size of table but not sure that will help much as there a a few tables linked that contribute to the total size of the database
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:44
Joined
Oct 29, 2018
Messages
19,201
thanks for the response I learned that Access caps the total size of the database to 2 gigs. is there a way to create a bit of code that displays the total size of that database? I did some searching and found some code that displays file size of table but not sure that will help much as there a a few tables linked that contribute to the total size of the database
Access limits the size of the database file, not the entire database system. So, displaying the file size should work for you. For example, if you had multiple files connected together as one database system/application, as long as each individual file size is less than 2GB, then the whole thing can be more than 2GB.
 

plog

Banishment Pending
Local time
Today, 00:44
Joined
May 11, 2011
Messages
11,020
This is possible with VBA, I am sure there are few people here who can give you specific code, I can only give you guidance.

You build a function:

Function get_FileSize()

In it you create a filesystem object (https://docs.microsoft.com/en-us/of...e/user-interface-help/filesystemobject-object). Point it towards your database and one of its properties is File Size. Return that value and you have what you need. You then set the source of your textbox to:

get_FileSize()
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:44
Joined
Feb 28, 2001
Messages
23,210
This is a two-part response.

FIRST, look up the FileSystemObject, which will tell you many things about the Windows file system - including properties of the files... properties like file size in bytes.

You can use CurrentDB.Name to give you the fully qualified name of the currently active database, which will be EITHER the entire file (for a single-file DB) or the front-end file if the DB has a front-end/back-end split. In the case of a split, find a table you know to be in the back-end and look at Tables(<<table-name>>).Connect (where you replace <<table-name>> with either the select table name in quotes, as a literal name, or a string variable holding the table name). FSO will give you in-use sizes based on the file <EOF> position.

SECOND, that information might be misleading, because the size of the file will be that of the last time it was saved. So after a big operation that appends a lot of new data, the size number you get for the saved version might be smaller than the real version, and that won't be updated until you close the app. There is also the issue that the saved file size might include "bloat" which is an insidious but very common condition in which the database is bigger than it should be due to the nature of dynamic memory allocation.

The only time the database size is actually accurate is immediately after you have saved the file and performed a Compact & Repair operation. If you have a split DB, that has to be a C&R on BOTH parts of the DB - front-end and back-end.

By the way - if doing a C&R on your file, ALWAYS make a backup copy first, just in case things get out of hand.
 

rshaw

New member
Local time
Yesterday, 23:44
Joined
Feb 10, 2020
Messages
21
Thank you everyone I will continue to look into this have either of you ran into any samples to perform this?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:44
Joined
Feb 19, 2002
Messages
36,961
You need to pass in the file name with the full path. I use it in the FE to watch the size of the BE. The commented code shows other options for the return.

It also requires a reference to the Microsoft Scripting Runtime

Code:
Function ShowFileAccessInfo(filespec)

    'requires a reference to Microsoft Scripting Runtime
 
    Dim FSO As New FileSystemObject
    Dim f As File
    Dim s As String
    Dim FileSize As Long
    Dim sFileSize As String
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set f = FSO.GetFile(filespec)
    FileSize = f.Size / 1000
 
    sFileSize = Format(FileSize, "###,###,###") & " KB"
 
    If FileSize > 1000000 Then
        sFileSize = sFileSize & " COMPACT NOW!!!"
    End If
    s = sFileSize
                             ''''   s = f.Path & "<br>"
                             ''''   s = s & "Created: " & f.DateCreated & "<br>"
                             ''''   s = s & "Last Accessed: " & f.DateLastAccessed & "<br>"
                             ''''   s = s & "Last Modified: " & f.DateLastModified
    ShowFileAccessInfo = s
End Function
 

rshaw

New member
Local time
Yesterday, 23:44
Joined
Feb 10, 2020
Messages
21
Awesome thank you all who took the time to respond think i can get it from here. Have a Great Weekend !!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:44
Joined
Feb 19, 2002
Messages
36,961
You're welcome. I had to build this for a client a few years ago because he was really bad about compacting:(
 

sxschech

Registered User.
Local time
Yesterday, 22:44
Joined
Mar 2, 2010
Messages
754
Another way of going about it. I put a text box on the form and then have a color code depending on the file size going from Green to Red and then after a particular size if user still hasn't compacted, then displays a message requesting to compact. Uses a vba function FileLen instead of scripting. This code checks size of the currentdb and one external file that is linked to it. You would need to edit accordingly.

Code:
Sub ProjectSize()
'Display size in kb of this database and the project database
'to give a warning if getting close to the 2 gig limit
'20150703
    Me.txtLocalSize = FileLen(CurrentDb.Name)
    Call ProjectWarning(Me.txtLocalSize, "txtLocalSize")
    If Dir(Me.txtProject) <> "" Then
        Me.txtProjectSize = FileLen(Me.txtProject)
        Call ProjectWarning(Me.txtProjectSize, "txtProjectSize")
    End If
End Sub

Sub ProjectWarning(stFileSize As Long, stControlName As String)
    Me.txtProjectSizeNote = ""
    Me.txtFileName.Visible = True
    Select Case stFileSize
        Case Is < 1000000000
            Form.Controls(stControlName).ForeColor = RGB(86, 161, 72)
            Form.Controls(stControlName).FontBold = False
            Form.Controls(stControlName).FontItalic = False
            Form.Controls(stControlName).BackStyle = Transparent
            Me.txtProjectSizeNote.Visible = False
        Case Is < 1500000000
            Form.Controls(stControlName).ForeColor = RGB(255, 162, 0)
            Form.Controls(stControlName).FontBold = True
        Case Is < 1750000000
            Form.Controls(stControlName).ForeColor = vbRed
        Case Else
            Form.Controls(stControlName).ForeColor = vbRed
            Form.Controls(stControlName).FontBold = True
            Form.Controls(stControlName).FontItalic = True
            Form.Controls(stControlName).Visible = True
            Me.txtProjectSizeNote = "Approaching 2 gig limit.  " & _
                                    "Please compact and repair the databases, " & _
                                    "create a new project or delete unneeded tables."
            Me.txtFileName.Visible = False
            Me.txtProjectSizeNote.Visible = True
    End Select
End Sub
 

rshaw

New member
Local time
Yesterday, 23:44
Joined
Feb 10, 2020
Messages
21
Another way of going about it. I put a text box on the form and then have a color code depending on the file size going from Green to Red and then after a particular size if user still hasn't compacted, then displays a message requesting to compact. Uses a vba function FileLen instead of scripting. This code checks size of the currentdb and one external file that is linked to it. You would need to edit accordingly.

Code:
Sub ProjectSize()
'Display size in kb of this database and the project database
'to give a warning if getting close to the 2 gig limit
'20150703
    Me.txtLocalSize = FileLen(CurrentDb.Name)
    Call ProjectWarning(Me.txtLocalSize, "txtLocalSize")
    If Dir(Me.txtProject) <> "" Then
        Me.txtProjectSize = FileLen(Me.txtProject)
        Call ProjectWarning(Me.txtProjectSize, "txtProjectSize")
    End If
End Sub

Sub ProjectWarning(stFileSize As Long, stControlName As String)
    Me.txtProjectSizeNote = ""
    Me.txtFileName.Visible = True
    Select Case stFileSize
        Case Is < 1000000000
            Form.Controls(stControlName).ForeColor = RGB(86, 161, 72)
            Form.Controls(stControlName).FontBold = False
            Form.Controls(stControlName).FontItalic = False
            Form.Controls(stControlName).BackStyle = Transparent
            Me.txtProjectSizeNote.Visible = False
        Case Is < 1500000000
            Form.Controls(stControlName).ForeColor = RGB(255, 162, 0)
            Form.Controls(stControlName).FontBold = True
        Case Is < 1750000000
            Form.Controls(stControlName).ForeColor = vbRed
        Case Else
            Form.Controls(stControlName).ForeColor = vbRed
            Form.Controls(stControlName).FontBold = True
            Form.Controls(stControlName).FontItalic = True
            Form.Controls(stControlName).Visible = True
            Me.txtProjectSizeNote = "Approaching 2 gig limit.  " & _
                                    "Please compact and repair the databases, " & _
                                    "create a new project or delete unneeded tables."
            Me.txtFileName.Visible = False
            Me.txtProjectSizeNote.Visible = True
    End Select
End Sub
I like this one but cant seem to make it work. Any additional information you can provide? I hadnt had much luck I keep getting !Name? errors in the textbox instead of the info i need. I know i must be doing something wrong. Or I get a Blank text Box with nothing in it.
 
Last edited:

rshaw

New member
Local time
Yesterday, 23:44
Joined
Feb 10, 2020
Messages
21
Can't you just use the FileLen function bound to a text box if that is the case, I am sure you have to use the full file path to get it to populate that field, correct?
 

sxschech

Registered User.
Local time
Yesterday, 22:44
Joined
Mar 2, 2010
Messages
754
Here is a stripped down ugly example. I hard coded some values in the file name text boxes, so the code would work. However, those would be changed to unbound text boxes and the names would be passed to them from your actual files.
 

Attachments

  • FileSizeTest.accdb
    512 KB · Views: 10

rshaw

New member
Local time
Yesterday, 23:44
Joined
Feb 10, 2020
Messages
21
Here is a stripped down ugly example. I hard coded some values in the file name text boxes, so the code would work. However, those would be changed to unbound text boxes and the names would be passed to them from your actual files.
Okay so let me give you an example that may help me understand and allow you to explain ( Thank you so much for your time) I had gotten tired of finding out at data entry that the max size had been passed and would not allow me to save additional records.

In my case the full path to the file name is : D:/WORK STUFF/ Daily_Reports_2022-2.accde

In theory could that filename be hardcoded somewhere invisible where it would reference it without giving the name up? Would i need the multiple text boxes like in your example? I only want to track the one database file.
 

sxschech

Registered User.
Local time
Yesterday, 22:44
Joined
Mar 2, 2010
Messages
754
I can put another example together tomorrow. You don't need multiple text boxes. My example had it because I copied from my database where I purposely had the files displayed so that the user would know which database was affected. Since your case doesn't seem to need that information, it would not be necessary. If you hard code the name, then that would imply that you don't plan to move, rename or use a different file, correct?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:44
Joined
Feb 28, 2001
Messages
23,210
If that file (Daily_Reports_etc.) is used as a linked table, you already have it in your Front-End DB in the form of the table's .Connect string that contains the full file spec. Unfortunately, unless you have taken pains to secure the DB to prevent people from browsing around in it, that file name is easily visible as part of connection properties between the front-end interfacing stuff and the back-end tables. Access security isn't the greatest in the world because it was designed as a small-system application for small shops that don't need and don't use heavy security. So the only thing to realize is that you can make it harder but it would be unlikely that you can completely secure it to prevent discovery of that file name.
 

sxschech

Registered User.
Local time
Yesterday, 22:44
Joined
Mar 2, 2010
Messages
754
Ok, here is a version with one text box to show the file size and a textbox for the message. If that still isn't what you wanted and maybe you were looking for a messagebox instead, then you can change the textboxcode to a messagebox. I hardcoded the filename and removed the space as I assume that was a typo. So if the name is not correct, when you open the form there will be an error message. The new form is called frmFileSizeHardCode
 

Attachments

  • FileSizeTest.accdb
    632 KB · Views: 9

Users who are viewing this thread

Top Bottom