By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,806 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Access Database File Is Growing -- Not Sure Why

100+
P: 153
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?
Dec 1 '06 #1
Share this Question
Share on Google+
7 Replies


100+
P: 153
Could it be because I have fields indexed?
Dec 1 '06 #2

100+
P: 153
ahh nevermind tried getting rid of indexes...that doesn't work...please help lol
Dec 1 '06 #3

100+
P: 153
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?
Dec 1 '06 #4

ADezii
Expert 5K+
P: 8,669
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
Dec 1 '06 #5

100+
P: 153
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.
Dec 1 '06 #6

NeoPa
Expert Mod 15k+
P: 31,661
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.
Expand|Select|Wrap|Line Numbers
  1. 'CompactDb compacts a remote Access database.
  2. Public Function CompactDb(ByVal strDB As String, _
  3.                           Optional strPW As String = "", _
  4.                           Optional blnZip As Boolean = False) As Boolean
  5.     Dim strNewDB As String, strZip As String, strLocale As String
  6.  
  7.     On Error GoTo ErrorCDB
  8.     strNewDB = ParamReplace(strDB, ".Mdb", "New.Mdb")
  9.     Call Echo(True, "Compacting """ & strDB & """.")
  10.     If strPW <> "" Then strLocale = ";pwd=" & Scramble(strPW:=strLocale)
  11.     If Exist(strNewDB) Then Kill strNewDB
  12.     Call DBEngine.CompactDatabase(SrcName:=strDB, _
  13.                                   DstName:=strNewDB, _
  14.                                   DstLocale:=strLocale, _
  15.                                   SrcLocale:=strLocale)
  16.     Kill strDB
  17.     Name strNewDB As strDB
  18.     If blnZip Then
  19.         strZip = ParamReplace(strDB, ".Mdb", ".Zip")
  20.         Call Zip(strZip, strDB)
  21.     End If
  22.     Call Echo(True, """" & strDB & """ compacted.")
  23.     CompactDb = True
  24.     Exit Function
  25.  
  26. ErrorCDB:
  27.     CompactDb = False
  28. End Function
Dec 1 '06 #7

100+
P: 153
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.
Expand|Select|Wrap|Line Numbers
  1. 'CompactDb compacts a remote Access database.
  2. Public Function CompactDb(ByVal strDB As String, _
  3.                           Optional strPW As String = "", _
  4.                           Optional blnZip As Boolean = False) As Boolean
  5.     Dim strNewDB As String, strZip As String, strLocale As String
  6.  
  7.     On Error GoTo ErrorCDB
  8.     strNewDB = ParamReplace(strDB, ".Mdb", "New.Mdb")
  9.     Call Echo(True, "Compacting """ & strDB & """.")
  10.     If strPW <> "" Then strLocale = ";pwd=" & Scramble(strPW:=strLocale)
  11.     If Exist(strNewDB) Then Kill strNewDB
  12.     Call DBEngine.CompactDatabase(SrcName:=strDB, _
  13.                                   DstName:=strNewDB, _
  14.                                   DstLocale:=strLocale, _
  15.                                   SrcLocale:=strLocale)
  16.     Kill strDB
  17.     Name strNewDB As strDB
  18.     If blnZip Then
  19.         strZip = ParamReplace(strDB, ".Mdb", ".Zip")
  20.         Call Zip(strZip, strDB)
  21.     End If
  22.     Call Echo(True, """" & strDB & """ compacted.")
  23.     CompactDb = True
  24.     Exit Function
  25.  
  26. ErrorCDB:
  27.     CompactDb = False
  28. 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.
Dec 1 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.