Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Compact & Repair Access Database from Excel

  Asked By: Jai    Date: Jan 20    Category: MS Office    Views: 9249
  

I need a macro or VBA code to Compact an Access database from Excel.
Any ideas??? I've seen some VB6 code but its not working in Excel.

Share: 

 

17 Answers Found

 
Answer #1    Answered By: Hattie Howard     Answered On: Jan 20

What is the code  and what does it do when you try to run it?

 
Answer #2    Answered By: Adanalie Garcia     Answered On: Jan 20

A common way of compacting an Access database  is to automate the menu
choice.



CommandBars("Menu Bar").Controls("Tools").Controls("Database utilities"). _

Controls("Compact and repair database...").accDoDefaultAction



The problem with doing this in Excel is that CommandBars refers to the
Excel object model rather than the Access object model.



A reference to the Access model is required and the command needs to be
changed to indicate that it refers to the Access model.

 
Answer #3    Answered By: Ginger Snyder     Answered On: Jan 20

If you have a reference to Access from Excel, say

AccessApp.CommandBars("Menu Bar").Controls("Tools").Controls("Database
Utilities")

I haven't tried it, just a thought.

 
Answer #4    Answered By: Jimmy Abp     Answered On: Jan 20

To compact an Access database  via VBA you need to make a copy of the
original, and compact from one to the other. When complete you can
delete the original. Here is code  from within Access, you will need to
reference MS-Access within Excel to accomplish this. Though I will admit
I never tried this via Excel.

Dim dbOriginal As String
Dim dbCompact As String
Dim dbPath As String
Dim dbTempPath As String

dbOriginal = "Inventory and Cost.mdb" 'Original Database
dbCompact = "Inventory and Cost1.mdb" 'Database to copy too
dbPath = "M:\Databases\" & dbOriginal 'Path of original database
dbTempPath = "M:\Databases\" & dbCompact 'Path of new database

Call DBEngine.CompactDatabase(dbPath, dbTempPath) 'Compact data
DoEvents
Kill dbPath 'Delete original
DoEvents
Name dbTempPath As dbPath 'Rename new database to original name

 
Answer #5    Answered By: Rickey Scott     Answered On: Jan 20

I copied and pasted the code  into that last email, and it didn't
come through looking right. I'll try this again, however if it still
looks funny in the email, I'm sure you can copy and paste into your
module and break it apart correctly.

Dim dbOriginal As String
Dim dbCompact As String
Dim dbPath As String
Dim dbTempPath As String

dbOriginal = "Inventory and Cost.mdb" 'Original Database
dbCompact = "Inventory and Cost1.mdb" 'Database to copy too
dbPath = "M:\Databases\" & dbOriginal 'Path of original database
dbTempPath = "M:\Databases\" & dbCompact 'Path of new database

Call DBEngine.CompactDatabase(dbPath, dbTempPath) 'Compact data
DoEvents
Kill dbPath 'Delete original
DoEvents
Name dbTempPath As dbPath 'Rename new database  to original name

 
Answer #6    Answered By: Monique Perry     Answered On: Jan 20

--also read this to the end:
http://www.mdbmakers.com/forums/showthread.php?
t=574&highlight=compactdatabase

 
Answer #7    Answered By: Roosevelt Jenkins     Answered On: Jan 20

I have searched the same problem for a while.
Found this:
www.mdbmakers.com/forums/showthread.php?t=9382
but have not succeed.
Maybe you can get something out of it, I'll be happy to hear.

 
Answer #8    Answered By: Rosie Bailey     Answered On: Sep 06

Well may be this VB code be a help

Application.CompactRepair SourceFile, DestinationFile [, LogFile]
'SourceFile = File to be compacted/repaired
'DestinationFile = the new compacted/repaired file
'LogFile = Optional (boolean) whether a log file should be created

Source:- recoveryandmanagement.wordpress.com/.../

 
Answer #9    Answered By: Patty Fox     Answered On: Sep 17

Repair your corrupt database then try Recovery for Access tool to repair lost .mdb & .accdb files. It recover various database objects like tables, vies, macros, modules, queries, etc.It supports MS Access 2010, 2007, 2003, 2002 & 2000.

Try:- www.recoverydeletedfiles.com/...recovery-tool.html

 
Answer #10    Answered By: Charlie Doerr     Answered On: Oct 10

All you need to do just read and follow the instruction posted here - www.filesrepairtool.com/access-file-repair.html

 
Answer #11    Answered By: John Brad     Answered On: Dec 18

You can this manually steps for recover access database file.

Read this blog to more details: nalavadebela.wordpress.com/.../access-database-repair

 
Didn't find what you were looking for? Find more on Compact & Repair Access Database from Excel Or get search suggestion and latest updates.




Tagged: