Insert record in Sharepoint list with VBA

All

I've been trying to add a record to a Sharepointlist, but this fails for some reason. In first step I made a connection to the list to retrieve the values in field Title. This works as expected with code below.

Private Sub test() Dim conSharePoint As ADODB.Connection Dim rst As ADODB.Recordset Dim strConnection As String Dim intIMEX As Integer Dim strDatabase As String Dim strList As String intIMEX = 2 'IMEX=1 for Read only, IMEX=2 for Read/Write strDatabase = "http://mysharepoint"
strList = "{F9050C5F-0528-47EE-9A29-9561DEB7B778}" strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=" & intIMEX & ";RetrieveIds=Yes;" & "DATABASE=" & strDatabase & ";" & "LIST=" & strList & ";VIEW=;RetrieveIds=Yes;TABLE=" & strList Set conSharePoint = New ADODB.Connection conSharePoint.Open (strConnection) Set rst = New ADODB.Recordset strsql = "select * from [" & strList & "]" rst.Open strsql, conSharePoint, adOpenDynamic, adLockOptimistic, adCmdText While Not rst.EOF Debug.Print rst!title rst.MoveNext Wend End Sub

Next, I added next lines to code above (between the "Wend" and "End Sub"

rst.Close
strsql = "insert into [" & strList & "](title) values('test value')"
rst.Open strsql, conSharePoint, adOpenDynamic, adLockOptimistic, adCmdText

The rst.Open command fails with Run-time error '-2147217887 (80040e21)': Cannot update 'title'; field not updateable

I removed those three lines and replaced them with

With rst
    .AddNew
    .Fields("title") = "test value"
    .Update
End With

This fails on ".Update" with error '-2147217887 (8004e21): The Microsoft Access database engine could not find object {F9050C5F-0528-47EE-9A29-9561DEB7B778}. Make sure the object exists and that you spell its name and the path name correctly.

Does someone have an idea what's wrong in code above?

Regards

January 7th, 2014 10:35am

Hi,
For this issue, I'm trying to involve someone familiar with this topic to further look at it.
Thanks,
Qiao
Forum Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
Free Windows Admin Tool Kit Click here and download it now
January 10th, 2014 9:01am

hi kvhoof,

if i may ask, do the username that passed have the permission to edit/add/delete at the ms access?

and also please check if 'title' and 'Title' have case sensitive.

example:

http://ankushbhatia.wordpress.com/tag/ace/

http://office.microsoft.com/en-in/access-help/HV080761285.aspx

January 10th, 2014 3:08pm

Aries

I am owner of the Sharepoint list. I just did a test by creating a new Access-database and created a link to my Sharepoint-list via "External Data" - More - Sharepoint list. When I open the linked table, I can add/edit/remove records.

I changed in my code the word 'title' to 'Title', but this neither resolves the problem.

If I open the design of the linked table, I do see more fields than only Title. So I've changed my code, but this resolves is same error: Run-time error '-2147217887 (80040e21)': Cannot update 'title'; field not updateable

strsql = "insert into [" & strList & "](Title,[Content Type],Attachments,[Workflow Instance ID],[File Type],[Created By],[Modified By],[Modified],[Created],[URL Path],[Path],[Item Type],[Encoded Absolute URL]) values('test value','','','','','','','1/7/2014 2:02 AM','1/7/2014 2:02 AM','','','','')"

Regards
kvhoof

Free Windows Admin Tool Kit Click here and download it now
January 10th, 2014 3:37pm

hi kvhoof,

please be reminded that changing data inside sharepoint database will be not supported.

http://support.microsoft.com/kb/841057/en-us

there are some parts that i am curious about,

1. if you put an empty value to the column, will it able to process without error?

2. if you put other table than 'Title', what is the result?

3. there is an adlock requirement at your code, as i know the adLockOptimistic, will lock when there is an update process, so that other people cant change the data inside, if you try to change it to adLockPessimistic, will it result as the same.

because after discussion with access team, based on the error message,

1. the column, may not be easily editable as it is a linked table, it may to be a foreign key, so that it may not be able to update.

2. query error, for example, try to use 'rst.execute' instead 'rst.open'.

3. the columns still in lock mode.

January 12th, 2014 11:08pm

Aries

I tried your suggestions

  1. Adding an empty value: returns in same error
  2. Add record to another field/sharepoint list: returns in similar error
  3. Use adLockPessimistic: returns in same error
  4. Foreign key: there are no foreign keys
  5. rst.execute: the adodb.recordset does not support a method .execute

Regards
kvhoof

Free Windows Admin Tool Kit Click here and download it now
January 13th, 2014 5:22am

hi kvhoof,

i will ask the dev team regarding this ADO method,

i quote:

"It is not a good idea to use the Source argument of the Open method to perform an action query that does not return records because there is no easy way to determine whether the call succeeded. The Recordset returned by such a query will be closed. To perform a query that does not return records, such as a SQL INSERT statement, call the Execute method of a Command object or the Execute method of a Connection object instead."http://msdn.microsoft.com/en-us/library/windows/desktop/ms675544(v=vs.85).aspx

meanwhile please have a look at the method in ADO also, perhaps it can give you a heads up,

http://msdn.microsoft.com/en-us/library/windows/desktop/ms681510(v=vs.85).aspx

http://msdn.microsoft.com/en-us/library/windows/desktop/ms675841(v=vs.85).aspx

January 13th, 2014 5:37am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics