Data Format Change Between Sharepoint and Access

Question

I moved the data store for a standalone Access database to a Sharepoint list. I'm running into formatting/data type issues in the communication between Sharepoint and Access. I have a couple of fields that had a Data Type of Number and a Format of Percent. When I put moved the data into a Sharepoint list, that formatting was retained so I can see the percentages as initially entered into the Access database in my Sharepoint list.

However, when I open the Access database now, the values for these percent fields are showing either a "0" or a "1". When I look at the design view of the data, I can see that the Format of the data is now Standard. So I guess that results in the entered values being rounded to the nearest whole number. I'd like to be able to just change the Format in the design view, but of course I can't now because the data source is the Sharepoint list.

This seems so odd because I know that the data storage is changing but on the surface I think it would be simple to retain formatting within the same application (Access) while adding a backend (Sharepoint) to the data.

Is there any way I can change settings in Sharepoint list so that it translates to Access getting the formatting of the data correct? Do I need to do something to the initial Access DB to ensure it gets put into Sharepoint in a way that'll be read out in the correct format? I think I came across something about Access inferring data types/formats - would it be helpful to ensure some clearly percentage records (not 0% or 100%) are near the top of the table when the data is moved?

Answer 1

Dear Pennan,

Good day! Thank you for posting your query in our community. We are happy to help you.

May I know whether you first create a table in Access and click on External Data>More>SharePoint List to export the table as a SharePoint list and then click on New Data Source>From Online Services>SharePoint List and link the Access table with the SharePoint list.

If yes, it seems that I can't reproduce the issue in our environment.

If it is convenient, could you upload some screenshots showing the issue in Access and SharePoint Online in your environment and list the steps about how you export the Access data to a SharePoint list and how you open the data in Access from the SharePoint list for our confirmation?

Note: Remove any private information before uploading the screenshots.

Sincerely,

Cliff | Microsoft Community Moderator

***Note: In the event that you're unable to reply to this thread, please ensure that your Email address is verified in the Community Website by clicking on Your Account Name > "My Profile" > "Edit Profile" > Add your Email Address > tick "Receive email notifications" checkbox > click on "Save".***

Answer 2

Thanks for the response.

To your first question, no that is not the method that I used to move the data to SharePoint. I don't know if it makes a difference but I followed the instructions for moving data given here: https://support.microsoft.com/en-us/office/import-link-or-move-data-to-sharepoint-65bf7b03-74bf-445c-959a-24b7a401ddee

So I went to the Database Tools tab, and in the Move Data group selected SharePoint. Then went from there. This may just be a case of me needing to move existing data into a SharePoint list, rather than linking to data that was already in SharePoint.

I started with the data showing percentages in the datasheet view and that being reflected in the variable format in the design view as well:

Then I moved this data to SharePoint, which shows the data in the same format:

Then I look at the updated Access data and it looks like this:

In a standalone database, it would be a simple matter of going to the Table Fields tab and changing the data type and format. However, since it is pulling from a data source in Sharepoint, I can't make any changes to the data types or formats in Access. I'd try to go into Sharepoint and change the data format there, but the data format is already correct in Sharepoint.

Answer 3

Dear Pennan,

Thanks for your updates and providing the screenshots for our confirmation.

I did a test in the way you used in our environment and I can't reproduce the issue as well.

As the table and the SharePoint Online list are linked, I suggest you open Design View in View>change the format from Standard to Percent manually>click on External Data>Linked Table Manager>click on Refresh>check whether the table is refreshed successfully. If yes, return to Datasheet View to see if the number values becomes normally. If yes, you can add a new value in Access and then use Linked Table Manager to refresh the data to see if the SharePoint Online list data is also updated.

If yes, the issue should go away in your environment. If you want to back up a new list with the data to get rid of any data loss, I suggest you create a new list with the same columns and use Power Automate to automatically copy the data to a new backup list.

Meanwhile, if it is convenient, please upload a full screenshot of the Account page in the Access application you are using for our reference. You can access the page via clicking on File>Account.

Note: Remove any private information before uploading the screenshots.

Sincerely,

Cliff | Microsoft Community Moderator

***Note: In the event that you're unable to reply to this thread, please ensure that your Email address is verified in the Community Website by clicking on Your Account Name > "My Profile" > "Edit Profile" > Add your Email Address > tick "Receive email notifications" checkbox > click on "Save".***

Answer 4

Thank you for the ideas. Unfortunately, changing the data format manually does not. Because the data is linked, the Access table is read-only. So any changes I try to make are immediately discarded when I go to save/refresh the database.

Is there any way to override this setting and update the data in Access and then pushing that back to Sharepoint?

I already have the Access database backed up so I'm not worried about data loss. Though thanks for the Power Automate tip.

I don't see much specific information about my setup on the Account page. What is it specifically that would be helpful to know?

Answer 5

Dear Pennan,

Thanks for your updates.

I suggest you open Design View in View>change the format from Standard to Percent manually and then click on External Data>Linked Table Manager>click on Relink after you change the format of the Number field to see if the values become fine in Access. I did a test in our environment and it seems to work fine.

About the version number of the Access application, you can click on File>Account to get the page like the following.

Thanks for your effort and time.

Sincerely,

Cliff | Microsoft Community Moderator