In today’s topic we will discuss about one very important aspect of Access database and SharePoint i.e how to Import/Export/publish Access Database To SharePoint. So, go through the complete post to learn advantages and necessity of doing so. And most importantly how to convert/move/publish Access database to SharePoint.
MS Access Database And SharePoint:
SharePoint is basically a document collaboration and management tool proposed by Microsoft. This content management system is comprised of a set of technologies having compact integration with Office 365 and convenient document management aptitudes.
One can easily use Access 2010/2013/2016/2019 and Access services on SharePoint to create web database applications. Ultimately this application will help you to perform the following tasks.
- To have safe and secure access to your data.
- Share Access database data all over the internet or to an organization.
- To make a database application that doesn’t require any MS Access application to use.
Access 2010/2013/2016/2019 and Access Services gives a platform to make databases that you can use on the SharePoint site.
Using an Access database and Access services you can design/publish a web database. Users who are already having an account on the SharePoint site can make use of the web database in a web browser.
What Are The Advantages Of Linking Access Database And SharePoint?
Before moving Access database data to SharePoint it’s important to know what’s the benefit of doing so.
Retrieve data from the Recycle Bin
In the SharePoint site, it’s quite easy to look for records and data which were deleted accidentally.
You don’t need to trouble a lot for viewing the list on the SharePoint site. In the quick launch, you just need to click View All Site Content.
Work offline :
You can make use Access database to work offline with linked data of SharePoint lists. This feature works great to work flawlessly also when SharePoint is not accessible.
After the availability of SharePoint, synchronize all your changes and resolve any kind of conflict.
The issue tracking template of access interacts directly with the SharePoint site’s issue tracking list.
Manage SharePoint list permissions:
You can check SharePoint permissions on linked lists to ensure that you don’t inadvertently access private data. SharePoint allows you to set different levels of permission. So, now you have the option to allow access permission to users.
If you need to do several changes to a list of data, like inserting lots of comments, fetching the status fields, and bringing data up-to-date. All these bulk editing tasks are so easy to perform in Access.
If you are using Access for the creation of reports present in linked list data. To broadly share these reports as PDF files, just export them to the SharePoint library. This SharePoint Library works as a report center. And it opens the PDF file in Adobe Acrobat reader for simple searching, paging, and reading.
Use SharePoint to create an Access table:
You can make use of the Access table which is based on a SharePoint list. It’s quite an easy and convenient way to make a table having similar fields of tasks, events, issues, contacts list. Access will make a corresponding table with the name UserInfo.
Common Scenarios For Using Access Database To Sharepoint List
Here are some common scenarios discussed below in which you need to import/export Access database to SharePoint list.
- When your department is using an Access database for report and query; whereas using SharePoint for team communication and collaboration. Well linking these two applications together, allows both SharePoint and Access database users to modify data. So, you can always get and work with updated data.
- Previously you are using an Access database and now you need to start using SharePoint. you have shifted several of your data and linked tables from you’re Access database to the SharePoint site. After linking these applications, you don’t need to create any local tables. You just have to make SharePoint lists, and link up these lists from your respective databases.
- When you need to continue with the SharePoint sites to store your list but also need to work with updated data present within Access to print reports and execute queries.
How To Import SharePoint List To Access Database?
Import option helps to perform recurrence of import tasks further in the future without going through the complete steps of the Import wizard every time.
Why Do You Need To Import SharePoint List To Access Database?
Some common reasons behind the importation of SharePoint list to Access database:
Suppose you want permanent shifting of data from the SharePoint List to Access Database. in that case, you need to import the list into Access and then make the deletion of the SharePoint list.
The department in which you are working is using Access but you are pointed to use the SharePoint list for merging your data into one single database.
Steps To Import SharePoint List to Access Database
- Firstly, you need to open your Access database in which you need to keep off the imported data. But if you don’t need to store data in a previously existing database then make a blank database.
- As per the Access database version you are using the location of the import/link text wizard may slightly differ. Check out the steps according to your Access database version:
For Office 365 or Access 2019 user:
- Go to the External Data tab, then choose the Import & Link group> tap to the New Data Source > From Online Services > SharePoint List.
For Access 2016/2013/2010 User:
- Go to the External Data tab, then choose the Import & Link group> tap to the More button this will open a drop-down from this drop-down menu choose the SharePoint List.
- After performing the above steps, Get External Data – SharePoint Site dialog box will open up on your screen.
- You need to specify the address of the source site, in the opened wizard box.
- Choose Import the source data into a new table in the current database option, and then tap to the Next.
- The wizard will display a list, from this list make the selection of the list which you want to import.
- From the Items to Import column, choose the view you need to apply for each of your chosen lists.
- Checkbox with label Import display values instead of IDs for fields that lookup values stored in another list controls, offers these two tasks:
- Choosing the checkbox option will help to import the values as a portion of the field itself.
- Un-check the checkbox, for the destination field to check out other tables for values. Performing this will copy down the IDs of the display value rows into the destination field. Actually, IDs are quite important for a proper definition of the lookup field in Access.
- At last tap to the OK.
Now you will see that the Access database will import complete of your SharePoint list. After this, it will display the status of the performed operation on the wizard ending page.
If you need to perform the import steps again in the future. Then you just have to save the details as import specification. Access database won’t overwrite the table already present in the database as part of the import operation. Also, you can’t append the contents of the list or view the existing table.
How To Export Access Database To Sharepoint List?
Migrating Access data to SharePoint is the best way to create a back-end database. but in this particular case, data is kept in SharePoint lists. Whereas, front-end is still the access database, so you can deploy it in just the same way you split the database.
If it is possible, then Export Tables to SharePoint Wizard will shifts the data to lists on the SharePoint site. As per the size and capacity of the database, the number of objects and system performance may get affected. If in case you want to stop the process then tap the Stop option.
This wizard will make a backup copy of your database. In an Access database, from the table, it makes a link to the lists. So now it’s been so easy to check the data on the SharePoint site while working on the Access. If any problem occurs, the Export Tables to SharePoint Wizard immediately reports an issue and save it in your Access database like a log table.
If you are an access 2007 user, then Export Tables to SharePoint Wizard is known as Move to SharePoint Site Wizard. It doesn’t make any referential integrity on SharePoint lists.
Use the Export Tables to SharePoint Wizard
- Go to the Database Tools option, and choose the Move Data group, hit the SharePoint It is present only if the database is saved with .accdb format.
- Try the below steps of Export Tables to SharePoint Wizard, which includes specifying your SharePoint site location.
- In order to cancel the process at any time, tap the stop.
- On the wizard on the last page, choose the checkbox of Show Details to watch out for more detail regarding migration.
- When the wizard completes all this task, hit to the Finish.
But if the wizard shows any warning, then you must review the log table and perform any action. This will ensure that your data was moved successfully.
How To Publish Access Database To SharePoint?
The process of publish Access database to Sharepoint office 365 is quite different in every version. So, let’s begin with how to perform this publishing of An Access Database In SharePoint. well, the complete step of publishing an Access database to SharePoint is described through the example, so watch it carefully.
- In Access database create or open a blank database.
- You can see that in the shown image we have opened a client named database. In this database, there is a table named clients so make double-tap to open it
- This will open up the client table and now you can see all its contained data.
- From the Access database application window, click the External data option.
- Now from the Export section tap the More option and from the drop-down menu choose the SharePoint List.
- in specifying the SharePoint section you need to enter the URL of your SharePoint site. E.g.: https://scuba3.atrackspace.com
- Within the specified name section assign name for the new list section.
- Uncheck the checkbox present next to the “Open the list when finished“.
- make a quick review of the settings and hit the OK option.
- if asked then enter the SharePoint user credentials.
- This will start exporting your client’s table from Access database to SharePoint list called Clients.
- To end up the Quick Edit view option, tap the Stop editing this list option.
After the creation of the SharePoint list, users can easily work with the list either present in the Access linked table or on the SharePoint site. You can put the data by making use of the Access table or form or by editing the list present over the SharePoint site.
Try all the aforementioned guidelines to perform the import/export task of the Access database to SharePoint. If you face any further problems while executing the conversion of the Access database to SharePoint. then in that case, please ask your queries in our comment section.