Access Database and SharePoint
- Dan Jakubiak
Faculty, staff, and Students may use SharePoint to backup Access Database files. In order to avoid edit conflicts on these database files, users must follow the procedure below to check the file in and out.
Although Access Database is a Microsoft product, it is not a part of the Microsoft365 suite and therefore does not support real-time like collaboration like Word, Excel, and PowerPoint do.
Checking out a file prevents all others from editing it. Others may still view the file.
When a file is checked out, it will have a lock icon.
Consider using Microsoft Excel instead of Access Database if you have multiple editors. There are online tools that convert Access Database files into Excel files.
Or, if there is only one user who edits the Access Database file, have that user check the file out to themself using the instructions below and then stop following this guide.
Why do Access Databases have their own help guide?
When UConn members used Access Database files in the past on the Q Drive, all users were always accessing the same file. This is because the Q Drive was a local network resource; it existed physically on campus..
However now, with SharePoint, the database file is no longer on our local network but instead it is in the cloud. When users in your department need to edit the file, their computer downloads a copy of the file from SharePoint onto their computer. Next, the user makes their changes. After that, the changes are uploaded back to the cloud. The next user then downloads the file from the cloud, with those changes, to their computer.
The nature of SharePoint allows multiple users to have a file open at the same time, unlike the Q Drive. If a user makes changes to the database file, the OneDrive App (both SharePoint and OneDrive use the same app) will periodically upload your changes to the cloud. If another user has the same file open, the OneDrive App will be unable to download those changes (since the file is open). If this does happen, there will be a conflict in SharePoint and SharePoint will create two files in order to preserve both users' changes. To prevent people from working on the file at the same time, users need to “check out” the file to themselves.
How do we handle these database files?
User navigates to the SharePoint website to check out the database file.
User edits the files and saves their changes.
User waits for their changes to sync with SharePoint.
User checks in the file.
The second user repeats this process starting at Step 1.
The file must be downloaded, edited, and then uploaded again. The next user must then download the changes before making their own changes.
It is your turn to edit the file:
Navigate to your SharePoint site. You may use s.uconn.edu/sharepoint as a shortcut to your SharePoint homepage.
Find the Access Database file and right-click on it.
Choose More then Check out. Leave or close the browser.
Open the file from within File Explorer (Windows) or Finder (macOS) and make your edits. Save the file as you normally would.
It is now someone else’s turn to edit the file:
When you are done editing the file and you would like to allow someone else to edit the file, save your changes and close the file.
Click on the OneDrive cloud icon to ensure the file uploads to SharePoint. The file will appear in the list of changes with “Uploaded” when it has finished processing.
Navigate to your SharePoint site. You may use s.uconn.edu/sharepoint as a shortcut to your SharePoint homepage.
Find the Access Database file and right-click on it.
Choose More then Check in.
Have the other user click on the OneDrive cloud icon to check to see that their OneDrive app has downloaded the updated file.
The file will appear in the list of changes with “Downloaded” when it has finished processing.
Note: if they have not previously opened this file on their computer, then it will not appear in the list, and they are all set to open it from within File Explorer (Windows) or Finder (macOS) as the latest version will be downloaded when they double click on the file.
Tell the other user it is now safe to open the file from their File Explorer (Windows) or Finder (macOS) window.
Related Guide