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

Compact / Compress Access File using code

P: 28
I have just come back to coding after a break of 12 months, as you know remebering wat u knew can be hard, i created a macro or code that compressed another database (renamed to temp, then copied back to original name) so that the user could maintain the size as required but for the life of me i cant find the file i created, can u help please.
Nov 24 '06 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,660
Are you asking for help finding a file stored on your system?
If not, I'm not sure what you mean.
Perhaps you could clarify.
Nov 24 '06 #2

P: 28
Are you asking for help finding a file stored on your system?
If not, I'm not sure what you mean.
Perhaps you could clarify.

No sorry, just that if you know the method / code that allows me to compact another file from a macro / database, i.e compact closed file A from code within open file B. hope this helps explain what i need.
Nov 24 '06 #3

NeoPa
Expert Mod 15k+
P: 31,660
I've got you.
I'll post it here when I've found it - shouldn't be too long.
Nov 24 '06 #4

NeoPa
Expert Mod 15k+
P: 31,660
This is an example function that I use.

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 = "") As Boolean
  4.     Dim strNewDB As String, strLocale As String
  5.  
  6.     On Error GoTo ErrorCDB
  7.     strNewDB = Replace(strDB, ".Mdb", "New.Mdb")
  8.     Call Echo(True, "Compacting '" & strDB & "'.")
  9.     If strPW <> "" Then strLocale = ";pwd=" & strPW
  10.     If Exist(strNewDB) Then Kill strNewDB
  11.     Call DBEngine.CompactDatabase(SrcName:=strDB, _
  12.                                   DstName:=strNewDB, _
  13.                                   DstLocale:=strLocale, _
  14.                                   SrcLocale:=strLocale)
  15.     Kill strDB
  16.     Name strNewDB As strDB
  17.     Call Echo(True, "'" & strDB & "' compacted.")
  18.     CompactDb = True
  19.     Exit Function
  20.  
  21. ErrorCDB:
  22.     CompactDb = False
  23. End Function
  24.  
  25. 'Exist returns true if strFile exists.
  26. '22/05/2003 Rewritten with better code.
  27. '20/05/2005 Added finding of R/O, System & Hidden files
  28. Public Function Exist(strFile As String, _
  29.                       Optional intAttrib As Integer = &H7) As Boolean
  30.     Exist = (Dir(PathName:=strFile, Attributes:=intAttrib) <> "")
  31. End Function
Nov 24 '06 #5

P: 28
This is an example function that I use.

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 = "") As Boolean
  4.     Dim strNewDB As String, strLocale As String
  5.  
  6.     On Error GoTo ErrorCDB
  7.     strNewDB = Replace(strDB, ".Mdb", "New.Mdb")
  8.     Call Echo(True, "Compacting '" & strDB & "'.")
  9.     If strPW <> "" Then strLocale = ";pwd=" & strPW
  10.     If Exist(strNewDB) Then Kill strNewDB
  11.     Call DBEngine.CompactDatabase(SrcName:=strDB, _
  12.                                   DstName:=strNewDB, _
  13.                                   DstLocale:=strLocale, _
  14.                                   SrcLocale:=strLocale)
  15.     Kill strDB
  16.     Name strNewDB As strDB
  17.     Call Echo(True, "'" & strDB & "' compacted.")
  18.     CompactDb = True
  19.     Exit Function
  20.  
  21. ErrorCDB:
  22.     CompactDb = False
  23. End Function
  24.  
  25. 'Exist returns true if strFile exists.
  26. '22/05/2003 Rewritten with better code.
  27. '20/05/2005 Added finding of R/O, System & Hidden files
  28. Public Function Exist(strFile As String, _
  29.                       Optional intAttrib As Integer = &H7) As Boolean
  30.     Exist = (Dir(PathName:=strFile, Attributes:=intAttrib) <> "")
  31. End Function

Many thanks - i appreciate your help and expertise
Nov 24 '06 #6

Post your reply

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