468,294 Members | 1,913 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,294 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
3 Weeks Ago #1
14 2944
NeoPa
32,070 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.
3 Weeks Ago #2
isladogs
245 Expert 128KB
The standard approach is to make a backup copy of the backend database(s) containing the linked tables.
3 Weeks Ago #3
NeoPa
32,070 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.
3 Weeks Ago #4
isladogs
245 Expert 128KB
Not sure I understand your point.
A backup copy of the backend database can easily be created using code from the frontend database
3 Weeks Ago #5
NeoPa
32,070 Expert Mod 16PB
Not using CopyFile if/when you have any linked tables open - according to the OP's OP.
3 Weeks Ago #6
isladogs
245 Expert 128KB
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
3 Weeks Ago #7
NeoPa
32,070 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.
3 Weeks Ago #8
twinnyfo
3,650 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.
3 Weeks Ago #9
isladogs
245 Expert 128KB
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
3 Weeks Ago #10
NeoPa
32,070 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.
3 Weeks Ago #11
twinnyfo
3,650 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....
3 Weeks Ago #12
isladogs
245 Expert 128KB
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 .....
3 Weeks Ago #13
NeoPa
32,070 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 :-)
3 Weeks Ago #14
isladogs
245 Expert 128KB
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
3 Weeks Ago #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
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.