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

It doesn't seem to matter whether I use relink or refresh, I still run into issues of not being able to change the data format because the database is read-only.

Here's a screen shot with the info about my Access version:

Answer 2

Dear Pennan,

Thanks for your updates.

May I know whether you can see whether the data format becomes percentage format after you click on OK on the pup-up window showed in your screenshot?

Can you see the following window after you click on Relink? If yes, click on Yes and then click on OK on the next window to see the result.

After doing some further tests, it seems that I can reproduce the issue in your environment now. It seems that the issue may happen after you manually change some formats of the fields or some modification conflicts occur. Sorry for the confusion in the previous replies.

The issue may happen when you close and open the database each time. I'm afraid that the current workaround may be to change the field format in Design View and relink the data by the steps above. I kindly suggest you check whether it is doable in your environment.

I think it may be a point which is needed to be improved at the Access side. I kindly suggest you submit your feedback on the Feedback platform to let our product developers know the situation in your environment for product improvements. If our product developers have some updates on the feedback you have submitted, they will update there directly.

Another way may be to first use SharePoint>External Data to export the data to a new SharePoint list in SharePoint Online. Then use Linked Table Manager to add a new link between the table in Access and SharePoint List and finally use Refresh All in Home to refresh the newly added record or the modified record in SharePoint Online and Access. If you haven't tried the way, I suggest you give it a try and see if the format issue disappears at the Access side in your environment.

Thanks for your effort and time.

Sincerely,

Cliff | Microsoft Community Moderator