Backup Linked BE Tables

  • Thread starter Thread starter Mike P
  • Start date Start date
M

Mike P

I have my database split into a FrontEnd (FE) containing all the modules,
forms, queries, etc., and a BackEnd (BE) containing all the tables. The BE
is linked. I am trying to write some VB code that will automatically back up
the BE when the user is running the application. This is working in Access
2003 however, I am migrating to 2007 and when trying to backup the BE, I get
an error that the “file is in useâ€â€¦ which it is. This is a single user
system.

I am thinking I have to unlink the tables, backup the BE, then relink… but
have not figured out how to do this. So far the code I have is.
strTmpFileName = strBackUpDir & "\" & strFileName & ".accdb"
Application.CompactRepair strCurrentDB, strTmpFileName

It is failing on the Application.CompactRepair since the FE is connected to
the BE I am trying to backup (and compactRepair while I am at it).

Any ideas?

Thanks,
Mike P.
 
I do this type of backup all the time.

Keep in mind that means that if your form is bound to a table, then you'll
not be able to accomplish this task.

Now, here is something even more interesting.

You cannot call (open) a form that is unbound from a bound form, and have
that 2nd form close the 1st form to close. While the actual first form will
be shown to be closed via code, the program execution stack and memory used
by MS access will have NOT YET released its connection to the back end.

eg:

from1 - bound (top of stack)
from1 - opens form2 (form1,form2)
form2 - closes form1 ( ,form2)

If you look closely at the above I put a ( ,form2) after form1 been closed
by form2 well form one has been closed. However, in the terms of the
execution and program code stack in access, form2 is still on top of the
code (memory etc), and the resources used by form1 CAN NOT be released until
a form two is actually closed.

To make a long story short, what this means is that you have to close all
forms and all record sets, and then run your code. This pretty much means
that you're going to have to use a custom menu, or something else that gets
run independent of any bound forms you have, and any of those forms that
are bound MUST BE closed before you execute that code (and as pointed out as
above, that code you call CAN NOT called this code from a bound form in any
way at all because you are unable to release the resources used in the above
program stack. you can of course opened manually to an unbound form, and
that I'm bound form can run code that closes bound forms, and you will
achieve a complete closure of links to the back end.

so either use a macro, or go to code directly etc. I got around the program
stack limitation by adding a custom menu bar, and in that custom menu, I
simply call some code in a standard code module that actually does the
closing of all open forms. The code then goes on to do the standard
compaction, which also of course makes our backup and copy of the backend
database at the same time.

So, once again you must ensure that all record sets and bound forms are
closed , and then you should be able to run that compaction code you have.
Keep in mind the second significant issue I pointed out, and that is that
any form that is bound is unable to call the compaction routines, and this
*includes* the scenario in which the 2nd form attempts to close all forms.

If someone here has found a work around for the above problem, then I'm all
ears. it's been a little while since I've done I have done the above
testing, and I suspect you might be able to launch the second form that is
unbound, and then have a button on that 2nd form that closes the calling
form, and does the compaction (copy).

Of course the other significant issue is that virtually all of the
developers here have been preaching and teaching for good many number of
years that you'll always want a live connection to your back end database,
and failure to do so will result in significant performance problems. so
I'll have to assume that you have some connection code that opens up the
back end database and *keeps* it open at all times. So, you'll want to
ensure you close that global recordset, or database object.
 
Hello Mike P ---

What are you doing it in A2003 that is different in A2007? ...

......

Anyway, you can do this with an open file, but you wont be useing the new
file that is created. The following code is merely used just to back up the
back end ...

Public Sub BackupBE(strDestination As String)

Dim oFSO As Object
Dim strSQL As String

'Flush the cache of the current database
DBEngine.Idle

'Create a file scripting object that will copy the db. I use the
scripting
'object because the VBA.FileCopy will not work on an open file
Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.CopyFile _
Split(CurrentDb.TableDefs("ALinkedTableName").Connect,
";DATABASE")(1) _
, strDestination
Set oFSO = Nothing

'Compact the new file, ...
Name strDestination As strDestination & ".cpk"
DBEngine.CompactDatabase strDestination & ".cpk", strDestination
Kill strDestination & ".cpk"

End Sub

Hope that helps! ...
 
Back
Top