470,843 Members | 2,000 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,843 developers. It's quick & easy.

Copying a Database with linked tables

1
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
Apr 17 '21 #1
14 3472
NeoPa
32,311 Expert Mod 16PB
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.
Apr 17 '21 #2
isladogs
358 Expert Mod 256MB
The standard approach is to make a backup copy of the backend database(s) containing the linked tables.
Apr 17 '21 #3
NeoPa
32,311 Expert Mod 16PB
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.
Apr 17 '21 #4
isladogs
358 Expert Mod 256MB
Not sure I understand your point.
A backup copy of the backend database can easily be created using code from the frontend database
Apr 17 '21 #5
NeoPa
32,311 Expert Mod 16PB
Not using CopyFile if/when you have any linked tables open - according to the OP's OP.
Apr 18 '21 #6
isladogs
358 Expert Mod 256MB
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
Apr 18 '21 #7
NeoPa
32,311 Expert Mod 16PB
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.
Apr 19 '21 #8
twinnyfo
3,653 Expert Mod 2GB
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.
Apr 19 '21 #9
isladogs
358 Expert Mod 256MB
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
Apr 19 '21 #10
NeoPa
32,311 Expert Mod 16PB
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.
Apr 19 '21 #11
twinnyfo
3,653 Expert Mod 2GB
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....
Apr 19 '21 #12
isladogs
358 Expert Mod 256MB
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 .....
Apr 19 '21 #13
NeoPa
32,311 Expert Mod 16PB
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 :-)
Apr 20 '21 #14
isladogs
358 Expert Mod 256MB
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
Apr 20 '21 #15

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by MeganF via AccessMonster.com | last post: by
3 posts views Thread by news.giganews.com | last post: by
2 posts views Thread by troy_lee | last post: by
1 post views Thread by Jonathan Wood | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.