Solved

cannopt run this from using docmd

Posted on 2011-03-19
5
279 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35171361
what error does it currently give?
0
 

Author Comment

by:PeterBaileyUk
ID: 35171376
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
ID: 35171438
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
ID: 35171932
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
ID: 35171994
was horrible but got there in the end
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 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