Operation must use an updateable query 1

[MacroAlan]

My form is displaying the results of a search and the user has indicated that the data is wrong and typed the new stuff in another field. I try to update based on the old information:

upSQ = "UPDATE dbo_DMPlatform SET "
    upSQ = upSQ & " dbo_DMPlatform.Program = " & Chr(34) & Me.txt[COLOR=#A40000]DM[/color]prog.Value & Chr(34) & ","
    upSQ = upSQ & " dbo_DMPlatform.SubProgram = " & Chr(34) & Me.txt[COLOR=#A40000]DM[/color]sub.Value & Chr(34)
    upSQ = upSQ & " WHERE (((dbo_DMPlatform.Program)=" & Chr(34) & Me.txt[b]MR[/b]prog.Value & Chr(34) & ") AND "
    upSQ = upSQ & " ((dbo_DMPlatform.SubProgram)=" & Chr(34) & Me.txt[b]MR[/b]sub.Value & Chr(34) & ") AND "
    upSQ = upSQ & " ((dbo_DMPlatform.PART)=" & Chr(34) & Me.txtPartSearch.Value & Chr(34) & "));"
    DoCmd.RunSQL upSQ

and the compiled SQL looks like

UPDATE dbo_DMPlatform SET dbo_DMPlatform.Program = "blue 747", dbo_DMPlatform.SubProgram = "slu sub" WHERE (((dbo_DMPlatform.Program)="added 747") AND  ((dbo_DMPlatform.SubProgram)="sub2") AND  ((dbo_DMPlatform.PART)="114S1822-999"));

I have no joins. I continue to get the “must use an updateable query” message.

Can anyone help?



Alan

 

[dhookom]

It looks like this might be a linked SQL server table. Do you have write permissions on the table? If you open the table in datasheet view can you make edits?


Duane
Hook'D on Access
MS Access MVP

 

[MacroAlan]

Now I remember! The BA said that the customer would not be able to delete; only add to the table. It precludes my app from making updates.



Alan

 

[MacroAlan]

In the end, I built pass-through queries and set up connection strings like the online samples. I was then able to disconnect the linked tables.It runs quickly.


Alan

 

[Andrzejek]

You don't need to repeat the name of the table with each field, and you may want to use ' instead of " Chr(34)

upSQ = "UPDATE dbo_DMPlatform SET "
upSQ = upSQ & " Program       = '" & Me.txtDMprog.Value & "',"
upSQ = upSQ & " SubProgram    = '" & Me.txtDMsub.Value  & "' "
upSQ = upSQ & " WHERE Program = '" & Me.txtMRprog.Value & "' AND "
upSQ = upSQ & " SubProgram    = '" & Me.txtMRsub.Value  & "' AND "
upSQ = upSQ & " PART          = '" & Me.txtPartSearch.Value & "';"
DoCmd.RunSQL upSQ

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.