Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

CompactDatabase encrypt decrypt

Posted on 2014-08-19
8
Medium Priority
?
698 Views
Last Modified: 2014-08-25
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.
0
Comment
Question by:CRB1609
  • 4
  • 4
8 Comments
 
LVL 59
ID: 40271602
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
 

Author Comment

by:CRB1609
ID: 40271627
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
 
LVL 59
ID: 40272503
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:CRB1609
ID: 40275322
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
 
LVL 59

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 40276562
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
 

Author Comment

by:CRB1609
ID: 40282210
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
 
LVL 59
ID: 40282821
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
 

Author Comment

by:CRB1609
ID: 40284778
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

581 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