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

“Data cannot be inserted because there is no matching record” error

Microsoft Access and linked tables to SharePoint lists are commonly used to manipulate data from SharePoint sites. Through the Microsoft queries, users can bulk update or insert data into a SharePoint list. I also did the same, but a few days ago I started to receive an error running an update query against a SharePoint list: “Data cannot be inserted because there is no matching record”.

In my case, removing some users from the site was the cause of this. People/group field type is actually a type of lookup field which is pointing to User Information List. But when you remove a user from site, which means removing it from the users list also, people/group field value is not reset to blank and it keeps continue storing the ID of removed user. So, when you try to run an update query you actually force the field to keep the value, but it fails because users is not anymore accessible in users list. If you recently removed users, go to your list and update records replacing removed users with other users, which are not removed from your site.