Copy tables from linked to local

Topic

New Reply

I have an Access 2007 database with linked tables in a SQL 2005 database, and I want to create a local copy of the tables for testing purposes. It seems that this can be done in Access by copying the linked tables and pasting them with structure and data as local tables. If the local tables are then modified in the Access database, will the original linked ones in the SQL database be affected in any way?

Reply | Quote

Replies

The local copy will have no effect on the linked table. I’d give the local table a very different name so you don’t accidentally confuse the two.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Depends what you want to test, but once you make the local tables, any actions that change data will still be referring to the original linked data.

I think it would nbe easier to make a new Access back-end, and import the tables into that. Then you could switch back-ends, between the real one and the test one.

Reply | Quote

Thanks for your replies; however, they seem to be contradictory. Are you each referring to a different type of copy, one type that does not maintain a link between the original and the copy (which is what I want to achieve), and another type that does (which is what I want to avoid)?

Reply | Quote

I don’t see any contradiction between our answers.

You can copy the linked table (tblPeople) for example and name the copy (tblPeople_Local) The two tables are independent, and you can change anything you like in tblPeople_Local and it will have no effect on tblPeople.

But

Anything in your database that makes changes to tblPeople will still do so. Any forms bound to tblPeople will still be bound to it. Any queries will still be bound to it. Any code designed to change tblPeople will still change it.
So my question is :

What sorts of things do you want to test?

For testing purposes you need things to work on tblPeople_Local, but when you have finished testing you need them to go back to working with the linked tblPeople. I am suggesting two different back ends as a way of doing that. That would allow you to revert to testing mode any time you like.

Reply | Quote

I have an Access 2007 database with linked tables in a SQL 2005 database, and I want to create a local copy of the tables for testing purposes. It seems that this can be done in Access by copying the linked tables and pasting them with structure and data as local tables. If the local tables are then modified in the Access database, will the original linked ones in the SQL database be affected in any way?

Why dont you copy the SQL Server database to a test SQL Server database.

Then just relink from one to the other, then no need for Access BE’s or changing of table names.

Reply | Quote

Thanks for your further replies.

John, when you said:
– “once you make the local tables, any actions that change data will still be referring to the original linked data”,
– I read this as:
– “once you make the local tables, any actions [on the local tables] that change data will still be referring to the original linked data” [which is what I wanted to avoid],
– whereas it seems that you meant:
– “once you make the local tables, any actions [on the linked tables] that change data will still be referring to the original linked data [which is expected].

However, the reason for making the copy of the original database is because some records need to be modified or deleted in the copy, which will be moved to a development system and tested there, while the original will continue to be used on a production system.

As I am more familiar with Access than with SQL Server, my original question was about using Access to copy the tables into an Access database rather than using SQL server to copy the database and re-linking Access to the copy. Regarding the latter method, is there a simple way to make a copy of a database using SQL Server 2005 Management Studio?

Reply | Quote

…. Regarding the latter method, is there a simple way to make a copy of a database using SQL Server 2005 Management Studio?

Yes, there are actually several. One way is to do a backup of the production database, create an empty new test database and then restore the production database into the test database. Another way is to use the Import/Export tool where you create an empty test database and then import tables from the production database into the test database. You will need to play with the Mgmt Studio a bit to understand how it works, and you will also need to be aware that the Import process generally does not include the primary and foreign keys. Hope this helps.

Reply | Quote

Thanks for your reply. Creating a separate test database by restoring a backup to a different destination with a different name sounds like a good method, as it should avoid disrupting the production database. Can you direct me to a step-by-step procedure for doing this in SQL Server 2005 Management Studio?

Reply | Quote

This MSDN article tells you how to backup a database. This MSDN article tells you how to restore a database backup to a new database – presumably your test database.

Reply | Quote

Thanks for your reply. I have a production Access front end linked to the production SQL database, and I want to link a test copy of the same Access front end to the test copy of the SQL database so that I can continue to use the same relationships, queries, etc. in the test setup.

How do I make the test copy of the Access front end connect to the test copy of the SQL database – is there a setting that I can change, or do I need to add the test copy of the SQL database as a new data source in ODBC and re-link all the tables in the test copy of the Access front end?

Reply | Quote

It is possible to modify your production database so that it will switch back and forth between the test and the production version of the backend, but it involves writing a bunch of code to relink the tables. In any event you will need to create a new ODBC data source for the test version of the SQL Server backend. I usually just copy the production Access frontend, make some visual forms changes so I know I’m working in the test version when I open it, and then relink the tables (using the linked table manager) in the test frontend to the test SQL Server backend database.

Reply | Quote

It is possible to modify your production database so that it will switch back and forth between the test and the production version of the backend, but it involves writing a bunch of code to relink the tables. In any event you will need to create a new ODBC data source for the test version of the SQL Server backend. I usually just copy the production Access frontend, make some visual forms changes so I know I’m working in the test version when I open it, and then relink the tables (using the linked table manager) in the test frontend to the test SQL Server backend database.

Adding to what Wendell has said, i tend to change the database Title that is displayed at the top of the Access instance you are running. This Title i keep blank for the current system, but i put TEST in caps when using the test system.

Reply | Quote

Thanks for your reply. I created test copies of the SQL Server backend and the Access frontend, and a new ODBC data source representing the test backend, and re-linked the tables in the test Access front end by ticking the “Always prompt for new location” option in the linked table manager and selecting the new data source for all tables. This all seems to have worked OK. However, is there a way to re-link all the tables in one step; i.e., to select all the tables and then select the new data source once rather than for each table individually?

Also, when viewing a table in SQL Server 2005 Management Studio, how do I refresh the view to reflect changes made in Access? I tried the Refresh options on the View menu and on the table right-click menu, but the view was not refreshed, although it was refreshed when I closed and reopened the table.

Reply | Quote

In the linked table manager, you can tell it to select all tables, and as long as all of your tables are linked identically, then the re-link with a new data location will only prompt you once. However, if you have any linked tables where the linking information is different, then you will be prompted for the ODBC database source for each table – which can be a major pain if you have a hundred or more tables as many of our databases do. But once you get them all linked correctly, then the process is pretty smooth. If you have several data sources, there are some third-party addins that may make you life easier.

On checking to see if the data has been changed, I normally do that in Access, as you can do filters and such that are much easier in Access. In SQL, you may want to create a query if you are trying to check selected records. But if you open the table to view records, it doesn’t normally refresh to show you new or changed records, and you do have to close and reopen it.

Reply | Quote

Re the linked table manager: it is only prompting for the new source once now; not sure why it prompted for every table the first time.

Re refreshing the table view: noted thanks; I normally do work in the Access front end but just wanted to double-check in SQL server that the test frontend was updating the test backend, rather than the production one.

Many thanks for your help.

Reply | Quote