Results 1 to 6 of 6
  1. #1
    Trial4life is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    33

    Backup tables linked to SharePoint lists into local tables

    Hi everyone,
    I have an access database in which all of the tables are linked to SharePoint Lists.

    I'm trying to figure out which is the best way to write a macro that creates a copy of the whole .ACCDB file, in which all of the linked tables are converted into local tables.

    I've tryed with this code (it runs when the button "BAK" is clicked):
    Code:
    Private Sub BAK_Click()        
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Set db = CurrentDb
           
        For Each tdf In db.TableDefs
            If Not Left(tdf.Name, 4) = "MSys" Then
                DoCmd.SelectObject acTable, tdf.Name, True
                DoCmd.RunCommand acCmdConvertLinkedTableToLocal
            End If
        Next
    
    End Sub
    The problem is that the
    Code:
    acCmdConvertLinkedTableToLocal
    command converts the tables to local tables, and then there is no way to get the original linked tables back after the backup file is created (by copy-paste into a different directory).

    I've found also this command:

    Code:
        DoCmd.TransferDatabase acImport, "Microsoft Access",  CurrentProject.Path & "\DATABASE.accdb", acTable, "SharePointTable",  "LocalTable", 0
    but it doesn't seem to work properly.

    What is the best practice to do backup these tables?

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You would need to create an external new "Backup" database not use the current database.
    You could then simply loop through all the connected tables in the live database and copy them to the new backup database as local tables.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Trial4life is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    33
    That could work.

    Which is the command to open, read and edit a Database on a different .ACCDB file?

    I guess I should replace the

    Code:
    Set db = CurrentDb
    with something else.

    I've tryed with
    Code:
    Set db = OpenDatabase(CurrentProject.Path & "\DATABASE_BACKUP.accdb")
    but it keeps converting the tables in the original file, not in the backup file.

  4. #4
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    To get over the conversion of the linked tables, You would have to use a loop to a make table type statement, something like.

    Code:
             strSql = "SELECT * INTO [" & backUpDatabseName.strBUTable & "] FROM " & strLocalTable
                           Current.Execute strSql  ' Create the new table
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Trial4life is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    33
    Thank you for the help. I'm close to reaching the goal using that sql query, I only have 2 issues left:
    1) The directory name contains a square brackets, and I can't manage to get it to read the path properly (I've tried using both ' and " before and after the first square bracket, but it doesn't work);
    2) The exported table (the one in the backup file, not linked to SharePoint) contins a lot of unwanted columns that I don't even visualize in the main database file; I'm not sure why these fields are been exported; is there a way to limit fields to export?

  6. #6
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please feel free to try my utility http://forestbyte.com/ms-access-utilities/fba-fuze/ that would do what you want. It can be scheduled to be run by the built-in Windows task scheduler so you could do the backups at night or whenever is more convenient.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Local Tables vs Linked tables
    By Tuckejam in forum SQL Server
    Replies: 6
    Last Post: 04-23-2021, 01:17 AM
  2. Replies: 2
    Last Post: 10-08-2020, 11:33 PM
  3. Replies: 2
    Last Post: 03-02-2018, 05:47 PM
  4. Replies: 2
    Last Post: 07-14-2017, 05:37 AM
  5. Number format in tables stored on Sharepoint as lists
    By tim.cassey in forum SharePoint
    Replies: 0
    Last Post: 02-16-2016, 08:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums