How do I create a copy of the BE from within code in the FE. 1

[ChrisW75]

I've got an MS Access application to which I've added some auditing procedures, and password protected the Back End database (also MS Access) so that users cannot get to the data without going through the Front End. However, some of the users want to be able to take copies of the data out into the field with them on laptops, or be able to write their own reports. Easiest idea I came up with was to give them a button in the FE database to export all the tables from the BE, (except the user control table and auditing table) to a read only database with no password protection on it so they can write their own reports without bugging me for them, but without damaging the integrity of the data in the main database.
I tried using the DoCmd.TransferDatabase method, but this requires the destination database already exists, and also only puts the link in rather than copying the data.

Any ideas? This is what I've done, I created a blank dummy destination table, then ran the code below.

Private Sub cmd_export_Click()

 DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\Workfiles\test\tester.mdb", acTable, "tblAppointSchedule", "tblAppointSchedule"

End Sub

 

[markphsd]

Have you also considered you may have to synchronize the databases? You can look up synchornizing in the search.

You can use the filesystem to copy a file. You could also create a mdb, then transfer the source tables to it. If you don't know how to do that, look up filesystem object in a search.

You can also look up, backup database. That would provide similiar results to what you want, since you are essentially backing up the data to another source.



Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[ChrisW75]

Thanks, I'll lookup the filesystem object, however, I then need to remove the password from the copy and, if possible, set the properties to read only. I'm guessing that the filesystem object won't allow that.

 

[ChrisW75]

I got the answer. After copying the database using the filesystem object as suggested by markphsd above, I then opened the copy and cleared the password.

Private Sub cmd_export_Click()

  'create copy of BackEnd database
  FileSystem.FileCopy "N:\InfoServices\test999D.mdb", "C:\Workfiles\test\tester2.mdb"
  
  'define workspace to load database into
  Dim tempdb As Workspace
  'define database
  Dim dbsAnother As Database
    
  ' Return reference to default workspace.
    Set tempdb = DBEngine.Workspaces(0)
    ' Open copy of BackEnd dabatase, providing the password.
    Set dbsAnother = tempdb.OpenDatabase("C:\Workfiles\tester2.mdb", True, False, ";pwd=testpass")
    'clear the password by setting to a zero length string
    dbsAnother.NewPassword "testpass2", ""
    'close the copy of the back end datbase
    dbsAnother.Close
End Sub

I'll still have to work out how to set the properties to read only, but I'm sure that won't be too hard. Then all I've got to do is work out what to do about the front end (give the users a second copy of the front end or put a facility in the existing front end to point to a new database rather than the default central copy.

If anyone has any thoughts on this, I'll welcome them.

Cheers.