Solved

cannopt run this from using docmd

Posted on 2011-03-19
5
248 Views
Last Modified: 2013-11-27
Can the code be amended so that it will run like this:

DoCmd.RunSQL qryUpdateDuplicateVariantDifftoTrue
UPDATE TblSMMTDataDifferences SET TblSMMTDataDifferences.variantDiff = True
WHERE (((TblSMMTDataDifferences.[MVRIS CODE]) In (SELECT [MVRIS CODE Field]
	FROM [Find duplicates for TblSMMTDataDifferences] 
)));

Open in new window

0
Comment
Question by:PeterBaileyUk
  • 3
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
what error does it currently give?
0
 

Author Comment

by:PeterBaileyUk
Comment Utility
3129 invalid sql statement expected delete insert procedure select or update

have tried openquery and command execute all fail

'DoCmd.OpenQuery "qryUpdateDuplicateVariantDifftoTrue"
DoCmd.RunSQL qryUpdateDuplicateVariantDifftoTrue
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
Comment Utility
and you've tried

DoCmd.RunSQL &_
"UPDATE TblSMMTDataDifferences SET TblSMMTDataDifferences.variantDiff = True
WHERE TblSMMTDataDifferences.[MVRIS CODE]) In (SELECT [MVRIS CODE Field]
      FROM [Find duplicates for TblSMMTDataDifferences] )"
0
 

Author Comment

