Access Database File Is Growing -- Not Sure Why

[Kosmos]

Hey guys, I was hoping someone could give me an idea of where I should look for this..my access database file is rapidly growing....but I have the access database import an excel spreadsheet and export an excel spreadsheet...and before and after the program is run I have every single table cleared. Right now the program is growing by 3 mb each time which is a reasonable size considering I am working with thousands of names and serial numbers, etc... which are then searched through millions of times to create a number set and primary key by which to sort by....it seems some or all of this data is being stored somewhere but surely not in my tables.
Any idea where to look?

[Kosmos]

Could it be because I have fields indexed?

[Kosmos]

ahh nevermind tried getting rid of indexes...that doesn't work...please help lol

[Kosmos]

I found out that if I go under Tools and then Database Utilities there's an option to 'Compact and Repair Database..." This works to compact the database to the original size, but how can I code this into the form so that when they click the button that runs the program it always compacts and repairs before finishing?

[ADezii]

Hey guys, I was hoping someone could give me an idea of where I should look for this..my access database file is rapidly growing....but I have the access database import an excel spreadsheet and export an excel spreadsheet...and before and after the program is run I have every single table cleared. Right now the program is growing by 3 mb each time which is a reasonable size considering I am working with thousands of names and serial numbers, etc... which are then searched through millions of times to create a number set and primary key by which to sort by....it seems some or all of this data is being stored somewhere but surely not in my tables.
Any idea where to look?

Do you try Tools, Options, General, Compact on Close

[Kosmos]

well I actually found what I was going for with the following code:

Public Sub CompactDB()

CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction

End Sub

it still does help for me to set it that way in case I back up the databse the file does get a little bit larger until I run the program again so thank you for your advice as well...the thing is other people will be running on their computers and they might not have their computers set to that, but I will include that now in the directions file.

Thanks again.

[NeoPa]

This function won't run directly as it refers to other procedures not available to you.
However, it should show you how to call the Compact & Repair feature from code.

'CompactDb compacts a remote Access database.
Public Function CompactDb(ByVal strDB As String, _
                          Optional strPW As String = "", _
                          Optional blnZip As Boolean = False) As Boolean
    Dim strNewDB As String, strZip As String, strLocale As String
 
    On Error GoTo ErrorCDB
    strNewDB = ParamReplace(strDB, ".Mdb", "New.Mdb")
    Call Echo(True, "Compacting """ & strDB & """.")
    If strPW <> "" Then strLocale = ";pwd=" & Scramble(strPW:=strLocale)
    If Exist(strNewDB) Then Kill strNewDB
    Call DBEngine.CompactDatabase(SrcName:=strDB, _
                                  DstName:=strNewDB, _
                                  DstLocale:=strLocale, _
                                  SrcLocale:=strLocale)
    Kill strDB
    Name strNewDB As strDB
    If blnZip Then
        strZip = ParamReplace(strDB, ".Mdb", ".Zip")
        Call Zip(strZip, strDB)
    End If
    Call Echo(True, """" & strDB & """ compacted.")
    CompactDb = True
    Exit Function
 
ErrorCDB:
    CompactDb = False
End Function

[Kosmos]

This function won't run directly as it refers to other procedures not available to you.
However, it should show you how to call the Compact & Repair feature from code.

'CompactDb compacts a remote Access database.
Public Function CompactDb(ByVal strDB As String, _
                          Optional strPW As String = "", _
                          Optional blnZip As Boolean = False) As Boolean
    Dim strNewDB As String, strZip As String, strLocale As String
 
    On Error GoTo ErrorCDB
    strNewDB = ParamReplace(strDB, ".Mdb", "New.Mdb")
    Call Echo(True, "Compacting """ & strDB & """.")
    If strPW <> "" Then strLocale = ";pwd=" & Scramble(strPW:=strLocale)
    If Exist(strNewDB) Then Kill strNewDB
    Call DBEngine.CompactDatabase(SrcName:=strDB, _
                                  DstName:=strNewDB, _
                                  DstLocale:=strLocale, _
                                  SrcLocale:=strLocale)
    Kill strDB
    Name strNewDB As strDB
    If blnZip Then
        strZip = ParamReplace(strDB, ".Mdb", ".Zip")
        Call Zip(strZip, strDB)
    End If
    Call Echo(True, """" & strDB & """ compacted.")
    CompactDb = True
    Exit Function
 
ErrorCDB:
    CompactDb = False
End Function

Yes I have seen this code...but thanks for the help as always. For what I was doing it wasn't a big issue because the program is for people that don't know how to use access so it's sorta like a one click function and the Wise Zoraster will answer all of your questions at once...and so after that is done and I clear all the tables I just have it do the compact function from the file menu.