Make an unlinked copy of a backend

[George-Bowyer]

I have a back-end db in access 2010, linked to a set of sharepoint tables on office365.

I want to make a completely unlinked copy of the db to store a "snapshot" of the data on a fixed date.

I have tried "save as" using the "Save as Local Database" option, but for some reason, it still saves as linked tables.

Can anyone please tell me how to go about it?

Thanks.

George

 

[gemma-the-husky]

is this any good

this backs up a table to a csv file. I use it to back up all my linked tables.
I was looking for something that would output the correct number of dps for real number formats.

 pseudocode
 for each tdf in currentdb.tabledefs
   if tdf.connect<>"" then
       call backuproutine
   end if
 next

 'so this is now the backup version that I use
  
  
 'modified and working
'http://stackoverflow.com/questions/6154812/how-do-i-keep-the-necessary-decimal-places-when-using-the-docmd-transfertext-com
 
 Public Sub ExportToCSV(TableName As String, _
          strFile As String, _
          Optional strQualifier As String = vbNullString, _
          Optional strDelimiter As String = ",", _
          Optional FieldNames As Boolean = False, _
          Optional tablecount As Long = 0)
          
          
    Dim Items(2) As Long

     'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
    'Set references by Clicking Tools and Then References in the Code View window
    '
    ' Exports a table to a text file.
    ' Accepts
    ' Tablename: Name of the Target Table or Query
    ' strFile: Path and Filename to Export the table to
    ' strQualifier: specifies text qualifier (typically a double-quote)
    ' strDelimiter: String Value defaults to comma: ,
    ' FieldNames: True or False
    '
    'USAGE: ExportToCSV TableName, strFile, Chr$(34), ",", True
    On Error GoTo errhandler
     Dim intOpenFile As Integer
    Dim strSQL As String, strCSV As String
    Dim fld As DAO.Field
     'Close any open files, not that we expect any
    Reset
     'Grab Next Free File Number
    intOpenFile = FreeFile
     'Open our file for work
    Open strFile For Output Access Write As #intOpenFile
     'Write the contents of the table to the file
    'Open the source
    
    Items(1) = DCount("*", TableName)
    Items(2) = 0
    
    strSQL = "SELECT * FROM " & TableName
     With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
       'Check if we need Field Names
      If FieldNames Then
        For Each fld In .Fields
          strCSV = strCSV & strDelimiter & strQualifier & fld.Name & strQualifier
        Next fld
        ' remove leading delimiter
        strCSV = Mid$(strCSV, Len(strDelimiter) + 1)
        'Write to File
        Print #intOpenFile, strCSV
      End If
       'Write records to the CSV
      Do Until .EOF
        strCSV = ""
        For Each fld In .Fields
          If fld.Type = dbText Or fld.Type = dbMemo Then
            If Len(Nz(fld.Value, "")) > 255 Then
                strCSV = strCSV & strDelimiter & strQualifier & Left(fld.Value, 255) & strQualifier
            Else
                strCSV = strCSV & strDelimiter & strQualifier & fld.Value & strQualifier
            End If
          Else
            strCSV = strCSV & strDelimiter & fld.Value
          End If
        Next fld
        
        ' remove leading delimiter
        strCSV = Mid$(strCSV, Len(strDelimiter) + 1)
        'Eliminate Back to back strQualifiers
        If Len(strQualifier) > 0 Then
          strCSV = replace(strCSV, strQualifier & strQualifier, "")
        End If
        'Write to File
        Print #intOpenFile, strCSV
        Items(2) = Items(2) + 1

        DoEvents
        .MoveNext
      Loop
       .Close
    End With
 ExitHere:
      'Close the file
      Close #intOpenFile
       Exit Sub
 errhandler:
      With Err
         MsgBox "Error " & .Number & vbCrLf & .Description, _
           vbOKOnly Or vbCritical, "ExportToCSV"
      End With
       Resume ExitHere
    End Sub

 

[smig]

The only way I can think of will be to copy all data from linked tables into local ones.

Create table query will be great.

However this will not let your app. Use the unlinked tables, since all queries, forms and reports are using the linked ones.

 

[George-Bowyer]

Thank you for your answers.

What seems to have worked relatively simply, is just to make a copy of the front-end and then go through and click "Convert to local Table" on all of the tables.

A bit time consuming, but gets what I wanted, I think.

Thanks again,

George

 

[Frothingslosh]

George, do you want to make a copy of JUST the backend, or of the full database? Using VBA, you can use FileCopy to make a copy of just the backend, or Scripting.FileSystemObject if you need more options.

If you want to do it manually, then you are probably already going about it the best way.

 

[George-Bowyer]

It's just the data that I want to "snapshot".

The database I back up by simply making copies.