Linked Tables From SharePoint Into Access

[GC2010]

I am linking in multiple tables from SharePoint into an access database, I have no control or access to the back-end SharePoint tables. My issue is that in SharePoint there is a field that is a memo? Or super long text, and append value is set to true, so that you see historical data "stacked" on top of each other.

Now my issue is that when I link the table into Access I get the most-recent entry from the SharePoint field only. Not ALL data. Is there A way I can get ALL data from the SharePoint field returned to Access?

For example, SharePoint might show...
userComments = "06/30/2019 - App was buggy tonight."
"06/20/2019 - App was running normal"
"06/18/2019 - App was down for 2 hours"

And when linked into Access all I get is
06/30/2019 - App was buggy tonight.

I need a way for all of the data to be brought into access

 

[theDBguy]

Actually, you should have it except that it's probably not normally displayed in the table. Instead, you can use a query with a calculated column using the ColumnHistory() method to show all the historical notes.

 

[June7]

If the field were in an Access table, would use ColumnHistory function in textbox or query or VBA.

I can't find an explicit example of applying this for SharePoint list field. Possibly exactly the same way.

 

[isladogs]

Suggest you also read this extended article which explains the use of the ColumnHistory property in detail.
http://www.mendipdatasystems.co.uk/column-history-memo-fields/4594523656

The following link looks like it may also be useful (untested)
https://sharepoint.stackexchange.com/questions/192417/how-can-we-read-version-history-of-document-library-and-update-particular-versio

Hope that helps

 

[GC2010]

Thanks everyone for the links and insight!!

I may be thinking of using this in a way not so good...
I have my linked SharePoint tables, and am creating 1 Local Table of ONLY the records that are relevant to me (essentially cutting the SharePoint Table from 3K records to 200).
--> Now I was wanting to alter my local table and add a field called comments and append ALL values from the SharePoint table to my local table.

Feasibly am I thinking of a proper way to utilize this?

 

[theDBguy]

Thanks everyone for the links and insight!!

I may be thinking of using this in a way not so good...
I have my linked SharePoint tables, and am creating 1 Local Table of ONLY the records that are relevant to me (essentially cutting the SharePoint Table from 3K records to 200).
--> Now I was wanting to alter my local table and add a field called comments and append ALL values from the SharePoint table to my local table.

Feasibly am I thinking of a proper way to utilize this?

Hi. Since the history records are stored in a system table, as Colin explained in his article, the only way you'll be able to get them into your local table would be to use some VBA code. However, this would mean your local table would be out of sync with the actual history in SharePoint as soon as a new comment is added there.

 

[GC2010]

Hi. Since the history records are stored in a system table, as Colin explained in his article, the only way you'll be able to get them into your local table would be to use some VBA code. However, this would mean your local table would be out of sync with the actual history in SharePoint as soon as a new comment is added there.

Local tables being out of sync is not an issue (I know it sounds crazy but its not).

What would be the VBA code to alter my table to add a column to house the comments, and then back code to copy comments from the SharePojnt table to my local table?

 

[June7]

Don't need VBA to alter table. Do it manually - it's a one-time design change.

Suggest you build a query object that reads the ColumnHistory.

SELECT ID, field1, field2, ColumnHistory("list name", "column name", "ID=" & [ID]) AS field3 FROM list WHERE some criteria;

Then reference that query in VBA. Either in INSERT SELECT or MAKE TABLE.

CurrentDb.Execute "INSERT INTO table SELECT * FROM query"

The trick is figuring out what event to put code into.

 

[theDBguy]

Local tables being out of sync is not an issue (I know it sounds crazy but its not).

What would be the VBA code to alter my table to add a column to house the comments, and then back code to copy comments from the SharePojnt table to my local table?

Hi. I was thinking the VBA code might depend on what you want to put in the local table. If you want to put all the history together in one record, then that should be as simple as what June was suggesting. However, if you want to duplicate the system table, as shown in Colin's article; that is, if you want all the history in separate records, then you might need some VBA to do it.

 

[GC2010]

Don't need VBA to alter table. Do it manually - it's a one-time design change.

Suggest you build a query object that reads the ColumnHistory.

SELECT ID, field1, field2, ColumnHistory("list name", "column name", "ID=" & [ID]) AS field3 FROM list WHERE some criteria;

Then reference that query in VBA. Either in INSERT SELECT or MAKE TABLE.

CurrentDb.Execute "INSERT INTO table SELECT * FROM query"

The trick is figuring out what event to put code into.

I have this as the SQL for my query but it produces #error for comments

Select [animal tracking].[account name], 
[animal tracking].[parent animal],
ColumnHistory(“[animal tracking]”, “Comments”, “[account name]=“& [account name]) As Comments
From [animal tracking]
Where [status] = “Active”;

