Copying a Database with linked tables

[EricB]

Hi Guys
I am using Access 2007 and I have two accdb databases with linked tables. As I am closing the Front End accdb database I want to make a copy of the Master Table accdb database to a storage file elsewhere for safe keeping.

"FileCopy" works fine for unlinked stuff but refuses to work if the tables are linked.

Do I have to unLink the Master Table, tables prior to using "FileCopy" or is there a way to copy and paste the linked Master Table while it's active?
Thanks
Eric

[NeoPa]

Hi Eric.

I believe FileCopy can only work with files that have no lock on them - IE. are not open by anything - even for reading. In my experience that works when all linked tables, as well as QueryDefs, Forms, Reports, and Recordsets of course, have been closed.

The COPY command from CMD.EXE can also work even with files locked for writing, as long as they have no lock preventing reading. You can trigger that using the Shell command from VBA but you have to use CMD.EXE with the /C or /K switches in order to do so.

[isladogs]

The standard approach is to make a backup copy of the backend database(s) containing the linked tables.

[NeoPa]

The question's about trying to do that controlled by the Front End though. IE How & when can that be done when connecting to the database stops various sorts of copying.

[isladogs]

Not sure I understand your point.
A backup copy of the backend database can easily be created using code from the frontend database

[NeoPa]

Not using CopyFile if/when you have any linked tables open - according to the OP's OP.

[isladogs]

I disagree with the comment.

A backup copy of a backup database can still be made when linked tables are open
Just to check my memory wasn't deceiving me I just ran a backup of an Access config BE with two linked tables open.
Before running the backup I edited records in each table as a further check
I used fso.CopyFile and it completed without error

[NeoPa]

Ah. Excuse me. I got that detail wrong - but I was only quoting what was already in the OP. Apparently :

EricB:
"FileCopy" works fine for unlinked stuff but refuses to work if the tables are linked.

Are you saying that isn't the case in your tests? Or have you not tested with what the OP was describing? Personally I simply took them at their word and explained the situation around locking of files in the hope that would help them with their issue.

[twinnyfo]

Friends,

I accomplish this using a FileSystemObject as IslaDogs describes. It can force a copy, even if the file is in use. I have never experienced any issues.

[isladogs]

Hi
I wrote a reply this morning but forgot to send it and it got 'lost' when my token expired.

I wrote the reply in post #3 as I have used FSO for many years to backup the backend database so I knew it worked whether or not any of the linked tables are open. Personally I see no point just copying an individual linked table.

However, I have also successfully tested that today using DoCmd.CopyObject whilst the linked table is open. However, 'copying' a linked table using that approach just creates a linked table in the destination database so that defeats the purpose of doing that as far as I am concerned.
Of course, it can then be converted to a local table in the destination database but that's a lot of fuss for no apparent gain in my opinion

[NeoPa]

I think there's some misunderstanding of the question here. My understanding of the original request is to make a copy of the BE database file that contains the Master Table. Thus copying a file. fso.CopyFile is a valid answer to how one could do that but the real question also includes why does the FileCopy statement not work in some circumstances. Clearly they've fallen over when trying to make a backup copy of the database so, as well as pointing at a way that can be done without using FileCopy (CMD.Exe / COPY / Shell), I also explained what they could do to ensure the FE doesn't interfere with the backup if they were intent on using FileCopy.

Your suggestion to use fso.CopyFile as an alternative probably makes more sense to most people than the (CMD.Exe / COPY / Shell) one. All ideas that can add to the general understanding are valuable.

PS. It was really good to see you in Teams earlier getting set up & ready for the Access DevCon 2021 on Thursday.

PPS. I so hate it when I lose work after a token expires. My sympathies.

[twinnyfo]

NeoPa,

The easy answer to the question as to why FileCopy won't work is that it doesn't work with files that are currently open/locked.

I am not smart enough to know why not, but, as you also agree and point out, fso.CopyFile does. Again, I can't explain why there is a difference, but there is. If the OP wants more than that, I cannot give it....

[isladogs]

Hi Ade
The OP seems to have gone AWOL since post #1 so perhaps we should leave this until @EricB chooses to respond.
As we have already discussed above there are better alternatives which will work even if the table is open.

I'm looking forward to doing my short presentation at DevCon about my JSON Analyse & Transform for Access app.
Inevitably the problem is working out what to exclude so as to cover the basics in about 10 minutes or so.
I was planning to do detailed planning for this today but my kitchen fitter drilled through the power cables by mistake first thing this morning.
As a result, I spent most of the day fixing the issue and restoring the power! Ah well, tomorrow is another day .....

[NeoPa]

Colin:
I was planning to do detailed planning for this today but my kitchen fitter drilled through the power cables by mistake first thing this morning.
As a result, I spent most of the day fixing the issue and restoring the power! Ah well, tomorrow is another day

So all in all you've had a bit of a lucky day then. Power out and token expired all running smoothly into one fantastic day.

I look forward to catching your presentation :-)

[isladogs]

LOL! All sorted now....& tomorrow is a detailed planning day
There's are many presentations I'm looking forward to at this year's DevCon.
However, I'm particularly interested in the Twin Basic session presented by Mike Wolfe based on work done by Wayne Phillips