by:PeterBaileyUk
Comment Utility
ok the above worked but the next docmd has a statement with a long sql and it wont allow line continuations unless i have very long statements, now it says error in from clause. why cant access run a query from the vba
DoCmd.RunSQL "INSERT INTO TempTblSMMTDataDifferences ( [INTRO DATE], [Previous Intro Date], MARQUE, [Previous Marque], [MODEL RANGE], [Previous Model Range], [RANGE SERIES], [Previous Range Series], VARIANT, [Previous Variant], CC, [Previous CC], [NOM CC], [Previous Nom CC], DOORS, [Previous DOORS], [BODY TYPE], [Previous BODY TYPE], TRANSMISSION," _
& " [Previous TRANSMISSION], FUEL, [Previous FUEL], ASPIRATION, [Previous ASPIRATION], [DRIVE TYPE], [Previous DRIVE TYPE], [DRIVING AXLE], [Previous DRIVING AXLE], [FORWARD GEARS], [Previous FORWARD GEARS], SEATS, [Previous SEATS], [ENGINE MODEL], [Previous ENGINE MODEL], Expr1034, [NO CYLINDERS], [Previous NO CYLINDERS], [VALVES PER CYLINDER]," _
& " [Previous VALVES PER CYLINDER], [VALVE GEAR], [Previous VALVE GEAR], [POWER KW], [Previous POWER KW], [CALC BHP], [Previous CALC BHP], WHEELBASE, [Previous WHEELBASE], GVW," _
& " [Previous GVW], [CAB TYPE], [Previous CAB TYPE], [VAN ROOF CONFIG], [Previous VAN ROOF CONFIG], [WHEELBASE TYPE], [Previous WHEELBASE TYPE], CalcNomCC, [Previous CalcNomCC], Introdiff, MarqueDiff, ModelRangeDiff, RangeSeriesDiff, CCDiff, NomCCDiff, DoorsDiff, BodyTypeDiff, TransmissionDiff, FuelDiff, AspirationDiff," _
& " DriveTypeDiff, DrivingAxleDiff, ForwardGearsDiff, SeatsDiff, EngineModelDiff, NoCylDiff, ValvespercylDiff, valvegearDiff, powerkwDiff, calcbhpDiff, gvwDiff, cabDiff, vanroofDiff, wheelbasetypeDiff, [MVRIS CODE], variantDiff, AbiMatchedStr2, AbiMatchedStr, CapMatchedStr2, CapMatchedStr, ContinentalMatchedStr, ContinentalMatchedStr2," _
& " GlassMatchedStr, GlassMatchedStr2, HalfordMatchedStr, HalfordMatchedStr2, IDSMatchedStr, IDSMatchedStr2, MichelinMatchedStr, MichelinMatchedStr2, TechDocMatchedStr, TechDocMatchedStr2, VividMatchedStr, VividMatchedStr2, SMMTComparitorDate, ComparitorType ) SELECT TblSMMTDataDifferences.[INTRO DATE], TblSMMTDataDifferences.[Previous Intro Date], TblSMMTDataDifferences.MARQUE, TblSMMTDataDifferences.[Previous Marque]," _
& " TblSMMTDataDifferences.[MODEL RANGE], TblSMMTDataDifferences.[Previous Model Range], TblSMMTDataDifferences.[RANGE SERIES], TblSMMTDataDifferences.[Previous Range Series], TblSMMTDataDifferences.VARIANT, TblSMMTDataDifferences.[Previous Variant], TblSMMTDataDifferences.CC, TblSMMTDataDifferences.[Previous CC], TblSMMTDataDifferences.[NOM CC], TblSMMTDataDifferences.[Previous Nom CC] , TblSMMTDataDifferences.DOORS, TblSMMTDataDifferences.[Previous DOORS], TblSMMTDataDifferences.[BODY TYPE]," _
& " TblSMMTDataDifferences.[Previous BODY TYPE] , TblSMMTDataDifferences.TRANSMISSION, TblSMMTDataDifferences.[Previous TRANSMISSION], TblSMMTDataDifferences.FUEL, TblSMMTDataDifferences.[Previous FUEL], TblSMMTDataDifferences.ASPIRATION, TblSMMTDataDifferences.[Previous ASPIRATION], TblSMMTDataDifferences.[DRIVE TYPE], TblSMMTDataDifferences.[Previous DRIVE TYPE] , TblSMMTDataDifferences.[DRIVING AXLE] , TblSMMTDataDifferences.[Previous DRIVING AXLE], TblSMMTDataDifferences.[FORWARD GEARS]," _
& " TblSMMTDataDifferences.[Previous FORWARD GEARS] , TblSMMTDataDifferences.SEATS, TblSMMTDataDifferences.[Previous SEATS], TblSMMTDataDifferences.[ENGINE MODEL], TblSMMTDataDifferences.[Previous ENGINE MODEL]," _
& " TblSMMTDataDifferences.Expr1034, TblSMMTDataDifferences.[NO CYLINDERS], TblSMMTDataDifferences.[Previous NO CYLINDERS], TblSMMTDataDifferences.[VALVES PER CYLINDER], TblSMMTDataDifferences.[Previous VALVES PER CYLINDER], TblSMMTDataDifferences.[VALVE GEAR], TblSMMTDataDifferences.[Previous VALVE GEAR], TblSMMTDataDifferences.[POWER KW]," _
& " TblSMMTDataDifferences.[Previous POWER KW], TblSMMTDataDifferences.[CALC BHP], TblSMMTDataDifferences.[Previous CALC BHP], TblSMMTDataDifferences.WHEELBASE, TblSMMTDataDifferences.[Previous WHEELBASE], TblSMMTDataDifferences.GVW, TblSMMTDataDifferences.[Previous GVW], TblSMMTDataDifferences.[CAB TYPE]," _
& " TblSMMTDataDifferences.[Previous CAB TYPE], TblSMMTDataDifferences.[VAN ROOF CONFIG] , TblSMMTDataDifferences.[Previous VAN ROOF CONFIG], TblSMMTDataDifferences.[WHEELBASE TYPE], TblSMMTDataDifferences.[Previous WHEELBASE TYPE]," _
& " TblSMMTDataDifferences.CalcNomCC, TblSMMTDataDifferences.[Previous CalcNomCC], TblSMMTDataDifferences.Introdiff, TblSMMTDataDifferences.MarqueDiff, TblSMMTDataDifferences.ModelRangeDiff, TblSMMTDataDifferences.RangeSeriesDiff, TblSMMTDataDifferences.CCDiff, TblSMMTDataDifferences.NomCCDiff, TblSMMTDataDifferences.DoorsDiff," _
& " TblSMMTDataDifferences.BodyTypeDiff, TblSMMTDataDifferences.TransmissionDiff, TblSMMTDataDifferences.FuelDiff, TblSMMTDataDifferences.AspirationDiff, TblSMMTDataDifferences.DriveTypeDiff, TblSMMTDataDifferences.DrivingAxleDiff, TblSMMTDataDifferences.ForwardGearsDiff, TblSMMTDataDifferences.SeatsDiff, TblSMMTDataDifferences.EngineModelDiff, TblSMMTDataDifferences.NoCylDiff, TblSMMTDataDifferences.ValvespercylDiff, TblSMMTDataDifferences.valvegearDiff, TblSMMTDataDifferences.powerkwDiff," _
& " TblSMMTDataDifferences.calcbhpDiff , TblSMMTDataDifferences.gvwDiff , TblSMMTDataDifferences.cabDiff, TblSMMTDataDifferences.vanroofDiff, TblSMMTDataDifferences.wheelbasetypeDiff, TblSMMTDataDifferences.[MVRIS CODE]," _
& " TblSMMTDataDifferences.variantDiff , TblSMMTDataDifferences.AbiMatchedStr2, TblSMMTDataDifferences.AbiMatchedStr, TblSMMTDataDifferences.CapMatchedStr2, TblSMMTDataDifferences.CapMatchedStr, TblSMMTDataDifferences.ContinentalMatchedStr, TblSMMTDataDifferences.ContinentalMatchedStr2, TblSMMTDataDifferences.GlassMatchedStr, TblSMMTDataDifferences.GlassMatchedStr2, TblSMMTDataDifferences.HalfordMatchedStr, TblSMMTDataDifferences.HalfordMatchedStr2, TblSMMTDataDifferences.IDSMatchedStr, TblSMMTDataDifferences.IDSMatchedStr2, TblSMMTDataDifferences.MichelinMatchedStr, TblSMMTDataDifferences.MichelinMatchedStr2, TblSMMTDataDifferences.TechDocMatchedStr, TblSMMTDataDifferences.TechDocMatchedStr2, TblSMMTDataDifferences.VividMatchedStr, TblSMMTDataDifferences.VividMatchedStr2, TblSMMTDataDifferences.SMMTComparitorDate, TblSMMTDataDifferences.ComparitorType" _
& " FROM TblSMMTDataDifferencesGROUP BY TblSMMTDataDifferences.[INTRO DATE], TblSMMTDataDifferences.[Previous Intro Date], TblSMMTDataDifferences.MARQUE, TblSMMTDataDifferences.[Previous Marque]," _
& " TblSMMTDataDifferences.[MODEL RANGE] , TblSMMTDataDifferences.[Previous Model Range], TblSMMTDataDifferences.[RANGE SERIES], TblSMMTDataDifferences.[Previous Range Series], TblSMMTDataDifferences.VARIANT , TblSMMTDataDifferences.[Previous Variant], TblSMMTDataDifferences.cc, TblSMMTDataDifferences.[Previous CC], TblSMMTDataDifferences.[NOM CC], TblSMMTDataDifferences.[Previous Nom CC], TblSMMTDataDifferences.DOORS, TblSMMTDataDifferences.[Previous DOORS], TblSMMTDataDifferences.[BODY TYPE], TblSMMTDataDifferences.[Previous BODY TYPE], TblSMMTDataDifferences.TRANSMISSION, TblSMMTDataDifferences.[Previous TRANSMISSION], TblSMMTDataDifferences.FUEL, TblSMMTDataDifferences.[Previous FUEL], TblSMMTDataDifferences.ASPIRATION, TblSMMTDataDifferences.[Previous ASPIRATION], TblSMMTDataDifferences.[DRIVE TYPE], TblSMMTDataDifferences.[Previous DRIVE TYPE]," _
& " TblSMMTDataDifferences.[DRIVING AXLE] , TblSMMTDataDifferences.[Previous DRIVING AXLE], TblSMMTDataDifferences.[FORWARD GEARS], TblSMMTDataDifferences.[Previous FORWARD GEARS]," _
& " TblSMMTDataDifferences.SEATS, TblSMMTDataDifferences.[Previous SEATS], TblSMMTDataDifferences.[ENGINE MODEL], TblSMMTDataDifferences.[Previous ENGINE MODEL], TblSMMTDataDifferences.Expr1034, TblSMMTDataDifferences.[NO CYLINDERS], TblSMMTDataDifferences.[Previous NO CYLINDERS], TblSMMTDataDifferences.[VALVES PER CYLINDER], TblSMMTDataDifferences.[Previous VALVES PER CYLINDER], TblSMMTDataDifferences.[VALVE GEAR], TblSMMTDataDifferences.[Previous VALVE GEAR], TblSMMTDataDifferences.[POWER KW], TblSMMTDataDifferences.[Previous POWER KW], TblSMMTDataDifferences.[CALC BHP], TblSMMTDataDifferences.[Previous CALC BHP], TblSMMTDataDifferences.WHEELBASE, TblSMMTDataDifferences.[Previous WHEELBASE], TblSMMTDataDifferences.GVW, TblSMMTDataDifferences.[Previous GVW], TblSMMTDataDifferences.[CAB TYPE], TblSMMTDataDifferences.[Previous CAB TYPE], TblSMMTDataDifferences.[Previous WHEELBASE TYPE], TblSMMTDataDifferences.CalcNomCC, TblSMMTDataDifferences.[Previous CalcNomCC], TblSMMTDataDifferences.Introdiff," _
& " TblSMMTDataDifferences.MarqueDiff, TblSMMTDataDifferences.ModelRangeDiff, TblSMMTDataDifferences.RangeSeriesDiff, TblSMMTDataDifferences.CCDiff, TblSMMTDataDifferences.NomCCDiff, TblSMMTDataDifferences.DoorsDiff, TblSMMTDataDifferences.BodyTypeDiff, TblSMMTDataDifferences.TransmissionDiff, TblSMMTDataDifferences.FuelDiff, TblSMMTDataDifferences.AspirationDiff," _
& " TblSMMTDataDifferences.DriveTypeDiff, TblSMMTDataDifferences.DrivingAxleDiff, TblSMMTDataDifferences.ForwardGearsDiff, TblSMMTDataDifferences.SeatsDiff, TblSMMTDataDifferences.EngineModelDiff, TblSMMTDataDifferences.NoCylDiff, TblSMMTDataDifferences.ValvespercylDiff, TblSMMTDataDifferences.valvegearDiff, TblSMMTDataDifferences.powerkwDiff," _
& " TblSMMTDataDifferences.CalcbhpDiff , TblSMMTDataDifferences.gvwDiff, TblSMMTDataDifferences.cabDiff, TblSMMTDataDifferences.vanroofDiff, TblSMMTDataDifferences.wheelbasetypeDiff," _
& " TblSMMTDataDifferences.[MVRIS CODE] , TblSMMTDataDifferences.variantDiff, TblSMMTDataDifferences.AbiMatchedStr2, TblSMMTDataDifferences.AbiMatchedStr, TblSMMTDataDifferences.CapMatchedStr2, TblSMMTDataDifferences.CapMatchedStr, TblSMMTDataDifferences.ContinentalMatchedStr, TblSMMTDataDifferences.ContinentalMatchedStr2, TblSMMTDataDifferences.GlassMatchedStr, TblSMMTDataDifferences.GlassMatchedStr2, TblSMMTDataDifferences.HalfordMatchedStr, TblSMMTDataDifferences.HalfordMatchedStr2, TblSMMTDataDifferences.IDSMatchedStr, TblSMMTDataDifferences.IDSMatchedStr2, TblSMMTDataDifferences.MichelinMatchedStr, TblSMMTDataDifferences.MichelinMatchedStr2, TblSMMTDataDifferences.TechDocMatchedStr, TblSMMTDataDifferences.TechDocMatchedStr2, TblSMMTDataDifferences.VividMatchedStr, TblSMMTDataDifferences.VividMatchedStr2, TblSMMTDataDifferences.SMMTComparitorDate," _
& " TblSMMTDaaDifferences.ComparitorType HAVING (((Count(*))>1));"

Open in new window

0
 

Author Closing Comment

by:PeterBaileyUk
Comment Utility
was horrible but got there in the end
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now