Linking SharePoint Table

[jo15765]

Hi - I am linking a Share Point table into MS Access. The issue that I am experiencing is that one of the fields in the table is a comments field, and when the table is linked into access, only the most recent comment is displayed instead of historical comments.



What needs to be done so that all values from this field will be linked in?

[CJ_London]

not sure if it works with sharepoint, but for a memo/long text field in an access table you would use the columnhistory function. The table property 'Append Only' needs to be set to Yes for it to work and it will only work going forward from the time this is set. It is a long time since I've used it, but think you need to use columnhistory function in the form or report - it won't work in a query.

found this link https://docs.microsoft.com/en-us/off....columnhistory

[jo15765]

not sure if it works with sharepoint, but for a memo/long text field in an access table you would use the columnhistory function. The table property 'Append Only' needs to be set to Yes for it to work and it will only work going forward from the time this is set. It is a long time since I've used it, but think you need to use columnhistory function in the form or report - it won't work in a query.

found this link https://docs.microsoft.com/en-us/off....columnhistory

Let me give a little more in my story, I have linked SharePoint Tables into an accdb.
I am then making local copies of only the fields I need from the SharePoint Tables in my accdb (this takes 8 SP tables down to two local tables)
I then run a make-table query from the two local tables using Allen Browne's Concat Related function so I am working with ONE table
And I set the record source of the form to be the one table

Now the issue I have noticed is that the one table does not have the history of my field comments. If I use the function you listed to and use Debug.Print sHistory then all History is displayed. Which that takes care of the display issue, thank you @Ajax.

The one step I am still trying to wrap my mind around how to set-up is....
Ideally I need the user to be able to update (add or edit) data on the form, which would then update the local table only. And I was going to join the local table to the SharePoint table on ID and update everything. Obviously the memo field would more than likely be to large for access to handle is there a way for me to "append" any added value to that field instead of over writing the SharePoint table with the most recent value only?

Or what do you guru's here have to suggest on a way to handle that?

[CJ_London]

Obviously the memo field would more than likely be to large for access to handle

it can hold 65000 characters - are you saying this is not enough?

need the user to be able to update (add or edit) data on the form

in respect of the memo field history, it cannot be edited if append is yes. any edits will be appended as a new comment

Personally I gave up on using column history a long time ago - basically too limited for what I wanted to be able to do. Easier to keep the memo field in a separate table on a one to many relationship with the main table, perhaps include a few extra fields to indicate time and user. Suspect that is the way you will have to go.

Not sure why you want to get everything down to one table - databases are intended to store data in a normalised way, making it denormalised only makes life more difficult.

I don't understand what you are trying to do - 8 sharepoint lists (not tables) combined into 1 then edited then updated back to the sharepoint lists. Why not just edit sharepoint directly? In a form you can set fields/records to be not updateable.