Linked Tables From SharePoint Into Access

[GC2010]

Don't need the middle-man array. Use Replace() function to double the apostrophes.

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

You have again saved the day! Thank you so much! I REALLY appreciate it!

 

[June7]

Did edits on my previous post, might review again.

 

[GC2010]

In my testing, I just realized I left out a where clause for the update statement! This is why I always test with garbage data initially ��

The column [Account Name] is a number data type

And I get this error:
Run-time error ‘3464’:
Data type mismatch in criteria expression

And this is the line (posting from mobile so quotes are going to look wonky)

Update lat set comments = ‘“ & replace(sHistory, “‘“, “‘’”) & “‘ where [acccount name] = ‘“ & ![account name] & “‘;”

 

[The_Doc_Man]

The data-type mismatch error is indicative that you provided something to Access in a way that makes it see it in one format whereas what you needed was another format. First, recognize that Access VBA has knowledge of the data type of something, but the SQL portion DOES NOT SEE the information about data types. SQL runs in a separate environment and does what it does only when passed an SQL string. To SQL, this is a numeric comparison:

WHERE X = 1

- but if X was actually stored as the (text) digit "1" for some reason, that is a case of error 3464 not merely looking for, but FINDING a place to happen.

The solution is that you have to convert the number to text, such as the CStr() function, or you have to convert the text to a number, such as the Val() or CLng() function.

WHERE X = CStr(1)
or
WHERE VAL(X) = 1

Obviously, if you are getting the numeric comparand from some other place, the problem will still be to convert one or the other of the comparands (but not both). That way you can get an apples-to-apples comparison.

As it actually appears, your problem is the reverse of what I posted, which doesn't invalidate what I posted. Your clause:

[acccount name] = ‘“ & ![account name] & “‘

leaves the blue part numeric (you said in post 23 it is a number data type) but the red part is clearly text because of those surrounding quotes.

 

[June7]

And I should have noticed the missing WHERE clause.

CurrentDb.Execute "Update LAT Set Comments = '" & Replace(sHistory, "'", "''") & "' WHERE [account name]=" & ![account name]

I still think you can do this with one UPDATE action instead of looping recordset.