I'm trying to create an Access 2016 Form that allows users to correct information located on a SQL Server Table but having trouble. First some restrictions; I can't create anything new in the SQL Server, The users do not have access to the password for the ODBC connection.
I know with an Access Pass-Through Query you can save an ODBC connection, however you have to use VBA to update the query with the data from a Access Form.
Currently my code in VBA is;
mysql = "Update dbo.MRollData Set dbo.MRollData.PolyPart = '" + Me.ActiveControl.Value + "' WHERE dbo.MRollData.LotNum = '" + Me.MRollLotNum.Value + "' AND dbo.MRollData.Side = '" + Me.MRollSide.Value + "' AND dbo.MRollData.MRIndex = '" + Me.MRollIndex.Value + "' AND dbo.MRollData.PolyPart = '" + Me.CurrentValue.Value + "'"
CurrentDb().QueryDefs("PolyPartChange").SQL = mysql
DoCmd.OpenQuery "PolyPartChange", , acEdit
This is activated by an OnClick command, however running this I get run-time error '438' Object doesn't support this property or method. The VBA code was written using a similar bit of code from another database that updates a query that executes a SQL query.
I've also tried to just run the query on its own and keep getting the Error "Query must have at least one destination field"
Am I missing something or is it not possible to run execute an SQL Update query in an Access pass-through query? Also is there an easier way to do what I am trying?
UPDATE; I have a tag listed here that I didn't see auto generate as ActiveControl, this should have been Me.ActualValue.Value
You could try with:
CurrentDb.QueryDefs("PolyPartChange").SQL = mysql
CurrentDb.QueryDefs("PolyPartChange").Execute
The query must be a pass-through query for your SQL syntax to work.