We help IT Professionals succeed at work.

"Could not find field" error in Access 2010 with linked table to SharePoint 2010 list

Medium Priority
3,017 Views
Last Modified: 2016-08-19
Hello!  I am using Access 2010 to link to a SharePoint 2010 list and update the list data using update queries.  The SharePoint list is very large, over 17000 rows.  Sometimes, the update query will fail with the error "Could not find field."  No error number or field name in question is provided.  What makes the issue very troubling is that it is intermittent with no obvious reason.  Some records will be updated successfully before the error is thrown, so it can't really be a missing or misspelled field name in the query.  I have reviewed the existing SharePoint data to ensure the values are not in conflict with field requirements, like choice field values and required fields. I am able to update the same record manually in the SharePoint list with the same update the query would have made, so I don't think it is a validation error.  Any help would be greatly appreciated.  This issue is getting worse and happening more frequently, causing me to not be able to complete the weekly updates.  Thank you!!
Comment
Watch Question

conagramanrock star

Commented:
hello i don't know how much assistance i will be able to give but i will try working with you to maybe get a handle on your problem.    

my first recommendation would be to not use access to update sharepoint instead use a sharepoint ui

that being said... you said are getting an error  "Could not find field."
where is this error being shown at? is that an alert inside a condition?
if so can you post the code for the function that owns the condition?

Author

Commented:
Hello!  The error appears when running an update query.  The  SharePoint list contains over 17,000 rows with many fields.  We receive a weekly HR data feed that we use to update the data in the  SharePoint list.  We can't do a simple record update because certain things need to happen based on the type of change found in the HR record, like triggering a string of things when the person's city changes.  It is hard to pinpoint any error in the queries because some of the records will be successfully updated before it crashes with that error.  I would assume that means the problem is data-related, but I can't find it. I have verified the data in SharePoint thoroughly. This doesn't happen in just one update query, but it many of them... intermittently.  Each week, the ones that fail changes so it has been mind-boggling.  Upgrading to Access 2013 is not an option in my environment.

When you recommend using the UI, I'm guessing you mean to simply update each record manually in the SharePoint list.  Given the huge task of updating thousands of rows every week, updating them manually would not be an acceptable solution.  That would be time-consuming and prone to error.  The series of 100+ update queries in Access used to take about an hour before this problem appeared.
After struggling with this problem for weeks, I finally found a work-around.  The issue seems to be environment-related, which explains why no amount of data-checking and code-checking was helping.  After modifying a cache setting, the update queries that were partially failing completed successfully.  The ultimate solution will be to improve the environment with more RAM/CPU power, but this is the work-around.

In File --> Options --> Current Database --> Caching Web Service and SharePoint tables … Uncheck the box next to “Use the cache format that is compatible with Microsoft Access 2010 and later”.

Author

Commented:
Resolved the issue myself.
Gloria, you are a saint. I was having real issues. Thank you for this solution.