• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

Compacting an Access 2007 database with Excel Macro

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
Saurabh Singh Teotia
Asked:
Saurabh Singh Teotia
  • 7
  • 4
1 Solution
 
calacucciaCommented:
Does this work?

Set appAccess = New Access.Application
appAccess.OpenCurrentDatabase txtTargetDatabase.Value, True, txtPassword.Value & ""
0
 
Saurabh Singh TeotiaAuthor Commented:
I dont want to open the database i want to compact it without opening..
0
 
Saurabh Singh TeotiaAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
calacucciaCommented:
Or

DBEngine.CompactDatabase strSource, strDestination, ";pwd=password", , ";pwd=password"
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Saurabh Singh TeotiaAuthor Commented:
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
 
Saurabh Singh TeotiaAuthor Commented:
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
 
calacucciaCommented:
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
 
Saurabh Singh TeotiaAuthor Commented:
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
 
Saurabh Singh TeotiaAuthor Commented:
LSM,
Even in the shell command it ask for database password, How will i embeded database password in the code itself?
Saurabh...
0
 
Saurabh Singh TeotiaAuthor Commented:
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
 
calacucciaCommented:
Glad you worked it out
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now