Solved

Compacting an Access 2007 database with Excel Macro

Posted on 2010-08-25
12
578 Views
Last Modified: 2013-11-27
Hi All,

What I'm trying to do is compacting a access 2007 database with an excel macro. However my problem is that database is password protected and it asked for password as in prompt a password box and when i enter it and it compacts the database.Somehow i'm not able to figure out how to embed this password in the code itself. Can anyone help me in the same. Enclosed is the code.

Also i tried jet method but it won't work since i'm dealing with 2007 and it gives me an error message in that which is class not registered.

My Code...

Saurabh...

Sub compactmydatabse()

Dim x As New Access.Application

 x.CompactRepair "c:\COD\ABC.accdb", "C:\COD\abc1.accdb"

Kill "C:\COD\abc.accdb"

Name "C:\COD\abc1.accdb" As "C:\COD\abc.accdb"



End Sub

Open in new window

0
Comment
Question by:Saurabh Singh Teotia
  • 7
  • 4
12 Comments
 
LVL 17

Expert Comment

by:calacuccia
ID: 33526660
Does this work?

Set appAccess = New Access.Application
appAccess.OpenCurrentDatabase txtTargetDatabase.Value, True, txtPassword.Value & ""
0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
ID: 33526678
I dont want to open the database i want to compact it without opening..
0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
ID: 33526701
Also the reason why i dont want to open it because if i come into compact command after that it gives me an error message stating microsoft can't compact and repair the current database.
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33526703
Or

DBEngine.CompactDatabase strSource, strDestination, ";pwd=password", , ";pwd=password"
0
 
LVL 84
ID: 33526709
You can use Shell:

Shell Chr(34) & "Full path to msaccess.exe" & Chr(34) & " " & Chr(34) & "Full path to your database" & Chr(34) & " /compact"

You __might__ be able to get by with this:

Shell "msaccess.exe " & Chr(34) & "Full path to your database" & Chr(34) & " /compact"

but that would be platform-dependent. If you're deploying this, take the time to get the correct value of the msaccess.exe executable.
0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
ID: 33526741
calacuccia,
What is the type for db engine as it gives me an error message that database not recognised.
LSM:-
Let me try that out will get back to you about it.
Saurabh...
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 59

Author Comment

by:Saurabh Singh Teotia
ID: 33526759
Lsm,
One quick observation about the above code, I tried it out it worked without an error message. However this is something weird when i check the properties of the database the file path is exactly still the same as earlier. However as soon i open the access database the file size reduces by 50% any particular reason why its happening this way?
Saurabh...
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33526764
Saurabh, I just understood that :-) Excuses for my ignorance. But can't you add a reference to the Jet library? If it's as stupid as my first post, I'll leave the field and go back to Excel :-)
0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
ID: 33526772
calacuccia, nope this is not jet library as per reasearch done by me jetlibrary method works perfectly till access 2000 as in till the format .mdb as soon the format becomes accdb it fails. I have been trying all set of methods and i have just not able to compact by this database becuase not is working in 2007... :-(
Saurabh...
0
 
LVL 59

Author Comment

by:Saurabh Singh Teotia
ID: 33526789
LSM,
Even in the shell command it ask for database password, How will i embeded database password in the code itself?
Saurabh...
0
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 0 total points
ID: 33527000
calacuccia,
I was further testing over it and here are my findings that if i modify the code as dbengine one to this it reconises 2007 as well and works perfectly.
Here is the my code which i used..
Saurabh...

Sub compactdatabse()

Dim x As New Access.Application





x.DBENGINE.CompactDatabase "C:\COD\Mad.accdb", "C:\COD\Mad1.accdb", ";pwd=pass", , ";pwd=pass"



Kill "C:\COD\Mad.accdb"

Name "C:\COD\Mad1.accdb" As "C:\COD\Mad.accdb"

End Sub

Open in new window

0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33527203
Glad you worked it out
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now