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

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];
StevenPMoffatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill RossCommented:
Hi,

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

The Query analyzer will prevent the query from working.

Regards,

Bill
0
StevenPMoffatAuthor 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?
0
Bill RossCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StevenPMoffatAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.