Need to Move MS Access database[single/split] from file server/shared drive to SharePoint Online.

Question

Hi All,

We are planning to move data from file server to SharePoint online, the customer is having MS Access databases too, the MS Access database includes single as well as split databases, as a part of data transfer to SharePoint Online, the data movement from file server to SharePoint Online will be consider as successful, if we move all the MS access database to SharePoint Online and then publish it from user's desktop machine to SharePoint Online.

Currently users used to open the MS access databases from their desktop machines and save/publish the data to the files server/shared drive.

How can we implement the same in SharePoint Online.

Kindly suggest.

Answer 1

Hi @Roopak Sahoo
Per my research, we could move Access database data to SharePoint Online as following:

Use the Export Tables to SharePoint Wizard:

1.On the Database Tools tab, in the Move Data group, click SharePoint. This option is only available if your database is saved in the .accdb file format.

2.Follow the steps in the Export Tables to SharePoint Wizard, including specifying the location of your SharePoint site.
To cancel the process, click Stop.

3.On the last page of the wizard, select the Show Details check box to see more details about the migration.

This wizard page describes which tables have been linked to lists and provides information about a backup location and the URL for your database. It also provides a warning if some migration issues were encountered and provides the location of a log table where you can see more details about the issues.

4.Click Finish when the wizard completes its actions.

If the wizard displays a warning, you should review the log table and take any actions necessary to ensure that your data was migrated successfully. For example, certain fields may not be moved or may be converted to another data type that is compatible with a SharePoint list.

For more information, you could refer to:
https://support.microsoft.com/en-us/office/import-link-or-move-data-to-sharepoint-65bf7b03-74bf-445c-959a-24b7a401ddee#movewizard

---

If an Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Yi Lu_MSFT ,

Thanks for your reply,

Will Export Tables to SharePoint Wizard only moves tables or its relationships[primary/foreign/compound key] with other tables too, does it's supports referential integrity and other objects of MS Access databases for eg:- Forms, Stored procedures, Queries, Views, Macros etc. as well as split databases of MS Access.

As specified above, currently users used to open the MS access databases from their desktop machines and save/publish data to the files server/shared drive, can't we do the same in SharePoint online, where we just need to publish the changes from MS Access desktop database to SharePoint Online.

Answer 3

Hi @Yi Lu_MSFT ,

I have installed MS Access database 2016, below are the points observed:

It is not feasible to upload, published or Shared MS Access desktop database in SharePoint Online or in OneDrive with multiple users, below are the reasons:

• It creates multiple copies of the same database file, if multiple users are using it simultaneously.
• Database file can’t be opened by single or multiple users from SharePoint Online, it’s need to be downloaded, and each user need to overwrite the changes, which seems not feasible for further use.
• If a user download it and upload it on SharePoint the changes will not be merged with other user’s changes, it will be overwrite by the last updated user and the changes will be reflected
From the last updated user, however the versions will be maintained in SharePoint Online, so that if a user restored the previous version, he can do the same.
• Forms, Queries, Macros and other objects of the MS Access can’t be shared, only tables data can be Shared that too by linking or by Moving/exporting the table to SharePoint

Exported the relational tables from MS Access to SharePoint Online using MS Access table Export wizard and Observed the below Points:

• Referential integrity is supported for MS Access in SharePoint Online, so child record in foreign key table will not be deleted if we tried to delete from Primary Key table, child record need to be deleted first in SharePoint Online too, and then only it will allow to delete the Primary Key table row of the table, in combination with Referential Integrity cascade delete supported & cascade update not supported in SharePoint Online. It created Lookup column in child table for the Primary Key columns, and which supports Restricted & Cascade delete in SharePoint online

• Only Auto-number/Number keys are eligible for Primary Key in MS Access, if any MS Access table having Primary Key on varchar data type for e.g.:- A01, A02, it works in MS Access
But while exporting the table to SharePoint Online it doesn’t support the relationship, and we will get the relationship broken in SharePoint Online and Parent and Child table will not behave as per the expectations[relates to referential integrity]

Since the database has been used; the ID numbers are not sequential - there are "missing" numbers as records have, over time, been deleted. This isn't an issue until you try to move things to Sharepoint.
The export Sharepoint takes the Primary Key / Autonumber and re-indexes it. So, for example, if my first entry before exporting: customerID = 3, after exporting, customerID = 1,
In such a case MS Access forms and Queries and other related objects which are referencing the required files will give unexpected results and need to be changed accordingly.
below is the supported url:
https://answers.microsoft.com/en-us/msoffice/forum/all/exporting-access-table-to-sharepoint-and/14dadf66-181a-4033-8895-75d831c0d415

After exporting table to SharePoint list, you can't do changes in the table design, for eg:-
if you need to add one more column to table, you can add it in MS Access, but it will not reflect in
SharePoint List.

• Publishing of MS Access web databases in SharePoint Online no more supported in recent office 365 subscriptions with regards to SharePoint Online.

Looking at the above points/observations, i don't think so it is a good idea to move the MS Access tables to SharePoint Online.

Please share you suggestions.