473,289 Members | 1,953 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

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 4015
NeoPa
32,554 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
454 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,554 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
454 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,554 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
454 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,554 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
454 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,554 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
454 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,554 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
454 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

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

Similar topics

1
by: laststubborn | last post by:
Hi All; We are going to change our application server. We will copy all of our Database from Mic 2000 Server OS to Mic 2003 Server OS. I found an article that how to move all the folders from...
1
by: Jerry Harris | last post by:
Hello, After copying a database to a new computer, a header using the =Format$ stops working. Instead of displaying the field, the report prompts me to "Enter paramater value for Format$. Any...
3
by: MeganF via AccessMonster.com | last post by:
Hi. I've been working on a database for a while now between my desktop and my laptop. Each time I copy the files, I go into the Linked Table Manager and update my path to my linked databases....
3
by: news.giganews.com | last post by:
Does anyone know if there's an easy way to export or copy the relationship structure from one mdb to a new one? The relationships in the db are time consuming to reproduce. I am trying to make a...
7
by: bill.brennum | last post by:
Hi, Have a number of Access Databases that I inherited and want to zip a few of them. My concern is that other active Microsoft Applications may be linking to the database or its tables. Is...
2
by: aoao | last post by:
I need to make a new database at the beginning of every year but have forgotten how to do this quickly and efficiently. I have a database with about 10 tables, 10 forms related to the 10 tables...
16
by: egateway | last post by:
I'm attempting to copy an exisiting Access Database file (.mdb) to be used as beginning design for a new database; however Design View, Save, and other controls are disabled; why? Platform:...
0
raveenrs
by: raveenrs | last post by:
Hi, I'm new to DB2, having to move from a flat-file non-sql database to DB2. I'm currently trying to work out how to convert all our "custom" linux scripts to be compatible with DB2. Currently,...
2
by: troy_lee | last post by:
What is the best way of copying all the records from a linked table into a replica table that is local on my computer? This is for development work at home where I can not access the main table. Is...
1
by: Jonathan Wood | last post by:
Greetings, I've copied a significant database from an existing Website to a new one. The database includes ASP.NET membership data. I noticed that I am unable to successfully log on using any...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.