CompactDatabase encrypt decrypt

Can someone please show me how to alter the following line of code:

1. To decrypt the destination database where the SourceDb has a password.

2. To encrypt the destination database

DBEngine.CompactDatabase SourceDb, DestinationDb

Thanks in advance.
Clive BeatonAccess DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
DBEngine.CompactDatabase "<old DB>", "<new DB>", , dbDecrypt ,";pwd=<password>"

DBEngine.CompactDatabase "<old DB>", "<new DB>", , dbEncrypt ,";pwd=<password>"

Everything in <> must be replaced with an actual value.

Jim.
0
Clive BeatonAccess DeveloperAuthor Commented:
Jim,

When I run the following code, I get "Run-time error 3001 Invalid argument."

Private Sub OK_Click()
   Dim CurrentFile As String, BackupFile As String
   On Error GoTo 0
   CurrentFile = "C:\Celebrant Assist V3\CelaData.accdb"
   BackupFile = "C:\Celebrant Assist V3\tmp.accdb"
   If Dir(BackupFile) > "" Then
      Kill BackupFile
   End If
   DBEngine.CompactDatabase CurrentFile, BackupFile, , dbDecrypt, ";pwd=p@$$w0rd"
   Application.Quit
End Sub

Open in new window


I can open the currentfile manually, using the password.

Can you help?

Thanks in advance.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If you drop the fifth argument, do you get the invalid argument?  

That last argument is a variant, so it should take it in any number of ways and still work.

Try

DBEngine.CompactDatabase CurrentFile, BackupFile, , dbDecrypt, ;pwd='p@$$w0rd'

and

DBEngine.CompactDatabase CurrentFile, BackupFile, , dbDecrypt, ;pwd=p@$$w0rd

 result should be the same however.  

 It's rare that I use a password with a DB (in fact I can't remember the last time I did), although I have done the compact and repair in the past like this.

Jim.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Clive BeatonAccess DeveloperAuthor Commented:
Jim,

If I drop the fifth argument (pwd) I get 'Invalid password'.  The other two lines gave a compile error.   It seems to want " surrounding the pwd argument

I have attached a zip file containing a tiny accdb to decrypt the associated test data file.  The data file password is banana.

If you have time, could you please have a look and see if you can spot the problem.

Thanks in advance.

Clive
TestDC.zip
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Clive,

 OK, couple of things:

1. You had a reference set to DAO 3.6; the 3.6 lib knows nothing about accdb's.

2. With the correct lib, it still doesn't work because using the database password in the 5th argument is incorrect.   Looks like the call has been changed as the 5th argument is now "DstLocal".  What's confusing is how anyone is getting this to work.  I found numerous postings where folks say they put the password in the fifth argument, but I always got "invalid password" not matter what I did.

3. So with that, what I came up with is this:

Private Sub OK_Click()
   On Error GoTo 0

   Dim CurrentFile As String, BackupFile As String

   CurrentFile = "C:\TestDC\TestDCdata.accdb"
   BackupFile = "C:\TestDC\tmp.accdb"
   If Dir(BackupFile) > "" Then
      Kill BackupFile
   End If
   'DBEngine.CompactDatabase CurrentFile, BackupFile, , dbDecrypt, ";pwd=banana"
   
   Dim JRO As JRO.JetEngine
Set JRO = New JRO.JetEngine
JRO.CompactDatabase "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & CurrentFile & ";Jet OLEDB:Database Password=banana", _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & BackupFile & ";Jet OLEDB:Engine Type=5;"

   MsgBox "Done"
'   Application.Quit

End Sub

This is using the ADO rather than DAO.

You need a reference set to "Microsoft Jet and Replication Objects 2.6 Library" or a later version (believe 2.6 was the last).

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Clive BeatonAccess DeveloperAuthor Commented:
Thanks, Jim.  Perfect.

Above, you wrote that you seldom use a password on a DB.  My password is on the BE db.  If I don't do that, how do I prevent users getting at the data.  I can post this as a separate question if you think I should.

Clive
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Clive,

 I've never prevent anyone from getting at the BE (well once, and it was a royal pain, so I never did it again) nor the FE for that matter other than to prevent un-authorized changes to the degree that the customer wants.

 I was instructed by a lawyer many years ago that I could open the door to a law suit if I prevented someone from getting at their data (whether that's still true today or not, I do not know).   Also understand that I do highly customized one-off software.   Because of that, the customer owns everything.

 If I need BE security, I do it at the file level.  I distribute the FE as a MDE.  Then I place the BE in a sub folder at least two levels deep.   At the first level, users are only given traverse folder rights and nothing else.

 So they can look into the 1st level sub folder, but see nothing.   The 2nd level sub folder has a highly randomized name, which they'd never guess.

 So as long as I never expose the full BE DB path in the app to the user, it effectively remains hidden and un-reachable except through the app even though there is no DB password.

Jim.
0
Clive BeatonAccess DeveloperAuthor Commented:
Jim,

Thanks for that.  Very clever.  

I have 600+ users so I think it would be too late to change anything.  I'll bear it in mind for my next project.

Thanks again for prompt and detailed help.

Clive
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.