Unanswered: How do I close my linked back end database?
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:-
Private Sub cmd_export_Click()
'Close the backend database?
Dim dbMain As Database
Set dbMain = CurrentDb()
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
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
SetAttr Path, vbReadOnly
DoCmd.Close acForm, "frmExportdbase"
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.
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.
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.
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!