Run Time 3703 - Operation must use an updateable query

[Mau5Trap5]

Hi all,

I am trying to update to a SQL table via Access form using ODBC connection.

The form has a selection of unbound fields that once populated I wish to be updated to relevant fields in SQL table.

I have written the attaced code attached to a button at the bottom of the form. i was expecting this to update the records in the SQL table. However, I get the follwoing message

Run Time error '3703' Operation must use an updateable query

I am not entirely sure why this is happening, could be something to do permissions, or could be the way I have setup the query. Would appreciate any help I can get.

DoCmd.RunSQL ("UPDATE dbo_EmployeeContact INNER JOIN ((dbo_EmployeeEmergencyContact INNER JOIN ((SELECT [%$##@_Alias].empid, [Surname] & ' ' & [Firstname] AS EmpName FROM (SELECT dbo_EmployeeContact.empid FROM dbo_EmployeeContact GROUP BY dbo_EmployeeContact.empid)  AS [%$##@_Alias] INNER JOIN dbo_Employee ON [%$##@_Alias].empid=dbo_Employee.empid GROUP BY [%$##@_Alias].empid, [Surname] & ' ' & [Firstname])  AS [%$##@_Alias] INNER JOIN dbo_Employee ON [%$##@_Alias].empid = dbo_Employee.empid) ON dbo_EmployeeEmergencyContact.empid = [%$##@_Alias].empid) INNER JOIN tblNetworkUsers ON [%$##@_Alias].EmpName = tblNetworkUsers.Name) ON dbo_EmployeeContact.empid = dbo_Employee.empid SET dbo_Employee.Firstname = [Forms]![frmPersonalInformationUpdate]![FName], dbo_Employee.Surname = [Forms]![frmPersonalInformationUpdate]![Surname], dbo_EmployeeContact.address1 = [Forms]![frmPersonalInformationUpdate]![Add1], dbo_EmployeeContact.address2 = [Forms]![frmPersonalInformationUpdate]![Add2]," & _
"dbo_EmployeeContact.address3 = [Forms]![frmPersonalInformationUpdate]![Add3], dbo_EmployeeContact.address4 = [Forms]![frmPersonalInformationUpdate]![Add4], dbo_EmployeeContact.address5 = [Forms]![frmPersonalInformationUpdate]![Add5], dbo_EmployeeContact.postcode = [Forms]![frmPersonalInformationUpdate]![PC], dbo_Employee.dob = [Forms]![frmPersonalInformationUpdate]![DOB], dbo_EmployeeContact.telephone = [Forms]![frmPersonalInformationUpdate]![Landline], dbo_EmployeeContact.mobilePhone = [Forms]![frmPersonalInformationUpdate]![Mobile], dbo_EmployeeEmergencyContact.Name = [Forms]![frmPersonalInformationUpdate]![EmergencyContact], dbo_EmployeeEmergencyContact.Relationship = [Forms]![frmPersonalInformationUpdate]![Relationship], dbo_EmployeeEmergencyContact.Telephone1 = [Forms]![frmPersonalInformationUpdate]![ECTelephone]" & _
"WHERE (((tblNetworkUsers.NetworkUser) = GetNetworkUserName()) And ((dbo_EmployeeContact.mainContact) = True) And ((dbo_EmployeeEmergencyContact.Type) = 'Next of Kin 1')) ")

Thanks in advance

 

[RuralGuy]

Maybe this link will help: http://allenbrowne.com/ser-61.html

 

[Minty]

I'm afraid without some serious reformatting of that SQL string I doubt anyone will take the time to help. There are some 2000 characters in 3 lines, it's virtually unreadable.

To try and help - can you create the query in the query editor first.

 

[Mau5Trap5]

Quite right you are.

Not particularly readable, will redo it.

Thanks

 

[Mau5Trap5]

In fact the first response answers the problem, I have GROUP BY in one my sub queries, rendering the action query read only. Thanks RuralGuy.

I assume creating a temporary table (based on the result of the above SQL) to update the SQL server table would be a work around...

UPDATE dbo_Employee INNER JOIN tblTemp
ON tblTemp.empid = dbo_Employee.empid 
SET dbo_Employee.Firstname = tblTemp.Firstname, dbo_Employee.Surname = tblTemp.Surname etc etc etc

??

 

[Frothingslosh]

Yes, a temporary table would work.