Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    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?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    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)?

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by Murgatroyd View Post
    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.

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    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?

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by Murgatroyd View Post
    .... 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.
    Wendell

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    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?

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    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?

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by WendellB View Post
    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.

  14. #14
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    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.

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •