Using Access Update Query to update local table from a linked Share Point list

StevenPMoffat
StevenPMoffat used Ask the Experts™
on
I have an update query that works on updating Table A in my access database with the latest information from Table B also in my access database.     However, I would like Table B to be a linked Share Point list in the access database, but when I set this up and run the update query, I get "Cannot Update. Database or object is in read-only".  

Yet I can delete all entries in Table A and use an Append Query to take entries from Table B and put in Table A.

This is my query.   Table A is called "Local Task Orders" and Table B is called "Task Orders".

UPDATE [Local Task Order] INNER JOIN [Task Orders] ON [Local Task Order].[Task Number] = [Task Orders].[Task Number] SET [Local Task Order].[Contract Record Number] = [Task Orders]![Contract Record Number], [Local Task Order].[Task Order Status] = [Task Orders]![Task Order Status], [Local Task Order].[Lead Technical Contractor] = [Task Orders]![Lead Technical Contractor];
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill RossProgrammer

Commented:
Hi,

Use the above in a Docmd.executeSQL "....." statement.

The Query analyzer will prevent the query from working.

Regards,

Bill

Author

Commented:
Gives me the same error.   I put Docmd.RunSQL with the above in a function and then executed it.    I couldn't find docmn.executesql.    Should I have done something differently?
Programmer
Commented:
Hi,

You're correct.  Try one more idea.

Set a variable to the SQL statement and use the execute method like below.

Dim strSQL as String

strSQL="UPDATE [Local Task Order] INNER JOIN [Task Orders] ON [Local Task Order].[Task Number] = [Task Orders].[Task Number] SET [Local Task Order].[Contract Record Number] = [Task Orders]![Contract Record Number], [Local Task Order].[Task Order Status] = [Task Orders]![Task Order Status], [Local Task Order].[Lead Technical Contractor] = [Task Orders]![Lead Technical Contractor];"

currentdb.execute(strSQL)

See if that returns the error.  If so, then the query returns multiple records and the update statement will not work.

Check that  [Local Task Order].[Task Number] and  [Task Orders].[Task Number] are primery keys in the 2 tables.

Regards,

Bill

Author

Commented:
Thanks for the Help Bill.   I got the same error.   While I have control over the primary key of the local table, I do not have control over the primary key of the linked table and it is not the task Number field so I could not change that.  I suspect that it is a multiple record thing and I will have to work out another workaround.   Probably a clear and append method.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial