MS Access can be a nice interface onto SharePoint. We’ve been implementing this for a client, allowing files and metadata that are managed in HBSMR (Access UI onto SQL Server database) to be synchronised with a SharePoint site (public interface onto some of the data).
For users working in HBSMR, we are providing tools to add a new file to SharePoint, update the metadata for an existing file, and delete a file.
However, we ran into trouble with adding new files, and thought it worth writing up the solution because it is clear that others have encountered the same problem without any solutions being posted on the web.
We are adding the file by moving it into the WebDAV folder for the SharePoint list (e.g. \\sharePointserver\archaeology\reports). This automatically creates a record in the List, with several fields populated. We then want to update the new record with custom metadata, things like who wrote the report, copyright statement, etc. So how do we get hold of the new record that has been automatically created? The unique piece of information we have is the file name, so we do a lookup into the table to fetch the ID for the record that describes our file. We’ve not yet fully bottomed out the best way of doing this, given potential complications with illegal characters in filenames etc., but for the moment we are looking for the list folder and filename in one of the automatically-populated metadata fields in the SharePoint table.
We found that we could not “see” the new record initially. After some experimenting we found that to see the new record required either a) requerying a form that was bound to the table, or b) open and closing the table (programmatically).
But that’s where the trouble really started. We could find the new record, and move to it in an editable recordset (DAO), however whenever we tried to update it we got a 3314 error: You must enter a value in the ‘Name’ field. The “Name” field is a non-negotiable field in a document list, and it reveals itself as a hyperlink to the file (when viewed on the SharePoint web interface or in the linked MS Access table. We tried inserting every variation of hyperlink and filename we could think of, but no change. Meanwhile we were sure all along this should be a field that SharePoint itself populated; yet although we could see all the other auto metadata fields, this one was always NULL after the refresh.
Paul M spotted the solution – we observed that when we watched the same list in the browser interface, and refreshed after adding the new file, the list refreshed the Name column with a valid hyperlink. Yet we could not see this in Access. So we tried a more violent refresh – instead of simply re-opening the table, we tried deleting it and re-linking it – yes! the hyperlink appeared for the new record.
After some more experimenting we found that it is sufficient to refresh the link programmatically, along these lines:
dim td as tabledef, db as database
set db = CurrentDb
set td = db.TableDefs(“MySharePointTableName”)
td.RefreshLink
etc (clean up, the find new record)
After that we can see not only the new record, but the hyperlink Name field is populated. We can then edit all the other fields, and we do not need to touch the Name field at all.