Edit Table Properties in Access Table linked to SharePoint List

New Contributor

I have an Access database with several tables linked to SharePoint lists.  I recently removed some unused columns/fields from some of the lists.  For one of the tables I get the following message asking for a parameter:

EnterParameterValue.png

I think I found the culprit in the Order By Statement which still includes the deleted field in the statement.

PropertySheet.png

How can I edit the Order By statement.   Since this is a linked table I can't edit it in Access and I haven't found a way to change the properties in the SharePoint list.

Is it possible to change the linked table to local, make the change in table properties, and then relinked the table to the list?

Thanks,

Merlin Siefken

6 Replies
I don't use SharePoint but you can right click on any linked table in the navigation pane and select Convert to Local Table.
BTW Is the first part of the connection string correct in the property sheet? ACEWSS?
As far as I know the ACEWSS entry (whatever that means) is correct. Everything is working fine. I just get the aggravating request when I try to open the table. I don't enter a parameter and click okay and the table opens just fine. All queries are working. I know you can make a linked table local. My question is, once I make it local, fix the Order By statement and save it, how do I relink the local table to the SharePoint list. If I remember correctly, trying to relink the table creates a new list in SharePoint.

Hi,

> Since this is a linked table I can't edit it in Access

That's true for many properties of a linked table but e.g. not for the Order By property. In Access

  1. open the table in design view
  2. delete the text in the property line
  3. click the Save icon before closing the linked table

Servus
Karl
*********
http://AccessDevCon.com
https://www.donkarl.com

 

@Karl Donaubauer 

I tried that and this is the response I get.

ReadOnly.png

Hi,
Funny. I had tried it successfully with an Order By setting for a linked Sharepoint list in Access 2010. Maybe it's different again with a newer Access version.


What happens if you do the Save As? Do you get a linked table/list with the changed property? In that case you could just rename it to replace the old one.

Servus

Karl
*********
http://AccessDevCon.com
https://www.donkarl.com

No. It saves the linked table as a "Copy of" local table with no data in it. I think I am going to have to change the table to a local table, make the changes and then probably rename the SharePoint list to "_old" and create a new SharePoint list linked to the modified local table in Access.