Creating read only copy of BE DB (1 Viewer)

[OBBurton]

Hi,
I have searched and have not been able to find anything on creating a read-only copy of a back end DB with VBA. I am creating backups of my back end file and would like to create a local read-only copy to use in the event that the regular back end is not available. If anyone has any insight, it would be greatly appreciated.

The code I am using to create the backup is:

Public Function BackupBE()
'On Error GoTo BackupBE_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 = "\\OBBURTON-PC\Users\Public\Documents\" & _
        "FUN Stuff\WilburBE.accdb"
    destinationFile = "\\OBBURTON-PC\Users\Public\" & _
        "Documents\FUN Stuff\Backups\WilburBE_Backup_" & _
        Year(Now) & "-" & Month(Now) & "-" & Day(Now) & _
        "_" & Hour(Now) & "-" & Minute(Now) & ".accdb"
    'this removes a file created at the same time
'    MsgBox destinationFile
    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

BackupBE_Exit:
    Exit Function
    
BackupBE_Err:
    ErrMsg ("BackupBE")
    Resume BackupBE_Exit
    
End Function

 

[MarkK]

What do you mean, "in the event that the regular back end is not available?" I suggest that whatever problem makes the regular file not available, that same problem will deny you access to a file in a sub-folder on the same disk on the same machine.

But mainly, the scheme you describe just doesn't seem real-world to me. The BE is not sacred. If you lose access to it and you have a back-up, abandon the lost BE and use the back-up as the new BE. I don't see why your back-up needs to be read-only? Just use it as the BE.

Or have I misunderstood something?

 

[OBBurton]

Hi,
Thanks for answering!
I have explained in more detail in a seperate message, but there is a local copy for other users and a "file server" copy, which at the moment resides on my machine. I don't want changes made to the local copies since they will be lost anyway. It is not "real-world" but I am trying to simulate "real-world". Soon the "file server" role will be moved to another machine so they will not exist on the same hard drive. There will of course, be a backup system to store the BE on a removable drive. Anyway, that is why I want to do it. Is it doable?

 

[OBBurton]

Hi,
I think I've thought of a different approach to my problem. Please take a look at my idea's main steps and tell me if you see any problems or have any suggestions.
1) Check to see if the main BE exists
2) If not change the table (there is only one) link source to the local copy of the BE
3) Change the main form's RecordsetType property to Snapshot.
I think this would all be doable and accomplish what I had hoped to accomplish, but the examples I've found so far for step 2 are kicking my butt. I'll keep working on them while I wait for some input.

 

[MarkK]

I don't see a problem with those steps you outlined. That should work.

 

[OBBurton]

Hi,
I'm trying to addapt the following code from http://www.access-programmers.co.uk/forums/showthread.php?t=167786 for my application, but I don't see where "Tdf" gets defined. Can you help me out?

Private Sub Relink_Click()
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Dim NewPathname As String
Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs
NewPathname = InputBox("Input the path here.", "Input Path", Default)
'Loop through the tables collection
For Each Tdf In Tdfs
If Tdf.SourceTableName <> "" Then 'If the table source is other than a base table
Tdf.Connect = ";DATABASE=" & NewPathname 'Set the new source
Tdf.RefreshLink 'Refresh the link
End If
Next 'Goto next table
End Sub

 

[OBBurton]

Hi,
Please pardon that last question. It's been a long time since I used a for loop.

 

[vbaInet]

Just going to add that there's no such thing as a read-only back end. You control the read/write permissions of the back end from the front end.

 

[OBBurton]

I figured out that I can open a form in read-only mode, which suffices to met my needs. Thanks for all the help!

 

[vbaInet]

Good job OB!