Backing up database with password (1 Viewer)

[OBBurton]

Many thanks to the many who helped me with my last post on this subject. Well, I finally got a function working flawlessly until I password protect the database. Is there anything I can add to this code to prompt the user for a password? Or even code to embed a password? The code is :

Public Function BackupDB()
On Error GoTo BackupDB_Err

    Dim sourceFile As String, destinationFile As String
    Dim aFSO As Variant
    Dim path As String, name As String

    path = CurrentProject.path
    name = CurrentProject.name
    
    sourceFile = path & "\InvoicesBE.accdb"
    destinationFile = path & "\Backups\InvoicesBE" & _
        "_Backup" & "_" & Year(Now) & "-" & Month(Now) & _
        "-" & Day(Now) & ".accdb"
    'this removes a file created on the same day
    If Dir(destinationFile) <> "" Then
        Kill destinationFile
    End If
    
    'this creates a backup into destination path
    If Dir(destinationFile) = "" Then
        Set aFSO = CreateObject("Scripting.FileSystemObject")
        aFSO.CopyFile sourceFile, destinationFile, True
        MsgBox "A database backup has been stored under " & destinationFile
    End If

BackupDB_Exit:
    Exit Function
    
BackupDB_Err:
    ErrMsg ("BackupDB")
    Resume BackupDB_Exit
    
End Function

 

[OBBurton]

From the deafening silence in response to this thread, may I assume that this cannot be done? Or is it merely very difficult?

 

[vbaInet]

From the deafening silence in response to this thread, may I assume that this cannot be done? Or is it merely very difficult?

No, not difficult at all. We (or at least me) were hoping that you would have done some research first

A quick search yields:
http://msdn.microsoft.com/en-us/library/aa141813(office.10).aspx

By the way, you should be treating your backup with more caution. Instead of deleting the file if it exists, move to another location or rename it and after successfully making the new copy, delete the other file. Or keep a history of all backups.

 

[OBBurton]

Hi, I'm trying to use thi function from http://msdn.microsoft.com/en-us/library/aa141813(office.10).aspx which was suggested by vbaInet:

Function OpenProtectedDB(strDBPath As String, _
                         strPwd As String)
   Dim cnnDB As ADODB.Connection

   Set cnnDB = New ADODB.Connection

   ' Open database for shared (by default), read/write access, and
   ' specify database password.
   With cnnDB
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Properties("Jet OLEDB:Database Password") = strPwd
      .Mode = adModeReadWrite
      .Open strDBPath
   End With
   ' Code to work with database goes here.
   cnnDB.Close
   Set cnnDB = Nothing
End Function

Ive tried to use it for submitting a password to a password protected BE. I think I am on the right track. When I opened the DB I got my password entry form like I expected, but when I hit the command button that called the OpenProtectedDB function I got a compile error "User defined type not defined". It crashed on "cnnDB As ADODB.Connection". What do I have to do to get this type defined? Or is there some other problem with the procedure I used to call the function? The password form is opened when the database FE is opened. It is not password protected. the form has only a TextBox named txtPassword and a command button named cmdGo. The command button code is:

Private Sub cmdGo_Click()
    Call OpenProtectedDB("C:\Users\OB\OneDrive\" & _
        "Misc\TestDB_BE.accdb", Me.txtPassword)
    DoCmd.OpenForm "frmInvoice", acNormal
End Sub

If anyone can help, I'd really appreciate it.

 

[vbaInet]

Is your question actually in two fold?

I.e.
1. Password protect a db and
2. open a password protected db?

From your original post I got the impression that all you wanted to do was password protect the db.

 

[OBBurton]

I have password protected the BE database but not the FE. I have been able to open the FE, but if I try to open the main form (frmInvoices) I get an error message "Not a valid password." So I deduced that I had to call the OpenPotectedDB function (which I believed would pass the password to the BE) before trying to load frmInvoice. The procedure I used to open the frmPassword form is outlined above.

I have done some research since my last post and discovered how to reference the Microsoft ActiveX Data Objects 2.1 Library and the Microsoft ADO Ext. 2.8 for DDL and Security. Doing this gave me a different error message "Provider not found. It may not be properly installed." It is now crashing on

.Open strDBPath

At least it is now compiling. Both of my variables have been passed properly. I don't understand why it is crashing. How do I insure that the provider is properly installed? Can anyone help? :banghead:

 

