Re: Backup Linked BE Tables

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.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx


.