DAO connection with bound objects; need to disconnect from DB, copy DB, then reconnect...
Posted on 2006-04-23
am re-writing an application which uses DAO connections and bound obects on a form. Ideally I need to keep the functionality that the DAO data object provides- of being able to quickly move through records , however, I also need to allow the user to 'back-up' or make a copy of the database file. The user should then be able to work with the original database file, but swap back to connecting to the backup if there is a mistake. It is sort of a limited 'redo'/ 'undo' function, where I am saving a copy of the entire database as the 'redo' or 'undo' to take the user to a previous 'state' of essentially the same database.
Although I have tried tracing through each step of my code and figuring out which connections are currently made to the database, and which recordsets are connected, as well as which DAO data objects themselves are currently 'connected' I am consistently getting either 'run time error 3426- action cancelled by an associated object' when I try to close the database (which I assume has something still connected to it?), or 'run time error 70- permission denied' when I try to use tempFS.MoveFile to take the database that I am using and move it to a backup location, in order to swap in the saved database.
Here is some of the code I am using:
Private Function dbUndo() As Integer
Dim tempRS As Recordset
If tempDB.Recordsets.Count > 0 Then
For Each tempRS In tempDB.Recordsets
tempRS.Close << Although I see six recordsets for tempDB- after closing the first I get error 3426
Assuming I get to this point and can close the database without the 3426 error... when I try to move the db file I then get the permission denied error
tempFS.MoveFile DPATH$ & DBNAME$, DPATH$ & "BAK\UNRE\R\" & DBNAME$ & ".re"
If I manage to get through both of those problems (by commenting lines out, or temporarily putting line in during debug) I try to be careful and reset each of these connections:
Set tempDB = OpenDatabase(DPATH$ & DBNAME$, False, False)
Set DataCustomer.Recordset = tempDB.OpenRecordset("CUSTOMER", dbOpenTable)
I would not consider myself to be a beginner or medium experience programmer, however am really starting to feel like one! I have only worked with VB4 and then VB6 for a few months while converting the above project.
I may be looking in completely the wrong direction or approaching this problem incorrectly, but I would like to able to connect, disconnect and reconnect to one of two databases, while maintaining the ability to keep the integrity of the data binding to my form objects.... is this possible?
TIA for any help you can offer....
500 points because this project is now 3 months past deadline and I finally figured I wasn't going to get it on my own!