none
SQL statement to find size of MS access database

    Question

  • Can you please tell me the SQL statement that you give me the size of MS access databse???
    Thursday, April 18, 2013 9:57 AM

Answers

  • As far as I know there is no built-in way to do that. You could create a custom VBA function and use that:

    Function GetFileSize() As Long
        GetFileSize = FileLen(CurrentDb.Name)
    End Function

    This function returns the size of the current database in bytes. To get kilobytes, divide by 1024, and to get megabytes, divide by (1024*1024).

    If you really want to do this in SQL:

    SELECT GetFileSize() AS FileSize


    Regards, Hans Vogelaar


    Thursday, April 18, 2013 10:22 AM

All replies

  • As far as I know there is no built-in way to do that. You could create a custom VBA function and use that:

    Function GetFileSize() As Long
        GetFileSize = FileLen(CurrentDb.Name)
    End Function

    This function returns the size of the current database in bytes. To get kilobytes, divide by 1024, and to get megabytes, divide by (1024*1024).

    If you really want to do this in SQL:

    SELECT GetFileSize() AS FileSize


    Regards, Hans Vogelaar


    Thursday, April 18, 2013 10:22 AM
  • What Hans proposed as solution works. However, to get the MB equivalent, you will have to divide the result by 1024 first and then another 1024 rto avoid Run-time error '6' [Overflow] message. Modifying Hans functon as follows:

    Function GetFileSize() As Long

    Dim FSize as Long

    FSize=FileLen(CurrentDb.Name)

    GetFileSize=FSize/1024/1024

    End Function


    New_Ibro

    Thursday, April 18, 2013 12:01 PM
  • Sir,

    this SQL query

    SELECT GetFileSize() AS FileSize

    doesn't run when I use this query in access....

    I need to know the size of Access Database through SQL Query..

    I need to make that SQL query from a JAVA application..

    Is there any way?

    Friday, April 19, 2013 2:20 PM
  • The code works for me within Access, provided that I have created the GetFileSize function in a VBA module.

    But it won't work in a Java application. Why do you want to use SQL for this - I'm sure Java must have other ways of finding the size of a file. I can't help you with that though.


    Regards, Hans Vogelaar

    Friday, April 19, 2013 2:47 PM
  • ok. thanks a lot Sir :)

    Friday, April 19, 2013 4:06 PM
  • I inserted/pasted the following into my form's VBA module, but how do I "call it up" on the form itself?

    Function GetFileSize() As Long

    Dim FSize as Long

    FSize=FileLen(CurrentDb.Name)

    GetFileSize=FSize/1024/1024

    End Function

    I used =GetFileSize() in a textbox's source, but I get nothing but Name?

    VWP1


    • Edited by VWP1 Monday, June 24, 2013 6:44 PM modify
    Monday, June 24, 2013 6:40 PM
  • Normally Functions like these are stored in a Standard Module not the Form's Module.

    Once you have the code in a standard module you call it from your Form or other object like this

    Private Sub YourCommandButtonHere_Click()

    GetFileSize

    End Sub


    Chris Ward

    Monday, June 24, 2013 6:54 PM
  • I would copy the function into a standard module (the kind you create by selecting Insert > Module in the Visual Basic Editor.

    Make sure that macros are enabled. The easiest way to do that is to make the folder containing the database a trusted location for Access.

    Instructions for Access 2007

    Instructions for Access 2010 and 2013


    Regards, Hans Vogelaar

    Monday, June 24, 2013 7:00 PM
  • Oops,

    You don't need the Private Sub.

    Just keep the formula you have in the Unbound Field.


    Chris Ward

    Monday, June 24, 2013 7:08 PM
  • Thank you.  It worked, but the textbox figure was rounded up; the file size is actually 34.5 MB, but the textbox reads 35 MB.  Is there any way to prevent this rounding?  I also added this:

    & ' MB'  so the formula is:  =GetFileSize() & ' MB'  and so I imagine I would have to do something like:

    =Round(GetFileSize(),1) & ' MB'  but that didn't work.

    VWP1

    Monday, June 24, 2013 9:25 PM
  • Change the return value of the function from a Long to a Double:

    Function GetFileSize() As Double
        GetFileSize = FileLen(CurrentDb.Name) / 1024 / 1024
    End Function

    This allows for decimals in the result.

    Regards, Hans Vogelaar

    Monday, June 24, 2013 9:41 PM
  • can I use the sql you just listed above and then the repair and compact automatically? How can I setup a filzesize and repair to run automatically when it is needed?
    Wednesday, November 29, 2017 3:41 AM
  • You can't use SQL to compact and repair a database.

    Compacting the active database using VBA code is tricky, since Access actually creates a new compacted database and then deletes the original one. As a result, code running in the original database instantly stops running...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 29, 2017 9:05 AM
  • So basically I need to run the vb.net code to check the size of the database, correct? Thus if the size is too big, then the vb.net code quit the connection to the access 2016 database and then  run the compact and repair? if not what should the order be and how would you setup the basic logic?
    Wednesday, November 29, 2017 3:55 PM
  • I can't help you with vb.net. Access VBA code would look like this:

    Sub CompactDb()
        Dim strName As String
        Dim strTemp As String
        strName = "C:\Access\MyDatabase.accdb"
        strTemp = "C:\Access\Temp.accdb"
        CompactDatabase strName, strTemp
        Kill strName
        Name strTemp As strName
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 29, 2017 5:33 PM
  • So basically I need to run the vb.net code to check the size of the database, correct? Thus if the size is too big, then the vb.net code quit the connection to the access 2016 database and then  run the compact and repair? if not what should the order be and how would you setup the basic logic?

    Hi midnight_car,

    I had once the situation that the Compact-action failed. If you have no recent backup then you are lost.

    Since then I always make a backup before compacting.

    Imb.

    Wednesday, November 29, 2017 5:58 PM