Access 2016 FE to Sharepoint (365/online) Lists - Pulling out what little hair i have left!!! Help (1 Viewer)

Dave1975

Member
Local time
Today, 20:15
Joined
Oct 21, 2015
Messages
39
Afternoon - its has been a long while since i have been asked to do anything that required any outside help but this task is drivign me up the wall and wondering if anyone has any insight.

Setting: Access 2016 as a front end using sharepoint lists as tables. Nothing to crazy so well within spec. Any DB i create and link to sharepoint is fine whichever way way i link (create or link to existing). The problem comes from someone else trying to access the lists from access - it wants to log on as me??!?

i have tried everything.

Some of us are remote working and using a VPN which i thought my be causing issue - but no - same issue for people in the office.

i got my IT to create a full SP site where i was owner and the other people i set up as owners.... no luck
I have created loads of different user groups on SP - no luck

from what i can see it should work... a simple linked list/table from Access but nobody other than the creator can access the lists....fustrating

I have a lot of Access solutions i need to move data to SP before i can start coding PowerApps replacements (will miss Access but still got it at home so all good)

Has anyone had this issue? or at least heard of it and its solution?

if i can provide any more info - let me know. here's hoping!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:15
Joined
Oct 29, 2018
Messages
21,358
If you add an owner to the site, can they create their own list and connect to it in Access?
 

Dave1975

Member
Local time
Today, 20:15
Joined
Oct 21, 2015
Messages
39
If you add an owner to the site, can they create their own list and connect to it in Access?
i haven't tried - is the thinking that it may force the DB to start recognising them as well?
even if that works i cant get everyone that uses my front ends to do that...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:15
Joined
Oct 29, 2018
Messages
21,358
i haven't tried - is the thinking that it may force the DB to start recognising them as well?
even if that works i cant get everyone that uses my front ends to do that...
No, the thinking is to verify if other users have the correct permissions to access the SharePoint site.
 

Dave1975

Member
Local time
Today, 20:15
Joined
Oct 21, 2015
Messages
39
No, the thinking is to verify if other users have the correct permissions to access the SharePoint site.
ok - good plan - so frustrated that didnt think of that - will try and advise
 

Dave1975

Member
Local time
Today, 20:15
Joined
Oct 21, 2015
Messages
39
update:
other users can create a list on the site
trying to link to the site from access - still wont allow
i linked the list to the DB but then it refused to let anyone else into the list from Access..

i'm convinced its a setting in Sharepoint that Access is getting confused over but no idea where to start lol
 

Isaac

Lifelong Learner
Local time
Today, 13:15
Joined
Mar 14, 2017
Messages
8,738
I use Sharepoint lists as a back end quite frequently. I'm curious though, I'm not at all clear yet on precisely what is happening.

You have mentioned "it wants to log on as me". What does that mean?

So, you have an Access file with Linked table objects which are Sharepoint lists. Another user opens the Access file. The look at the object pane over on the left, and double-click on one of the linked Sharepoint lists/tables. What happens? Error message? If so, what? Can you give a screenshot?

Details seem very important to this issue.
 

Dave1975

Member
Local time
Today, 20:15
Joined
Oct 21, 2015
Messages
39
You have got it.

On the SharePoint site .. anyone i have setup as a at least a contributor or higher can create a list, modify it etc

When i create or they create a database that then either links to an existing list on the SP site or creates a list on the SP site from access.... cannot access the list after - it asks for a domain log on.

the only one who can access a list linked in MS Access (Created by or Existing) is me...SP site owner

I'm more and more thinking our corporate settings for SharePoint are the issue but given that we can create lists and use a whole load of SharePoint stuff this also seems unlikely at the same time.

we also use power-apps - and this uses lists just fine.

its probably one of those 'staring me in the face' moments all programmers get but after weeks of trying i am at a loss.
 

Isaac

Lifelong Learner
Local time
Today, 13:15
Joined
Mar 14, 2017
Messages
8,738
1. is the sharepoint site on-premises or sharepoint-online ?
2. are the users on Citrix?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:15
Joined
Oct 29, 2018
Messages
21,358
trying to link to the site from access - still wont allow
Hi. Can you please elaborate on this part? How exactly are you and the others create a link in Access to the SharePoint List? Thanks.
 

Dave1975

Member
Local time
Today, 20:15
Joined
Oct 21, 2015
Messages
39
multiple different ways
1. i create access DB, i create sharepoint list from access to our sharepoint site(of which we are all full permissions), other users cannot open list from within Access - asks for domain log on
2. i create list on SP site, i link to it in a new Access DB, nobody can access list in Access asks for domain log o

all users can use access and if tables are in another database or ODBC/sql - no issues - its just sharepoint.
everything points to sharepoint being the issue but the users can create lists etc on SP just cant access them through Access..

got me proper stumped
 

Isaac

Lifelong Learner
Local time
Today, 13:15
Joined
Mar 14, 2017
Messages
8,738
Linked table - Sharepoint Online - Citrix Users getting login popup | Access World Forums (access-programmers.co.uk)

Just for clarity, I don't think (unless I missed it sorry) you've mentioned what actually happens if the users go ahead and enter those login credentials. (For us, they sometimes get the same problem you're getting IF it is 1) sharepoint online, as you have, and 2) they are Citrix users. (see above link). In that particular case, the db seems not to work (do the thing it was trying to do - insert, update, etc), no matter what they enter.

On the other hand, for our users who occasionally get that credentials popup when using their browser to sharepoint, they are able to enter their username as domain\username and then their network password, and all is well.

I read a complicated article about how to possibly solve this via Trusted Sites + Credential Manager, but it was like a 20 step process that there is zero chance I can train my 70 users across the globe on. I can't remember the link now but will post if I find it.

One more question:
. i create list on SP site, i link to it in a new Access DB, nobody can access list in Access asks for domain log o
Meaning you go through the Ribbon > External Data > Online Services > Sharepoint? or you run code=DoCmd.TransferSharepointList ?
 

Dave1975

Member
Local time
Today, 20:15
Joined
Oct 21, 2015
Messages
39
no matter which credentials are provided - login is not possible unless its me and i never get the login request

they can open the DB just cant view any shared list whether linked or created/linked

we have no access to internet options so cant changed preffered sites


both ..
Ribbon > Create > Sharepoint Lists > {any list type}
or
Ribbon > External > More > Sharepoint List


both end up the same
 

Isaac

Lifelong Learner
Local time
Today, 13:15
Joined
Mar 14, 2017
Messages
8,738
I have been struggling a lot with Access-to-Sharepoint recently too, and found little to no help on the matter. The only other thing I can think of to try, in case it provides any insight into the situation, is to experiment - try having them perform the actual link from inside Access, (ribbon >external etc). Would be curious to see if it lets them - if it lets them input a Site address, see the lists, and make the link - and then open it.

As I mentioned, I have been completely unable to have my users who are inside Citrix, connect to sharepoint online links in an Access database.
:(

Best of luck, hope you come up with a solution of some kind.
 

Users who are viewing this thread

Top Bottom