Copying a password-protected BE in code

[RedSkies]

Here's what I'd like to do:

I have a split database with a password protected back end. In the front end interface, I want to create a routine that allows a user to copy the BE to the location of his choosing so that he can design his own adhoc queries/reports from a snapshot of the master data tables.

In other words, I don't want to give him access to the master BE but I don't care what he does with his own personal copy.

I just need a few hints on how to get started. I figure the routine will need to do the following:

Capture the source and destination file paths (I already have an existing routine to do this).

Temporarily change the BE password using the NewPassword method to a value I will provide to the user. (Or can I simply reset the password to nothing somehow?).

Copy the BE file by using the FileCopy method.

Change the BE password back to its original value.

Can all this be done from the front end, particularly the NewPassword method? Are there any special considerations with respect to exclusive rights I need to deal with?

Thanks in advance for any suggestions. I'm completely open to other approaches too if you have them. I'm beginning to think this probably isn't the best way to go about it.

Anne

 

[John.Woody]

You could try the following:

Filecopy from a to b

Then compact b and remove the password at this time

DBEngine.CompactDatabase YourFile, YourTemp, dbLangGeneral & ";pwd=yourPword", , ";pwd="

or similar

 

[Autoeng]

RdSkies:

I think you are going about this all wrong but proceed with how you see fit. If it were me, I would, and assume that you have, copied the FE to each user's machine. There I would let him create queries/reports to their hearts content but I wouldn't do it in the BE. Queries and reports don't belong in the BE.

 

[ghudson]

I would let the user export the tables to an Excel file. One file for each table (or query). Then the user can sort, filter, etc. all they want without the possibility of the users messing with data at the table level or in the frontend or backend of a db. A user should NOT have access to your tables or the design of your db. How can you prevent that if you allow them to play with your db whether it be the frontend or backend. Another solutions would be to export the objects to a new db onto their hard drive. My only concern with that is the users would still get all too familiar with your db and might be curious enough to play with it in a manner that you did not intend and then you will have all kinds of problems if they try and alter the innards of your db.

HTH

 

[RedSkies]

Thanks for the input, guys

I think I have it figured out though, just had to change the order a bit. Autoeng, you're correct: All users will have a local copy of the FE containing various queries and reports, but it will be locked down. That is, I'm not allowing anyone to have direct access to the tables (without going through a form) or to make any kind of design changes.

The purpose of providing a snapshot copy of the BE is for the benefit of a few "geeks" out there who may need to create one-time adhoc reports that aren't already provided in the existing FE. With their own copy of the data tables, they can manipulate and display the data however they wish without any danger of corrupting the data in the master BE. Slightly goofy? Yes! But that's what they asked for.

If anyone is interested, here's how I went about it:

First I created a form where the user can enter his desired filepath and filename for the BE copy. A button on the form has the following code:

Private Sub CopyBackEnd_Click()
On Error GoTo Err_CopyBackEnd_Click
Dim strSrc As String
Dim strDest As String
strSrc = "ActualFilePathofBackEnd"  
strDest = Me.DestinationFile

If Dir(strDest) <> "" Then
    Kill strDest
End If

CopyFile strSrc, strDest
SetDBPassword strDest, "ThePassword", ""
MsgBox "A copy of the data has been saved successfully as " & strDest, vbOKOnly, "File copied successfully"
   
Exit_CopyBackEnd_Click:
    Exit Sub

Err_CopyBackEnd_Click:
    MsgBox Err.Description
    Resume Exit_CopyBackEnd_Click
    
End Sub

I had problems using FileCopy because the backend file was already in use, so I used this piece of code I found somewhere (sorry I don't remember where) which allows copying a file even if it's already open.

Declare Function apiCopyFile Lib "kernel32" Alias "CopyFileA" _
      (ByVal lpExistingFileName As String, _
      ByVal lpNewFileName As String, _
      ByVal bFailIfExists As Long) As Long
      
    Sub CopyFile(SourceFile As String, DestFile As String)
      '---------------------------------------------------------------
      ' PURPOSE: Copy a file on the network or local machine from one location to another.
      ' ACCEPTS: The name of the source file and destination file.
      ' RETURNS: Nothing
      '
      ' ** This will copy a file if it is open or not.
      '
      '---------------------------------------------------------------
        Dim result As Long
         If Dir(SourceFile) = "" Then
            MsgBox Chr(34) & SourceFile & Chr(34) & _
               " is not valid file name."
         Else
            result = apiCopyFile(SourceFile, DestFile, False)
         End If
      End Sub

Then I used the following code courtesy of another Forum user (R2D2) to change the password after the file copy is made:

Function SetDBPassword(strDBPath As String, _
                      strOldPwd As String, _
                      strNewPwd As String)
  ' This procedure sets a new password or changes an existing
  ' password.

  Dim dbsDB      As DAO.Database
  Dim strOpenPwd As String

  ' Create connection string by using current password.
  strOpenPwd = ";pwd=" & strOldPwd

  ' Open database for exclusive access by using current password. To get
  ' exclusive access, you must set the Options argument to True.
  Set dbsDB = OpenDatabase(Name:=strDBPath, _
                           Options:=True, _
                           ReadOnly:=False, _
                           Connect:=strOpenPwd)

  ' Set or change password.
  With dbsDB
     .NewPassword strOldPwd, strNewPwd
     .Close
  End With

  Set dbsDB = Nothing
End Function

 

[Autoeng]

As usual you're correct Rich. I guess I was a little foggy this morning when I answered.

Nice code RedSkies.