Results 1 to 7 of 7

Thread: Restore table

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    We have an SQL database connected to Access via an ODBC link. Is it possible to copy a linked table from the SQL database into a local table in Access (as a backup) and paste it back from Acess to the SQL database later if need be?

  2. #2
    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
    We have an SQL database connected to Access via an ODBC link. Is it possible to copy a linked table from the SQL database into a local table in Access (as a backup) and paste it back from Acess to the SQL database later if need be?
    How often is this required?
    If daily then just include the SQL database as part of that daily backup.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. We already have all tables included in automatic daily backups. This requirement is for a one-off quick backup/restore; e.g., when running a delete or update query in Access half way through a day.

  4. #4
    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
    Thanks for your reply. We already have all tables included in automatic daily backups. This requirement is for a one-off quick backup/restore; e.g., when running a delete or update query in Access half way through a day.
    Why not do a copy table in SQL Server. Then if you have to restore the main table to the saved version delete the main table in SQL Server and copy the saved table back to the main table.
    I have done this before in SQL Server but have forgoten how to, Im ure someone will tell you how.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by Murgatroyd View Post
    Thanks for your reply. We already have all tables included in automatic daily backups. This requirement is for a one-off quick backup/restore; e.g., when running a delete or update query in Access half way through a day.
    Is this the same database you were describing in Multiple users (Access 2007)? If so, and if you have multiple users working in the database, deleting a large number of records or an entire table is a recipe for disaster. It would help to understand a bit more about your application. Is this mostly a heads-down data entry operation where records are stored in a temporary table and then processed in some fashion afterwards, making the data redundant. If so, you might think about an archive table in SQL Server, and a trigger to store the redundant records when they are deleted, and finally a process to delete the archived records after a specified period and you are sure they are no longer needed. If there is some other issue than what I've described, we would be happy to make suggestions, but copying a table into Access is pretty inefficient compared to simply running a make table stored procedure or an append procedure to add the records to an existing table. The combination of the SQL Server log file and a good maintenance strategy makes it almost impossible to loose data because of a system crash or most external faults, so a backup in Access seems unnecessary, and adds more complications.
    Wendell

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. In Windows Explorer, I can use basic copy, paste, delete and rename functions to do a simple backup and restore of a file if need be. I was hoping that something similar would be possible with a table in Access or SQL Server Management Studio.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Both Access and SQL Server store their data in a single file, so doing a copy/paste doesn't work as it does with files and with other Office documents.
    Wendell

Posting Permissions

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