Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    14

    Red face Unanswered: How do I close my linked back end database?

    Hiya,
    OK, this is a little complex. I have an application which has a front end with all the code, and a backend with all the data (including user logins and passwords). The back end is password protected and the whole thing is fully locked down to maintain auditing integrity. Now, I want to give the users the ability to create a separate copy of this database so that they can take it away on their laptops, and also create whatever reports they may want, essentially a separate copy of the backend data, but without the password protection. Not elegant, however it'll keep them off my back.

    Here's the code I'm using:-

    Code:
    Private Sub cmd_export_Click()
    
      'Close the backend database?
      Dim dbMain As Database
      Set dbMain = CurrentDb()
      dbMain.Close
    
      Dim Path As String
      Path = filepath & "\stand_alone_copy.mdb"
        
      'create copy of BackEnd database
      FileSystem.FileCopy glbPath, Path
      
      '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(Path, True, False, ";pwd=mypassword")
        'clear the password by setting to a zero length string
        dbsAnother.NewPassword "mypassword", ""
        'close the copy of the back end datbase
        dbsAnother.Close
        
        SetAttr Path, vbReadOnly
        
        DoCmd.Close acForm, "frmExportdbase"
    
    End Sub
    The thing is, this doesn't work. Filecopy cannot copy a file which is currently open (although I can manually copy the file through windows explorer), and the preceding commands don't actually close the current database at all. So, what I want is one of 3 options
    1 - How to close the back end and then re-open it afterwards.
    2 - A different command for copying the file.
    3 - A totally different way of doing this.

    Any help will, as always, be gratefully received. I'm a bit of a noob at this, and this project is a really steep learning curve for me. Alot of this code is pretty much copied parrot fashion from help dialogues and tweaked until it works, so if you see something really dumb there, please let me know.
    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you could try to create a replica - have a look for replication in the help system

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    Either that or use an API function to copy and paste your file.



  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I suppose another approach may be to copy the tables you want to a third db, and then disconnect from that db.

    Could also consider using an audit log which monitors every change to the back end p[erhaps say storing the changes as native SQL (eg insert...., update etc...), export that table and run it against a copy taken when the back end db can be safely copied.

    Or get aggressive with the users and force a shut down for maintenance say over lunchtime. do your copy then and your users will be nor more than say 3..4 hours out of date.

    just a few thoughts

  5. #5
    Join Date
    Nov 2004
    Posts
    14
    Thanks for the suggestions. The API idea sound interesting, but I don't know where to start, what commands should I be looking for? I'm still not used to working in VBA.
    The other avenue I'm going to look at is creating a 3rd .mdb file (don't know how to do that yet) then export/import all the tables into that.

    I'm not sure about the replication thing. I looked into that, but I got the impression that this keeps track of changes in all the copies to allow synchronisation and that's a bit more than I want to bite off at the moment, the project is overdue and I need to get this last bit done ASAP, so I'm looking for a quick and foolproof way of doing it.
    Cheers.

  6. #6
    Join Date
    Nov 2004
    Posts
    14
    Done it. The reason that it couldn't copy the back end database was that there was a form open which linked to data in it. By adding in a "docmd.close acForm <formname>" at the start, the code I had above worked perfectly. I then added in a docmd.openform <formname> to reopen it at the end (just before closing my copy form) and it worked beautifully. I suspect my "dbMain.Close" command is doing nothing as I didn't have to reopen, unless of course reopening the form implicitly does this.
    I think I'm now only a little debugging away from being able to release this for UAT.
    Thanks for your help!

    Chris.
    Last edited by ChrisW75; 02-21-05 at 19:47.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •