Good Morning everone,
i'm having a real problem figuring this one out. what i'm trying to do is run a query to update a table on the SQL Server but it keeps erroring out. i have 2 ways i'm trying to accomplish this. if you can help me with a solution for at least one of them it will fix my issue. Also, all tables this is referencing is on the SQL Server (No Local tables in the access Frontend).
Attempt # 1
when running this code it gives the error:
"Compile error: Type mismatch" and highlights "rsDif" in the sqlUp SQL statement.
Attempt # 2
when running this code it gives the error:
Run-time error '3075'
Syntax error (missing operator) in query expression 'rs.Costrs.CostEach FROM ADJ_SUBtemp INNER JOIN (SELECT sum(Cost_per_Each - COST) as CostEach, TRANS_ID FROM tblTempAdjFromExclDif GROUP BY TRANS_ID) rs ON ADJ_SUBtemp.ADJ_ID = rs.TRANS_ID'
any help is most appreciated.
i'm having a real problem figuring this one out. what i'm trying to do is run a query to update a table on the SQL Server but it keeps erroring out. i have 2 ways i'm trying to accomplish this. if you can help me with a solution for at least one of them it will fix my issue. Also, all tables this is referencing is on the SQL Server (No Local tables in the access Frontend).
Attempt # 1
Code:
Dim sqlGroupDif As String
Dim sqlUp As String
Dim db As DAO.Database
Dim rsDif As DAO.Recordset
Set db = OpenDatabase("", False, False, "Driver={SQL Server Native Client 11.0};Server=L563931F034C53\ICQADB;Database=QA;Uid=QALogIn;Pwd=qalogin;")
sqlGroupDif = "SELECT sum(Cost_per_Each - COST) as CostEach, TRANS_ID FROM tblTempAdjFromExclDif GROUP BY TRANS_ID"
Set rsDif = db.OpenRecordset(sqlGroupDif, dbOpenDynaset, dbSeeChanges)
sqlUp = "UPDATE ADJ_SUBtemp " _
& "Set TotCostOff = rs.CostEach " _
& "FROM ADJ_SUBtemp INNER JOIN " & rsDif & " as rs " _
& "ON ADJ_SUBtemp.ADJ_ID = rs.TRANS_ID;"
db.Execute sqlUp, dbSeeChanges
when running this code it gives the error:
"Compile error: Type mismatch" and highlights "rsDif" in the sqlUp SQL statement.
Attempt # 2
Code:
Dim sqlUp As String
Dim db As DAO.Database
Dim rsDif As DAO.Recordset
Set db = OpenDatabase("", False, False, "Driver={SQL Server Native Client 11.0};Server=L563931F034C53\ICQADB;Database=QA;Uid=QALogIn;Pwd=qalogin;")
sqlUp = "UPDATE ADJ_SUBtemp " _
& "Set TotCostOff = rs.CostEach " _
& "FROM ADJ_SUBtemp INNER JOIN (SELECT sum(Cost_per_Each - COST) as CostEach, TRANS_ID FROM tblTempAdjFromExclDif GROUP BY TRANS_ID) rs " _
& "ON ADJ_SUBtemp.ADJ_ID = rs.TRANS_ID;"
db.Execute sqlUp, dbSeeChanges
when running this code it gives the error:
Run-time error '3075'
Syntax error (missing operator) in query expression 'rs.Costrs.CostEach FROM ADJ_SUBtemp INNER JOIN (SELECT sum(Cost_per_Each - COST) as CostEach, TRANS_ID FROM tblTempAdjFromExclDif GROUP BY TRANS_ID) rs ON ADJ_SUBtemp.ADJ_ID = rs.TRANS_ID'
any help is most appreciated.