Note: I posted this from mobile so I know the double quotes are not correct here but they are in access

 

[June7]

If query really has those smart quotes, have to replace with normal quotes.

Also, if [account name] is text type, need apostrophe delimiters.

ColumnHistory("[animal tracking]", "Comments", "[account name]='" & [account name] & "'")



Advise not to use spaces in object naming convention.

 

[GC2010]

If query really has those smart quotes, have to replace with normal quotes.

Also, if [account name] is text type, need apostrophe delimiters.

ColumnHistory("[animal tracking]", "Comments", "[account name]='" & [account name] & "'")



Advise not to use spaces in object naming convention.

They are not smart quotes in the query.

Agreed, spaces in field names is horrible but that is how they are set up in SharePoint and I am unable to change

[account name] is number data type

 

[June7]

Okay, but I hope names in local table are more sensible.

I tested query and it works for me. But I am not using SharePoint. Never have, zero experience with it.

 

[GC2010]

Okay, maybe I’m mid understanding.

At the moment I’m just wanting to run a straight select query from my share point table - [animal tracking]
Field name - comments
And for the where [account name] = [account name] (both from the SharePoint table

I changed the alias to a different name than the SharePoint table and still getting the #error ok that field

 

[GC2010]

If I specify “[account name] = 231”

For the where Clause the query will execute as expected. But I want to generate column history for all results the select query returns.

 

[June7]

I think your understanding is just fine, as well as mine.

As I said, works for a local Access table. No idea about using SharePoint list. Why don't you create a local table with memo field set for append only and some dummy records. Does query work with it? That will give some indication of where issue lies.

I did a test on alias field name and found it did not make a difference if it is same as original field.

 

[GC2010]

Problem with brackets

I needed to use quotes only and no brackets in the table name

One issue remaining tho, I did this as a make table query and the query is setting the comments field as short text so data is truncated

 

[June7]

Surprising about the brackets.

Interesting, I found thread where issue was field created as memo and not just text. I cannot replicate that. Cannot find example of creating table with memo/longtext field via SQL in Access.

Why CREATE action? Why not build destination table then use INSERT SELECT action?

 

[GC2010]

Surprising about the brackets.

Interesting, I found thread where issue was field created as memo and not just text. I cannot replicate that. Cannot find example of creating table with memo/longtext field via SQL in Access.

Why CREATE action? Why not build destination table then use INSERT SELECT action?

For some reason if I do an INSERT statement it performs the insert on BOTH my linked SharePoint table as well as the local Access Table. It's very peculiar as if I do an insert from local access table to local access table only the destination table receives the insert.

I've come up with a work-around but hitting a snag with escaping characters. For example, this is what I have ....

Function UpdateLocalTableWithFullComments()
'we have already run the make table query and run an alter table statement to create the comment field with a long text data type
Dim aNames As Variant
Dim aName As Variant
Dim rs As DAO.Recordset("Select [account name] FROM [animal tracking] order by [account name] ASC")

With rs
  .MoveLast
  .MoveFirst
  aNames = .GetRows(.RecordCount)
End With

rs.Close

For Each aName in aNames
  colhist CStr(aName)
Next

End Function

Sub colhist(aName As String)
Dim sHistory As String
sHistory = Application.Col.umnHistory("animal tracking", "Comments", "[account name]=" & aName)
DoCmd.RunSQL "Update LAT Set Comments = '" & sHistory & "';"

Now the DoCmd.RunSQL line throws an error because sometimes the comments will have single quotes like below, which of course throws off the update statement. Is there a quick function out there or a quick way to "escape" all the single quotes in the string so that the update statement will execute as needed?

"06/30/2019 - Rec'v email stating app was buggy tonight."
"06/20/2019 - canc'ld call - App was running normal"
"06/18/2019 - App was down for 2 hours"

 

[June7]

Don't need the middle-man array. Use Replace() function to double the apostrophes. There is an extra dot in Col.umnHistory.

With rs
    .MoveLast
    .MoveFirst
    Do While Not .EOF
        sHistory = Application.ColumnHistory("animal tracking", "Comments", "[account name]=" & ![account name])
        DoCmd.RunSQL "Update LAT Set Comments = '" & Replace(sHistory, "'", "''") & "';"
        .MoveNext
    Loop
End With

Why don't you do a join between LAT and query object that has ColumnHistory function and run a single update action instead of looping recordset?

If you want to avoid warnings for this action, use:
DoCmd.SetWarnings False
…
DoCmd.Set Warnings True

or

CurrentDb.Execute "UPDATE ..."