Copy current Access database with VBA

Access is still being in used by some companies, even is not perfect choice anymore. So, today I was requested to create a sort of back-up functionality for an Access database which contains linked tables to SharePoint. I have tried some methods, but all them are failed, including FileCopy method from VBA. Still VBA was solving my problem, but code was a little bit different.

Dim fDialog As Office.FileDialog
Set fDialog = Application.FileDialog(msoFileDialogSaveAs)

With fDialog

    If fDialog.Show() <> 0 Then
    
        Dim Sourcefile As String
        Dim Destinationfile As String
        Sourcefile = Application.CurrentProject.Path & "\" & Application.CurrentProject.Name
        Destinationfile = fDialog.SelectedItems(1)
    
        With CreateObject("Scripting.FileSystemObject")
            .copyfile Sourcefile, Destinationfile
        End With
       
      
    End If
End With

Code is also opening a file save dialog and you can choose the location where you want to copy the database.

Advertisements

Programatically import SharePoint list into Microsoft Access

As strange as it seems for some IT professionals, days when VBA was often used to Office application are not dead. In some environments, VBA programming remains the easiest way Microsoft Office users and developers can create solutions. And, as one of the strongest integration is between SharePoint 2010 and Microsoft Access, I have decided to approach one the main, which is how to import SharePoint into the local database from code.

Without more explanations, I thing code below is easy.

On Error GoTo error

' Variables definition here
Dim tbl As DAO.TableDef
Dim fld As Field


'Delete table if exists
For Each tbl In CurrentDb.TableDefs
    If tbl.Name = "Table name" Then
        DoCmd.DeleteObject acTable, "Table name"
        Exit For
    End If
Next


'Import SharePoint list
DoCmd.TransferSharePointList acImportSharePointList, "http://urlofthesite", "{A92FF376-FC2E-4390-BB03-CC47C756EB08}", "{F1AA137A-7C97-4C89-AB79-31447B77545C}", "Table name", True

'Create column a column to work with
CurrentDb.TableDefs("Table name").Fields.Append CurrentDb.TableDefs("Table name").CreateField("My local text field", dbText, 255)


exitOnError:
    Exit Sub

error:
    MsgBox Err.Description
    'In case of error, delete imported table to empty the memory
    DoCmd.DeleteObject acTable, "Table name"
    Resume exitOnError

End Sub

The code is deleting the table if already exists in the local database from the previous operation, import the list based on specified view and list id and create a local field to work with. Typically, developers create a local table, often containing more columns to work with than SharePoint list, import the list, append the list data into local table and update the data into local table, for the main reason they can have there as many field as they want without affecting the SharePoint list. But no need for this. Everything can be done in less operations. Once the SharePoint list is imported, developer can perform as many operations he wants without affecting SharePoint data, which in most of the cases is business critical.

The key is DoCmd.TransferSharePointList, which is very well documented here.