Feedback Restore Database (1 Viewer)

[Borror]

Thanks and big shout out to all those who are helping me.


Access Objects are the Tables, Queries, Forms, Reports, Macros, Store Procedure and Functions that builds up a Database. What am looking for is the complete deletion of the Access Objects in the database and replaced by the Access Objects from the BackUp file.


Arnelgp Codes below imports Tables from the Backup file and add up to the blank Tables in the Database which produces a bug dialog box with the inscriptions " Tables are already in the objects". This means that there should a total deletion to the existing Access objects and replaced by those Access Objects in the Backup file.


Please, help me edit this.


Private Sub do_restore()
Const SQL_STRING As String = "SELECT * INTO <table> FROM <table> IN '<externDB>';"
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim strSQL As String
'* open External database
Set db = OpenDatabase(Me.txtFile, False, False)
For Each td In db.TableDefs
If Left(td.Name, 4) <> "MSys" And Left(td.Name, 1) <> "~" Then
strSQL = Replace(SQL_STRING, "<table>", td.Name)
strSQL = Replace(strSQL, "<externDB>", Me.txtFile)
CurrentDb.Execute strSQL
End If
Next
db.Close
Set db = Nothing
Application.RefreshDatabaseWindow
End Sub

 

[Ranman256]

You wouldn't normally do that.
The backup would be a copy of the db in use, so just copy it back.

 

[The_Doc_Man]

What am looking for is the complete deletion of the Access Objects in the database and replaced by the Access Objects from the BackUp file. What am looking for is the complete deletion of the Access Objects in the database and replaced by the Access Objects from the BackUp file.

If you are trying to do replacement from an external database to your currently active database, the code you showed would ONLY replace table content. You CANNOT perform a total object replacement into the currently active database. Here is why.

Your code, whatever it is, will run under the control of some object (remember, one of the things you want to replace) that provides the supporting infrastructure for the execution thereof. This infrastructure will either be a form and its class module or it will be a macro that performs a RunCode action against something in a general module. One or the other WILL be the case because that's how you run code in Access.

As long as tables and queries aren't open at the time, you COULD actually delete and replace these objects at the object level. Ditto, reports and any closed forms. However, if any form is open, it is also LOCKED. You CANNOT replace that object while it is open and if you DID close it, you would terminate the code that was running under it. Your replacement process would stop when it hit that active form object, leaving you with a half-replaced set of data.

If you are using a macro and some code in a general module, the macro and the module are LOCKED and CANNOT be replaced at that time. Again, if you DID succeed in replacing the macro, you would stop the code because you are yanking the infrastructure out from underneath that code. You would end up with a half-replaced database, which would be unusable.

But let's look deeper. Let's say you DID manage to run code to replace all objects inside the active database. I'm am 99.99% sure you can't, but let's say you did.

The MOMENT you destroy the previous tables and queries in order to replace them, ALL repeat ALL of your forms and reports will break because the bound forms and reports will lose their data references regardless of what you used for the form or report .Recordsource data property. Doesn't matter whether the .Recordsource was a literal query or the name of a pre-defined query or the name of a table. All will have the same effect. You would have to go back into design mode to re-establish the data connections. I've done this manually (i.e. deleted and replaced a table) and it doesn't work well (or at all) until you go in and re-establish the implied connections.

Just because you deleted and rebuilt table XYZ doesn't mean that the internal object numbers will stay the same. If you looked closely enough, you would realize that the internal tables of Access are ALSO normalized; this means that recordset producing objects work off of a table or field's object numbers, and you just changed them all around.

I would bet that you would not EVER be able to assure the same object numbers were assigned to each object, because that is an internal function of Access and depends on factors that are not normally under your control, nor should they be. And that means all of your references are now scrambled.

In fact, if you destroy the tables first, the QUERIES will also break because THEY also reference tables by an internal reference number. You CANNOT guarantee that you'll get the objects back in the right numbers, so that means your reloaded database is STILL broken. Maybe worse than it was before you started this ill-fated process.

If you know that backup copy X is the one you want, do a manual COPY. Takes seconds and if the backup is good, the operation will easily succeed. Doing it any other way is working too hard and fighting what I would consider an inevitable manual recovery step in design mode for everything.

Forgive me if I seem impatient but you've pursued this idea of yours across multiple posts. The fastest, most accurate and reliable method to restore a database is to copy the whole file in one action and be DONE with it. You can do a lot of really crazy things with Access, but you cannot force Access to violate its own internal locking system. And that is what you are trying to do with this total object replacement strategy.

 

[The_Doc_Man]

As an addendum: You asked about this question and Arnel gave your some code but you may have missed something in passing. In this thread https://access-programmers.co.uk/forums/showthread.php?t=300710 Arnel told you you would need a 2nd-level database to accomplish the desired replacement. He knows as I do that a database cannot replace itself while it is running. Look at his last post in the referenced thread.

What I said in my previous post stands. But if you had a second database to drive this process, it would be trivial to simply use that second database to copy the file you think you need. What you can't do from inside an Access app, you CAN do from OUTSIDE in another Access app.

In this thread https://access-programmers.co.uk/forums/showthread.php?t=300807 Arnel pointed out that if you were just restoring TABLES (not all objects) that you could use that SQL sequence that does the INSERT INTO from an external source. He CLEARLY did not suggest you could replace anything else.

 

[GinaWhipp]

I think you should REread this...
https://access-programmers.co.uk/forums/showpost.php?p=1585956&postcount=5

You cannot do that within the same file. You must use two databases.