Connection in Sharepoint (Access Database)

Question

Hi All,

I am stuck to create a Powerpivot (datamodel) in excel who is connected to an Access Database in Sharepoint.

As you can see below the properties of my connection, to me everything looks fine but once I try to refresh data, it asks me Login and Password.

I do not know where is from this password because in my Access Database I do not have one.

Connection String :

Provider=Microsoft.ACE.OLEDB.12.0;Data Source="https://...................../sites/KxxxxCpecialTicketingGuidelinesCoronavirus-Reporting/Shared Documents/Reporting/1. Reporting/2020/1. Database/Database Report.accdb";Mode=Read;Extended Properties=;Jet OLEDB:System database=;Jet OLEDB:Registry Path=;Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

If you could tell me how I can remove this login /password when I refresh the pivot, it would be nice :)

Thank you in advance,

Kev

Answer 1

Hi dudu,

Thanks for sharing your experience here.

According to your description, we noticed that you create a Powerpivot to connected to an Access Database in Sharepoint online.

As we are not sure how you connect to Access Database in Sharepoint online. If you can't use the method in Excel normally, from our side, you can link your data to SharePoint list first and then  save the Access database in the local environment, then open Excel>Data>Go to PowerPivot window, then select get external data from Access:

Then browser your Access file location and complete the process according to the prompted message.

After completed, you can refresh your data as normal:

Please have a try and share the result with us.

Best Regards

Higgins

Answer 2

Hi Higgins,

Thank you for your quick reply.

Iwas thinking at first to put all my database in SharePoint, but when I see the time I need to load all the data there and how it is not practicle for me to amend data inside. I resign from this idea. So I think Access Database was the best option.

I think I did exactly how you show above, you just have another version than me.

And then select all the database I have:

I have to mention that this PowerPivot will be accessible to other people who may need to refresh data and filter it. If my database is on my local disk there cannot do that.

Also please take in consideration some of the database counts more than 500000 lines.

I am open to any solution you can offer me :)

Thank you in advance,

Kev

Answer 3

Hi dudu,

Thanks for your updating to help us better understand your concern.

As we noticed that you want to refresh the data which edit by other users, in this case, once you sync Access database which stored in the SharePoint online to the local OneDrive sync folder, you can follow the steps in my previous reply to create a PowerPivot table from this file. 

Then from our test, your user can also sync the file to their OneDrive sync folder and made some change. All change will update to SharePoint online first, then you will see the change in your OneDrive sync folder, and you can use fresh data features in Excel to get those changes by other users.

Besides, according to your method, we are sorry that we got a Model OLE DB Query instead of Microsoft Access Database of Connection type:

Therefore, you can follow my method to check if you can update the data edited by other users, and please post back more detail about your method which can be helpful to help us reproduce your issue from our side.

Looking forward to hearing from your reply.

Best Regards

Higgins

Answer 4

Hi Higgins,

When I try your way, I got this error once I try to connect with my Access Database:

It is possible that Sharepoint do not allow me to read data from an Excel when I try to connect directly through the SP Path?

Thank you again for your support, Kev.

Answer 5

Hi dudu,

Thanks for your updating.

From your situation, we would like to inform you that you can connect your Access database from the OneNote sync folder instead of the SharePoint path.

Once you upload your Access file in SharePoint online, click Sync button on the top ribbon to open your OneDrive sync folder, and then find your Access file in the OneDrive sync folder and select Always keep on this device:

Then you can go to Excel>Data>Data tools>Go to the PowerPivot window, and then you can follow the steps in my previous reply to get your Access data:

Please have a try and share the result with us.

Best Regards

Higgins