[OBBurton]

More research. I found out that the Microsoft.Jet.OLEDB.4.0 provider does not work in Access 2013 64 bit. I changed the provider to "Microsoft.ACE.OLEDB.12.0" and now I get a runtime error "Not a valid password." My password is correct, so what are the possible causes?

 

[vbaInet]

You need to delete the linked all the tables in the front end, relink to the backend using the External Data tab -> Access, selecting your db and providing the password.

 

[OBBurton]

I am a knucklehead! I don't even want to admit what my problem has been.I have the FE opening just fine now. Both FE and BE are password proteccted. I am now trying to back up the password protected BE file. The code to backup is called by the cmdGo button on the password entry form. The code is:

Private Sub cmdGo_Click()
    Call OpenProtectedDB("C:\Users\OB\OneDrive\Invoices\" & _
        "InvoicesBR.accdb", txtPassword)
End Sub

The code for OpenPotectedDB is:

Function OpenProtectedDB(strDBPath As String, _
                         strPwd As String)
   Dim cnnDB As ADODB.Connection
   Set cnnDB = New ADODB.Connection

   ' Open database for shared (by default), read/write access, and
   ' specify database password.
   With cnnDB
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .Mode = adModeReadWrite
      .Open ConnectionString:=strDBPath, Password:=strPwd
   End With
   ' Code to work with database goes here.
    Call BackupDB
   cnnDB.Close
   Set cnnDB = Nothing
End Function

When I click on cmdGo I get a runtime error "Cannot start your application. The workgroup information file is missing or opened exclusively by another user." From what I have gathered online, my connection string is correct and I'm sure the password is right. I know no other user has the file open. Both variables are passing correctly. I'm not sure what to do next? Does anyone have any ideas? :banghead:

 

[vbaInet]

Ok, now you've lost me. You say "I am now trying to back up the password protected BE file" but then you're showing code for opening a password protected db.

What exactly are you trying to do now? Backup the back end or open the password protected front end?

 

[OBBurton]

I'm trying to backup the BE. I thought I needed the OpenProtectedDB to do it. I'm the one who's lost! If I skip that and just go back to the code for backing up my BE, the code is:

Public Function BackupDB()
On Error GoTo BackupDB_Err

    Dim sourceFile As String, destinationFile As String
    Dim aFSO As Variant
    Dim path As String, name As String

    path = CurrentProject.path
    name = CurrentProject.name
    
    sourceFile = path & "\InvoicesBE.accdb"
    destinationFile = path & "\Backups\InvoicesBE" & _
        "_Backup" & "_" & Year(Now) & "-" & Month(Now) & _
        "-" & Day(Now) & ".accdb"
    'this removes a file created on the same day
    If Dir(destinationFile) <> "" Then
        Kill destinationFile
    End If
    
    'this creates a backup into destination path
    If Dir(destinationFile) = "" Then
        Set aFSO = CreateObject("Scripting.FileSystemObject")
        aFSO.CopyFile sourceFile, destinationFile, True
        MsgBox "A database backup has been stored under " & destinationFile
    End If

BackupDB_Exit:
    Exit Function
    
BackupDB_Err:
    ErrMsg ("BackupDB")
    Resume BackupDB_Exit
    
End Function

When I set a break point set at "aFSO.CopyFile sourceFile, destinationFile, True" and click the button it stops the program there. But when I hit F8
it goes straight to my error handler. It shows an err.Number of 0. Both of the variables are right but it doesn't run. It worked fine before I password protected the DBs.

 

[OBBurton]

I finally figured out what I did wrong! Sorry to lead you on a wild goose chase, it was a couple of amateur mistakes that aren't worth explaining. Thanks for your patience.

 

[vbaInet]

Good to hear OBBurton!

By the way, did you take note of this comment?

By the way, you should be treating your backup with more caution. Instead of deleting the file if it exists, move to another location or rename it and after successfully making the new copy, delete the other file. Or keep a history of all backups.

 

[OBBurton]

Thank you for bearing with me through this, I really appreciate it. Yes, I did take note and have changed the code. It now includes the date, hour and minute in the name and I am keeping a complete history of them. I'm not terribly concerned if I rewrite a backup that is less than a minute old. Should I be?

 

[vbaInet]

Should I be?

Not unless your whole household is busy writing to the db every second.

 

[OBBurton]

It would be very unusual for us to both have it open at the same time.