Check Database Size then repair and compact over a size

[ideafixer]

I want to check the size of my database then, it is over let's say 20 Megs, automatically compact and repair the database. I know it must happen as an auto exec. I need to make sure I do not get stuck in a loop if the actual database size gets over 20 Meg. I am looking for a place to start adding this functionality to my database.

Thanks

 

[danvlas]

To get the size of the current database, use:

FileLen(CurrentDb.Name)

Store it somehow somewhere and check the difference between two subsequent openings of the file.

CurrentDB is DAO specific, I'm not sure about doing it in ADO.

However, to get the size of the database without using CurrentDb:

Open "C:\Path\File.mdb" For Input As #1
TheSize = LOF(1)
Close #1


HTH


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[gerard1979]

It's funny, I'm just looking for the same problem at the same time. I give u my start so we can think together:
'Use the form timer and look over the filesize
'If filesize bigger then a value then look for other users and log them off. It's just in a very low stage but I get a good look next week no more time now. Succes!Gerard
'Private Sub Form_Timer()
'Dim strFileName As String, tempstrFileName As String
'strFileName = "H:\Gerard\Acquisitie_be.mdb"
'tempstrFileName = "H:\Gerard\Acquisitie_bek.mdb"
'If TrackFileSize > 6 Then
' If TrackUsers > 1 Then
' If waarschuwUser_flag Then
' BerichtWeergeven "U heeft nog 2 minuten de tijd om af te maken waar u mee bezig bent." & vbNewLine & _
' "Hierna zult u automatisch uitgelogd worden......"
' waarschuwUser_flag = False
' Exit Sub
' Else
' Application.Quit
' End If
' Else
' Application.Quit
' DBEngine.CompactDatabase strFileName, tempstrFileName
' Kill strFileName
' Name tempstrFileName As strFileName
' End If
'End If
'End Sub
'' Make sure you have a reference to the
'' Microsoft DAO x.x Object Library
'Public Function TrackFileSize() As Integer
'
'Dim filename As String
'Dim filepath As String
'Dim filelength As Integer
'
' filepath = "H:\Gerard\"
' filename = Dir("H:\Gerard\ACQUISITIE_be.mdb&quot
'
' TrackFileSize = FileLen(filepath & filename) / 1000 / 1000
'End Function

'Public Function TrackUsers() As Integer
' TrackUsers = Nz(DCount("UserID", "tblLoggedOn&quot, 0)
'End Function

 

[evalesthy]

Related question. If you are using Acc2k or higher why wouldn't you just use: Tools --> Options --> 'General' tab --> "Compact on Close" checkbox?

 

[ideafixer]

That is an interesting point. I could do it that way. Do you know if it is possible to turn the compact on close property on and off through code?