Best Ways to Migrate Data Between Access Database & SharePoint Easily

SUMMARY:

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 set of technologies having compact integration with the 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 following tasks.

  • To have safe and secure access to your data.
  • Share data all over the internet or to an organization.
  • To make a database application which doesn’t require any MS Access application to use.

Access 2010/2013/2016/2019 and Access Services gives a platform to make databases which you can use on SharePoint site.

Using Access database and Access services you can design/publish web database. User who are already having account on the SharePoint site can make use of the web database in 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 SharePoint site it’s quite easy to look for records and data which were deleted accidentally.

Quick Launch:

You don’t need to trouble a lot for viewing the list on 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 conflicts.

Issue Tracking

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 to private data. SharePoint allows you to set different levels of permission. So, now you have the option to allow access permission to users.

Bulk editing:

If you need to do several changes to 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.

Report distribution:

If you are using Access for the creation of reports present in linked list data. To broadly share these reports as PDF file, just export it 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 easy and convenient way to make table having similar fields of tasks, events, issues, contacts list. Access will make a corresponding table with 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 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, that you can always get and work with updated data.
  • Previously you are using 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 SharePoint site. After linking of 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 of 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

How To Import Sharepoint List to Access Database

Import option helps to perform recurrence of import tasks further in future without going through the complete steps of Import wizard every time.

Why You Need To Import SharePoint List to Access Database?

Some common reasons behind importation of SharePoint list to Access database:

Suppose you want permanent shifting of data from SharePoint List to Access Database. in that case you need to import the list into Access and then make deletion of the SharePoint list.

The department in which you are working is using Access but you are pointed to use SharePoint list for merging of your data into one single database.

Steps To Import SharePoint List to Access Database

  1. 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 previously existing database then make a blank database.
  2. As per the Access database version you are using the location of import/link text wizard may slightly differs. 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.
  1. After performing the above steps, Get External Data – SharePoint Site dialog box will open up on your screen.
  2. You need to specify the address of source site, in the opened wizard box.
  3. Choose Import the source data into a new table in the current database option, and then tap to the Next
  4. The wizard will display a list, from this list make selection of list which you want to import.
  5. From the Items to Import column, choose the view you need to apply for each of your chosen list.
  6. Check box with label Import display values instead of IDs for fields that look up values stored in another list controls, offers these two tasks:
  • Choosing the check box option, will help to import the values as portion of the field itself.
  • Un-check the checkbox, for destination field to check out another 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 proper defining of the lookup field in the Access.
  1. At last tap to the OK

Now you will see that Access database will import complete of your SharePoint list. After this it will display the status of performed operation on wizard ending page.

If you need to perform the import steps again in future. Then you just have to save the details as import specification. Access database won’t overwrite table already present in database as part of import operation. Also, you can’t append the contents of list or view to existing table.

How To Export Access Database To Sharepoint List?

Export Access Database To Sharepoint List

moving Access data to SharePoint is best way to create backend 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 to the Stop option.

This wizard will make a backup copy of your database. In Access database, from the table it makes a link to the lists. So now it’s been so easy to check the data on SharePoint  site while working on the Access. If any problem occurs, the Export Tables to SharePoint Wizard immediately reports issue and save it in your Access database like a log table.

Note:

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

  1. Go to Database Tools  option, and choose the Move Data group, hit the SharePoint It is present only if the database is saved with .accdb format.
  2. Try the below steps of Export Tables to SharePoint Wizard, which includes specifying of your SharePoint site location.
  3. In order to cancel the process at any time, tap to the stop
  4. On wizard last page, choose the check box of Show Details to watch out more detail regarding migration.
  5. 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?

Publish Access Database To SharePoint

Process of publishing Access database in SharePoint is quite different in every version. So, let’s begin how to perform this publishing of An Access Database In SharePoint. well the complete step of publishing Access database to SharePoint is described through the example, so watch it carefully.

  • In Access database create or open a blank database.

Publish Access Database To SharePoint 1

  • You can see that in the shown image we have opened a client named database. In this database there is table named clients so make double tap to open it

Publish Access Database To SharePoint 2

  • this will open up the client table and now you can see all it’s contained data.

Publish Access Database To SharePoint 3

  • From the Access database application window, click the external data option.

Publish Access Database To SharePoint 4

  • now from the Export section tap to the More option and from the drop-down menu choose the SharePoint List.

Publish Access Database To SharePoint 5

  • in specify SharePoint section you need to enter URL of your SharePoint site. E.g.: https://scuba3.atrackspace.com

Publish Access Database To SharePoint 6

  • Within the specify name section assign name for the new list section.

Publish Access Database To SharePoint 7

  • Uncheck the check box present next to the “Open the list when finished“.
  • make a quick review over the settings and hit the OK option.

Publish Access Database To SharePoint 9

  • if asked then enter the SharePoint user credentials.

Publish Access Database To SharePoint 11

  • This will start exporting your clients table from Access database to SharePoint list called Clients.

Publish Access Database To SharePoint 12

  • To end up the Quick Edit view option, tap to the Stop editing this list option.

Publish Access Database To SharePoint 13

Conclusion:

After the creation of the SharePoint list, user 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 import/export task of Access database to SharePoint.  If you face any further problem while executing conversion of Access database to SharePoint. then in that case, please ask your queries in our FAQ section.

Best Ways to Migrate Data Between Access Database & SharePoint Easily