Copying a linked table

branston

Registered User.
Local time
Today, 10:46
Joined
Apr 29, 2009
Messages
372
Hi,

I have set up a bit of code to copy an exisiting table and change the name slightly:
DoCmd.CopyObject , "TblDetail_" & PWyr, acTable, "TblDetail"

(Where PWyr is an integer which is set earlier)

TblDetail is stored in the back end of my database, and this code is running in the front end. TblDetail is a linked table in the front end.

When running the code, it all seems to work fine. The new table appears with the correct name & data in it.
It has the linked symbol by it, so I thought it had worked perfectly, storing the table in the back end and automatically creating a link to it in the front end.

This is how it acts, anyway. You are unable to open the table in design mode as it tells you its stored somewhere else, and you can succesfully refresh the link etc

BUT.... If you go into the back end, the table doesn't seem to be there...

Any ideas?
 
Copying a table in the frontend doesn't do anything but create a second link to the same table in the backend. You need to create it in the backend first and then link to it in the front end. If this is something that isn't just a very rare thing (copying a table) then I suggest that the design is flawed because you should not have to be copying tables, adding tables, etc. except in RARE circumstances once the database is designed and running.
 
you an use a maketable query, to create a local copy of the data in a linked table
 
Thanks for your replies. The reason I am having to copy a table is that I have new data each week, but need to keep the old data for back up.

Gemma-The-Husky, so, if I use a make table query in the front end of my database, and told it to be made in the back end , would it automatically be linked?
Actually, thinking about it, I dont know if it even has to be for what I need...

Thank you
 
Also, just to check - if I moved the table to the front end and used the CopyObject code there it would make an actual seperate copy of the table, right?
 
The reason I am having to copy a table is that I have new data each week, but need to keep the old data for back up.
You really should have a date/time stamp so you can store the records TOGETHER - not in another table. And then you can pull whatever you need whenever you need it instead of creating tables for each week. Or you could have a single table for your archive and then append the current table records (including a date or date/time) and then you run a delete query to clear the current table and then import the new records.

But I would HIGHLY suggest moving away from this "make table" idea.
 
Thanks for your replies. The reason I am having to copy a table is that I have new data each week, but need to keep the old data for back up.

I just thought I would add my 2c here. I would first be suggesting that you follow the previous suggestions of only having 1 table. However I can imagine as a beginner, dealing with the "new" data and "old" data all in the same table is a problem and nightmare.

Thus I could offer some middle ground. You create one historical table in your BE. Link it to your FE. Then instead of copying data - you add the "old" data to this historical table. ON the date of addition to the historical table you add a date so that you can find it again.

This way you would have a table with only your new data and then a much bigger table with all your old data. As you get more experienced you will work out how to deal with the historical data table.
 
Thanks for all your suggestions. I shall have a re-plan.
 
Hi guys,

Just so you know I have now put all the data into 1 table and with a few extra columns so I can still run the reports by week & month, and it works like a charm.

Thanks for the suggestions, I really don't know why I didn't just do it like that in the first place!

:-)
 
Hi guys,

Just so you know I have now put all the data into 1 table and with a few extra columns so I can still run the reports by week & month, and it works like a charm.

Thanks for the suggestions, I really don't know why I didn't just do it like that in the first place!

:-)

I think its getting used to how access/any database works. Unlike a spreadsheet, you rarely see all the data displayed. most things are achieved by working on a subset of the data.

with regard to the maketable query, i thought it was for something like making a copy of linked data to be able to use off site.
 

Users who are viewing this thread

Back
Top Bottom