MS Access Linked SharePoint Online Lists Connection String

Question

I have an MS Access app that has 2 linked lists from SharePoint Online. I iterate all tables on start up and Refresh the Links as I want to make sure the connection is good and also for if I want to point at a different SharePoint Online Instance, say for Dev or Test. This only works if I'm logged into SharePoint Online, so I want to change that so I don't get a prompt to log in. I've done this before with on premise SharePoint and the AD authentication is all you need, but SharePoint Online is not connected to AD and uses a completely different set of credentials.

My connections string looks like this if you view the List in Design from inside MS Access. ACEWSS;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=https://mycompany.sharepoint.com;LIST={AF7E4E8E-6E7D-4BEA-B856-F69BF58114A4};VIEW=; RetrieveIds=Yes;TABLE=spMyList;

Is it possible to add credentials to this connection string? I looked up on http://www.connectionstrings.com and they have example but nothing works. I tried User/Password and also uid/pwd but I still get that prompt.

Dave Stuart Dave Stuart · Accepted Answer · 2017-03-12T20:55:30

The answer to this problem is to set the TableDef.Attributes Property to DB_ATTACHSAVEPWD just before the Connect and RefreshLink as shown below.

tblDef.Attributes = DB_ATTACHSAVEPWD
tblDef.Connect = strSQLAzureConnectionString
tblDef.RefreshLink

MS Access Linked SharePoint Online Lists Connection String

1 Answers