• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

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];
0
StevenPMoffat
Asked:
StevenPMoffat
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now