How to copy current db

[Bill]

Is there a way in code to copy the current
database. FileCopy will not allow one to
copy a currently open file. (I'm wanting to
archive the current database)

Thanks,
Bill

 

[John W. Vinson]

Is there a way in code to copy the current
database. FileCopy will not allow one to
copy a currently open file. (I'm wanting to
archive the current database)

Thanks,
Bill

Use Windows copy tools, not Access. It's like lifting yourself by your own
bootstraps to try to copy a database that you're already in.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

John,
I see your point. However, the application I'm
working on needs to provide an archiving
function to the end user from within the application
itself. I'm only talking about the backend, of
course.....does that change your assessment of
what I'm asking?
Bill

 

[John W. Vinson]

John,
I see your point. However, the application I'm
working on needs to provide an archiving
function to the end user from within the application
itself. I'm only talking about the backend, of
course.....does that change your assessment of
what I'm asking?

I haven't actually used it, but it is possible to compact a database in code
into another name. That may be something you could use.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

The current application has exclusive use of
the backend, so it occurred to me that perhaps
I could close the backend, copy it to another
directory and then re-open???
Bill

 

[Bill]

John,
I've been trying to use the closecurrentdatabase
with the idea of closing, copying and re-opening,
but without success. Your idea of compacting
might very well work, as I have other functions
that invoke WinZip to create a zip file and send
those files out via and e-mail attachment. However,
an un-packed archive file better serves the end user
if and when the situation arises that it be accessed
from the current application.

Anyway, I know I can copy/paste from Windows
Explorer even when the backend is open. Do you
by chance know how to invoke the Windows
Explorer copy/paste functionality from VBA?

Bill

 

[Allen Browne]

You cannot safely copy an Access database while it is in use. If there are
multiple users, and someone could be connected to the back end on another
computer, you cannot merely copy the back end file without the possibility
that the copy if corrupt.

An alternative is to work your way through the tables to make a backup of
the data table-by-table, and just ignore any tables that are currently
locked. This means the back up may be incomplete. It also means that the
backup itself may be incompatible (e.g. if you copied a table of related
records just as someone was deleting a record on the ONE side of the join
which then doesn't appear when you copy the primary table.)

If you are prepared to take those chances (on the argument that any back up
is better than none), I can't recommend it, but here's an example of how
it's done:
http://allenbrowne.com/unlinked/backup.txt

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

 

[Bill]

Hi Allen,
How are things in Perth?

The situation is exclusive use to the current
user, so the exposure in this dedicated
single-user environment is nill. It is only in
a situation such as this that I would even
consider "archiving" and entire backend
while it is open.

Bill

 

[Ken Snell]

I have written and used an ACCESS database that can be used to compact and
backup a backend file. I've used this by scheduling the opening of this
database via Windows Scheduler, and it checks to be sure the backend file is
not in use, and then compacts and makes backup copy of the backend file.

I can post this sample database on web if you're interested in it.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

 

[Bill]

Thanks for the offer Ken. The archiving function
at hand here includes some somewhat major
"house-cleaning" in the database, so there's more
to what I need than just making a copy of the DB.
To bad I can't "stack" Windows commands that
would essentially close the app, run a utility and
re-start the app like we could on IBM VM.
Bill

 

[John W. Vinson]

Thanks for the offer Ken. The archiving function
at hand here includes some somewhat major
"house-cleaning" in the database, so there's more
to what I need than just making a copy of the DB.
To bad I can't "stack" Windows commands that
would essentially close the app, run a utility and
re-start the app like we could on IBM VM.
Bill

You can, actually; you could put a Macro in the database which does all this
housecleaning (or launches code to do so). Scheduler or a .bat file can open
Access using the /x macroname command line switch to run this code.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Mike Painter]

John,
I see your point. However, the application I'm
working on needs to provide an archiving
function to the end user from within the application
itself. I'm only talking about the backend, of
course.....does that change your assessment of
what I'm asking?
Bill

You can't do anything with open files and using Windows to copy the files or
to use a backup routine is probably a better way.

You could transfer the table contents by attaching a backup database and
updating or appending the data to tables in that database.

As for archiving in general, unless you are adding massive amounts of data
every day it will be a long time before there will be any performance issues
in Access.

If you want to archive them, mark them archived and use a query to get at
the unachieved records.

 

[Tony Toews]

The current application has exclusive use of
the backend, so it occurred to me that perhaps
I could close the backend, copy it to another
directory and then re-open???

Yes, I do that all the time. You will need to close all open forms
with a record source and all unbound forms that have a combo box or
list box bound to a row source.

Actually what I do is create and open a form with a label which states
"Please wait" and run through the open forms and reports collection
closing all of them other than the Please wait form.

I also ensure that, if any are used, all recordsets and global
database variables are closed. You, as the developer, can verify
this by ensuring the backend LDB file vanishes.

Then do whatever you want to the backend and rerun your startup code
to open the appropriate forms, etc.

See my Backup, do you trust the users or sysadmins? tips page for more
info.
http://www.granite.ab.ca/access/backup.htm

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

 

[Bill]

Thanks everyone, your thoughts, time and suggestions
are greatly appreciated.

I opted to build a Windows bat file on the fly, save it
and use SHELLWAIT to run it. The application is
purposely designed such that there are no controls
bound to the backend and, with a single user, the
backend I/O is totally quiesed. Were this not the
case, I would never be doing anything like this with
the backend open.

Thanks again,
Bill