Modify SQL Server data in Access using a View

%3CLINGO-SUB%20id%3D%22lingo-sub-1202993%22%20slang%3D%22en-US%22%3EModify%20SQL%20Server%20data%20in%20Access%20using%20a%20View%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1202993%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EIn%20SQL%20Server%20I%20have%20created%20some%20tables.%20A%20main%20'Software'%20table%20with%20some%20text%20fields%20and%20%3CEM%3Eforeign%20keys%26nbsp%3B%3C%2FEM%3Epointing%20to%20ids%20of%20secondary%20tables%20like%20'InstallationType'%20and%20'LicenseType'.%20I%20have%20also%20created%20a%20view%20showing%20the%20labels%20corresponding%20to%20the%20'InstallationTypeId'%20and%20'LicenseTypeId'.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3ERemark%3C%2FU%3E%3A%26nbsp%3BThe%20main%20reason%20I%20am%20using%20a%20View%20is%20that%20I%20have%20to%20do%20some%20calculations%20involving%20several%20tables%20-%26gt%3B%20out%20of%20scope%20of%20this%20post%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22SQLServer-Access.jpg%22%20style%3D%22width%3A%20644px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F174310i4BA2E18AFB0FEEEB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22SQLServer-Access.jpg%22%20alt%3D%22SQLServer-Access.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSPAN%3EIn%20Access%20I%20have%20defined%20a%26nbsp%3B%3C%2FSPAN%3E%3CEM%3Elinked%20table%26nbsp%3B%3C%2FEM%3E%3CSPAN%3Epointing%20to%20the%20view.%20If%20I%20modify%20the%20values%20corresponding%20to%20the%20text%20fields%20(like%20the%20name%20of%20the%20version%20of%20the%20Software)%20this%20is%20ok.%20But%20if%20I%20am%20changing%20the%20value%20of%20the%20'InstallationType'%20label%20(like%20replacing%20'Saas'%20by%20'On%20Premise'%20for%20the%20Salesforce%20row)%20there%20is%20an%20issue.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20label%20of%20the%20'InstallationType'%20(of%20the%20record%20with%20id%20%3D%202)%20is%20modified%20('Saas'%20is%20replaced%20by%20'On%20Premise').%20But%20my%20purpose%20is%20to%20modify%20the%20'InstallationTypeId'%20of%20the%20Salesforce%20row%20in%20the%20'Software'%20table%20instead.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I'm%20wondering%20how%20may%20I%20modify%20the%20'SQL%20Update'%20query%20executed%20in%20Access%3F%20Maybe%20through%20a%20Form%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExpected%20query%2C%20something%20like%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3EUPDATE%3C%2FSTRONG%3E%20Software%3C%2FP%3E%3CP%3E%3CSTRONG%3ESET%3C%2FSTRONG%3E%20InstallationTypeId%20%3D%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B(%3CSTRONG%3ESELECT%3C%2FSTRONG%3E%20Id%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3CSTRONG%3EFROM%3C%2FSTRONG%3E%26nbsp%3BInstallationType%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%3CSTRONG%3EWHERE%3C%2FSTRONG%3E%20InstallationTypeLabel%20%3D%20'On%20Premise')%3C%2FP%3E%3CP%3E%3CSTRONG%3EWHERE%3C%2FSTRONG%3E%20Name%20%3D%20'Salesforce'%20AND%20Version%20%3D%20'Winter%202020'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20sharing%20your%20experiences!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERudy.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1202993%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Visitor

Hello,

In SQL Server I have created some tables. A main 'Software' table with some text fields and foreign keys pointing to ids of secondary tables like 'InstallationType' and 'LicenseType'. I have also created a view showing the labels corresponding to the 'InstallationTypeId' and 'LicenseTypeId'.

 

Remark: The main reason I am using a View is that I have to do some calculations involving several tables -> out of scope of this post

 

SQLServer-Access.jpg

 In Access I have defined a linked table pointing to the view. If I modify the values corresponding to the text fields (like the name of the version of the Software) this is ok. But if I am changing the value of the 'InstallationType' label (like replacing 'Saas' by 'On Premise' for the Salesforce row) there is an issue.

 

The label of the 'InstallationType' (of the record with id = 2) is modified ('Saas' is replaced by 'On Premise'). But my purpose is to modify the 'InstallationTypeId' of the Salesforce row in the 'Software' table instead.

 

So I'm wondering how may I modify the 'SQL Update' query executed in Access? Maybe through a Form?

 

Expected query, something like:

UPDATE Software

SET InstallationTypeId = 

     (SELECT Id

     FROM InstallationType

    WHERE InstallationTypeLabel = 'On Premise')

WHERE Name = 'Salesforce' AND Version = 'Winter 2020'

 

Thanks in advance for sharing your experiences!

 

Rudy.

 

 

0 Replies