Access connected to sharepoint list

[thansson]

Hi

I have 2 list I update from Access to sharepoint.

First list I run 3 query (Add, Update, Delete) no problems.

Second Liste the same 3 query (Add, Update, Delete).

When i come to the last Query Delete most of the times I get the failure (Cannot update, database or object is read only)

If i keep running the query sometimes it will work. ( Ivahe set the cache to "Never")

Did anyone have this issue?

Regards

Tony

[#2]

@thansson 

One possibility, based on that brief overview, is that the SharePoint list is still being locked from the previous query while some process continues, possibly re-indexing or something like that. What happens if you wait longer between running the various queries? 

How many records are in these lists? SharePoint is definitely handicapped when processing larger lists. The exact meaning of "larger" can be flexible but anything over 5,000 records might be venturing into that territory. Definitely anything over 25,000 records in a SharePoint list is going to be suspect--performance wise.

[#3]

Hi @George Hepworth 

tks for your anser.

Numbers of Rows is different each time, but around 3000 Rows.

It dont help if I am waiting, if  I close access and reopen, and only run the last query, still the same. If I keep on running the query it Will loose connection, and after that the query has Been executed.

It is Strange it has Been working for a while without problems.

[#4]

@thansson 

How many rows are in that SharePoint list, in total?

While 3,000 is less than the  typical 5,000 where we often see slowdowns in SharePoint lists with Access, if that is 3,000 ADDITIONAL rows, you might well be over-taxing the SP lists.

What you describe suggests that you are approaching, or have passed, some threshold number of records. 

If that's not it, I'd be looking at things like network connections, and so on that can impact performance.

[#5]

Hi@George Hepworth 

It is a total on 3000 rows

[#6]

@thansson 

A total of 3,000 rows isn't huge, even for SP, so I'd be back to looking at two possibilities.

You mentioned this is a delete query? Are you deleting every record in the list, or using a filter to select some subset of the total? If it is 3,000 and that's all of the records, that is one thing. If it is a filtered subset of a larger number, that will be slower, and potentially very slow.

The other possibility is that something else in your environment changed. Unfortunately that often happens and we don't know about it until problems pop up. Since we can't see the system nor what might have changed, we would have to rely on the SharePoint admins for information.

[#7]

@George Hepworth 

Hi thank you

It is a delete query, deleting the difference between to tables, on each 3000 Rows.

It will delete about the Half of the Rows.

[#8]

@thansson 

Okay, that's in line with what I think is typical for bulk deletions, especially where your criteria is to compare two tables and delete some percentage of rows from one of them. I have seen this kind of problem on native Access tables on a few occasions. It might confirm the problem to be coming from that source if you can break down the delete into a small number, say 100. If that makes a significant difference, then you might need to adopt a different approach. One way to do that would be "batches" of say 100 until all of the obsolete records are deleted. Another way might be to create a temp table that contains only the Primary Key for the fields to be deleted as an intermediary step. Then join that temp table to the target table on that PK and see if that is more efficient.

[#9]

@George Hepworth 

Thank you for your time, good for me to know.

Can you give me hint how to only opdate 100 Rows at the time?

Can I do this only by query, or do I need vba?

[#10]

@George Hepworth 

Hi

I now have a vba code counting the effected rows and delete certain number of rows (intNumRecords), inside a loop.

It works in ACCESS but when connected to Sharepoint I get the failure:

Runtime error 3086 when getting to the delete SQL Str.

[code]

strSQL = "SELECT *"
strSQL = strSQL & " FROM SAP_Import2"
strSQL = strSQL & " WHERE SAP_Import2.title = '" & pTitle & "'"
strSQL = strSQL & " ORDER BY Title;"

strSQL = "DELETE FROM (SELECT TOP " & intNumRecords & " * FROM SAP_Import2 WHERE SAP_Import2.Title = '" & pTitle & "')"

[/code]

How should I make the SQL so it will work on SharePoint ?

[#11]

@thansson I don't know that there should be any difference between an Access table and a SharePoint linked list (table). A couple of things to consider, but these are basically general trouble-shooting steps.

First, I'd stop the code and use Debug.Print to send the SQL String produced by your VBA into the immediate window and then copy that into a query to see what happens there. Sometimes the errors raised that way are more informative and the problem clearer.

Second, I'd want to be sure that there are actually records to delete in the target table, and that the query retrieves them. You can check that by opening your test query as a SELECT before trying to run it.

And finally, are there any foreign key restraints on the SP list that prevent deletions?

[#12]

@George Hepworth 

Hi 

I dont have problems deleting, even with a query. Only if the bulk delete is to Big.

I have used debut.print and it stops at the sql string.

The sql string Can see how Many Rows to delete, and is able to see the fin.title, so

the problem is in the middle of the sql string.

it is working inside access, so it have to a problem in the SQL

[#13]

@thansson 

So, if you use Debug.Print to send the SQL String to the immediate window, and then copy that SQL into a new query and open it, it does return records that will be deleted, so that's not the problem.

[#14]

@thansson 

"it is working inside access, so it have to a problem in the SQL"

I assume you mean that a delete query does delete the records. Well, that means the SQL in that query is right, but the SQL generated in the VBA is not right. And that's the SQL I would like to see tested. Thanks.

[#15 · Accepted solution]

@George Hepworth 

It is working if I only choose 50 Rows each time

Thank you and Merry Christmas

[#16]

@thansson 

This indeed sounds to me like a limitation on the SharePoint side. You'd have to wrestle an explanation out of the SP Site admins if you can get them to pay attention.