Access crash caused by large sql

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Harel
    #1

    Access crash caused by large sql

    I have a report SQL which have been working for a few weeks. Its large
    and the target database is large. It has 59 join, and I dont think
    there is anything I can do about this, since the DB I use has been
    normalized by pros.

    My problem is I try to had another join to display another value, then
    I get a crash report from MS-Access. Anything I can to do diagnose
    this problem. My development depends on this SQL:

    SELECT
    BASE_VEHICLE_ID ,
    VEHICLE_ID,
    Trim(Make.MakeN ame) as Make,
    Trim(Model.Mode lName) as Model,
    YearID,
    Trim(SubModel.S ubModelName) as Sub,
    Trim(DriveType. DriveTypeName) as DriveType,
    BodyNumDoors.Bo dyNumDoors + "DR" as Doors,
    Trim(BodyType.B odyTypeName) as Body,
    Trim(Transmissi onType.Transmis sionTypeName) as Trans,
    Trim(Transmissi onNumSpeeds.Tra nsmissionNumSpe eds) as TransSpeeds,
    Trim(Transmissi onControlType.T ransmissionCont rolTypeName) as
    TransControl,
    EngineBase.Lite r + "L" as EngLiter,
    EngineBase.CC as EngCC,
    EngineBase.Cyli nders + " CYL" as EngCyl,
    Trim(EngineDesi gnation.EngineD esignationName) as EngineName,
    Trim(Aspiration .AspirationName ) as Aspiration,
    Trim(FuelType.F uelTypeName) as Fuel,
    BrakeType1.Brak eTypeName as FrontBrake,
    BrakeType2.Brak eTypeName as RearBrake,
    BrakeSystem.Bra keSystemName as BrakeName,
    BrakeABS.BrakeA BSName as ABS,
    PART_NUMBER,
    FITMENT_NOTE_DE SC
    INTO
    RAPPORT_MAPPING
    FROM
    APPLICATION_ACE S,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BaseVehicle,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Vehicle,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Make,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Model,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].SubModel,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].DriveType,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97
    20080731.mdb].VehicleToBodyS tyleConfig,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyStyleConfi g,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyNumDoors,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyType,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].VehicleToTrans mission,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Transmission,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionBa se,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionTy pe,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionNu mSpeeds,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97
    20080731.mdb].TransmissionCo ntrolType,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].VehicleToEngin eConfig,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineConfig,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineBase,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineDesignat ion,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Aspiration,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].FuelType,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].VehicleToBrake Config,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeConfig,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeType as
    BrakeType1,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeType as
    BrakeType2,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeSystem,
    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
    \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeABS,
    PART,
    FITMENT_NOTES_L EGACY
    WHERE
    BaseVehicle.Bas eVehicleID = APPLICATION_ACE S.BASE_VEHICLE_ ID
    AND
    Make.MakeID = BaseVehicle.Mak eId
    AND
    Model.ModelID = BaseVehicle.Mod elId
    AND
    Vehicle.Vehicle ID = APPLICATION_ACE S.VEHICLE_ID
    AND
    Vehicle.BaseVeh icleID = BaseVehicle.Bas eVehicleID
    AND
    Vehicle.SubMode lId = APPLICATION_ACE S.SUBMODEL_ID
    AND
    SubModel.SubMod elId = Vehicle.SubMode lId
    AND
    DriveType.Drive TypeID = APPLICATION_ACE S.DRIVE_TYPE_ID
    AND
    VehicleToBodySt yleConfig.BodyS tyleConfigID =
    APPLICATION_ACE S.BODY_STYLE_CO NFIG_ID
    AND
    VehicleToBodySt yleConfig.Vehic leID = APPLICATION_ACE S.VEHICLE_ID
    AND
    BodyStyleConfig .BodyStyleConfi gID =
    VehicleToBodySt yleConfig.BodyS tyleConfigID
    AND
    BodyNumDoors.Bo dyNumDoorsID = BodyStyleConfig .BodyNumDoorsID
    AND
    BodyType.BodyTy peID = BodyStyleConfig .BodyTypeID
    AND
    VehicleToTransm ission.VehicleI D = APPLICATION_ACE S.VEHICLE_ID
    AND
    VehicleToTransm ission.Transmis sionID =
    APPLICATION_ACE S.TRANSMISSION_ ID
    AND
    Transmission.Tr ansmissionID = VehicleToTransm ission.Transmis sionID
    AND
    TransmissionBas e.TransmissionB aseID =
    Transmission.Tr ansmissionBaseI D
    AND
    TransmissionTyp e.TransmissionT ypeID =
    TransmissionBas e.TransmissionT ypeID
    AND
    TransmissionNum Speeds.Transmis sionNumSpeedsID =
    TransmissionBas e.TransmissionN umSpeedsID
    AND
    TransmissionCon trolType.Transm issionControlTy peID =
    TransmissionBas e.TransmissionC ontrolTypeID
    AND
    VehicleToEngine Config.VehicleI D = APPLICATION_ACE S.VEHICLE_ID
    AND
    VehicleToEngine Config.EngineCo nfigID =
    APPLICATION_ACE S.ENGINE_CONFIG _ID
    AND
    EngineConfig.En gineConfigID = VehicleToEngine Config.EngineCo nfigID
    AND
    EngineBase.Engi neBaseID = EngineConfig.En gineBaseID
    AND
    EngineDesignati on.EngineDesign ationID =
    EngineConfig.En gineDesignation ID
    AND
    Aspiration.Aspi rationID = EngineConfig.As pirationID
    AND
    FuelType.FuelTy peID = EngineConfig.Fu elTypeID
    AND
    VehicleToBrakeC onfig.VehicleID = APPLICATION_ACE S.VEHICLE_ID
    AND
    VehicleToBrakeC onfig.BrakeConf igID =
    APPLICATION_ACE S.BRAKE_CONFIG_ ID
    AND
    BrakeConfig.Bra keConfigID = VehicleToBrakeC onfig.BrakeConf igID
    AND
    BrakeType1.Brak eTypeID = BrakeConfig.Fro ntBrakeTypeID
    AND
    BrakeType2.Brak eTypeID = BrakeConfig.Rea rBrakeTypeID
    AND
    BrakeSystem.Bra keSystemID = BrakeConfig.Bra keSystemID
    AND
    BrakeABS.BrakeA BSID = BrakeConfig.Bra keABSID
    AND
    PART.PART_ID = APPLICATION_ACE S.PART_ID
    AND
    FITMENT_NOTES_L EGACY.FITMENT_N OTE_ID =
    APPLICATION_ACE S.FITMENT_NOTE_ ID
    ;

    Tags: None
    • Harel
      #2
      Re: Access crash caused by large sql

      Another question: is there a way to turn of the questions "you are
      about to insert xx records", and the other one "the table will be
      suppressed...". Because of the time the query needs to complete, I'm
      in bed...Thanks.

        Comment

        • Bob Quintal
          #3
          Re: Access crash caused by large sql

          Harel <guyharel13@gma il.comwrote in news:b1752fc5-3616-4479-b62e-
          4c592863d19a@d4 5g2000hsc.googl egroups.com:
          Another question: is there a way to turn of the questions "you are
          about to insert xx records", and the other one "the table will be
          suppressed...". Because of the time the query needs to complete, I'm
          in bed...Thanks.
          in your previous message you said that the SQL was for a report.
          Reports in Access do not use MakeTable queries, so you should not get
          the above messages.

          If you are actualy making a new table to use in the report, using
          DoCmd.openQuery , prefix the statement with DoCmd.setwarnin gs false, and
          follow it with Docmd.setwarnin gs true.

          --
          Bob Quintal

          PA is y I've altered my email address.
          ** Posted from http://www.teranews.com **

            Comment

            • Bob Quintal
              #4
              Re: Access crash caused by large sql

              You need to split the SQL into several smaller queries, the first
              being an insert query, then update the related fields using update
              queries.

              Also, your code is using where clauses to enforce the relationshps.
              That way is obsolete, use Joins instead, as they are more efficient.

              Q

              Harel <guyharel13@gma il.comwrote in
              news:85c81575-13b2-4a99-9a0f-
              7f21f287be2f@j2 2g2000hsf.googl egroups.co
              m:
              I have a report SQL which have been working for a few weeks. Its
              large and the target database is large. It has 59 join, and I dont
              think there is anything I can do about this, since the DB I use
              has been normalized by pros.
              >
              My problem is I try to had another join to display another value,
              then I get a crash report from MS-Access. Anything I can to do
              diagnose this problem. My development depends on this SQL:
              >
              SELECT
              BASE_VEHICLE_ID ,
              VEHICLE_ID,
              Trim(Make.MakeN ame) as Make,
              Trim(Model.Mode lName) as Model,
              YearID,
              Trim(SubModel.S ubModelName) as Sub,
              Trim(DriveType. DriveTypeName) as DriveType,
              BodyNumDoors.Bo dyNumDoors + "DR" as Doors,
              Trim(BodyType.B odyTypeName) as Body,
              Trim(Transmissi onType.Transmis sionTypeName) as Trans,
              Trim(Transmissi onNumSpeeds.Tra nsmissionNumSpe eds) as
              TransSpeeds,
              Trim(Transmissi onControlType.T ransmissionCont rolTypeName) as
              TransControl,
              EngineBase.Lite r + "L" as EngLiter,
              EngineBase.CC as EngCC,
              EngineBase.Cyli nders + " CYL" as EngCyl,
              Trim(EngineDesi gnation.EngineD esignationName) as EngineName,
              Trim(Aspiration .AspirationName ) as Aspiration,
              Trim(FuelType.F uelTypeName) as Fuel,
              BrakeType1.Brak eTypeName as FrontBrake,
              BrakeType2.Brak eTypeName as RearBrake,
              BrakeSystem.Bra keSystemName as BrakeName,
              BrakeABS.BrakeA BSName as ABS,
              PART_NUMBER,
              FITMENT_NOTE_DE SC
              INTO
              RAPPORT_MAPPING
              FROM
              APPLICATION_ACE S,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BaseVehicle,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Vehicle,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Make,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Model,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].SubModel,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].DriveType,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97
              20080731.mdb].VehicleToBodyS tyleConfig,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyStyleConfi g,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyNumDoors,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyType,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97
              20080731.mdb].VehicleToTrans mission,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Transmission,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionBa se,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionTy pe,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97
              20080731.mdb].TransmissionNu mSpeeds,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97
              20080731.mdb].TransmissionCo ntrolType,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97
              20080731.mdb].VehicleToEngin eConfig,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineConfig,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineBase,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineDesignat ion,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Aspiration,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].FuelType,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97
              20080731.mdb].VehicleToBrake Config,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeConfig,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeType as
              BrakeType1,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeType as
              BrakeType2,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeSystem,
              [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco
              AAIA DB
              \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeABS,
              PART,
              FITMENT_NOTES_L EGACY
              WHERE
              BaseVehicle.Bas eVehicleID = APPLICATION_ACE S.BASE_VEHICLE_ ID
              AND
              Make.MakeID = BaseVehicle.Mak eId
              AND
              Model.ModelID = BaseVehicle.Mod elId
              AND
              Vehicle.Vehicle ID = APPLICATION_ACE S.VEHICLE_ID
              AND
              Vehicle.BaseVeh icleID = BaseVehicle.Bas eVehicleID
              AND
              Vehicle.SubMode lId = APPLICATION_ACE S.SUBMODEL_ID
              AND
              SubModel.SubMod elId = Vehicle.SubMode lId
              AND
              DriveType.Drive TypeID = APPLICATION_ACE S.DRIVE_TYPE_ID
              AND
              VehicleToBodySt yleConfig.BodyS tyleConfigID =
              APPLICATION_ACE S.BODY_STYLE_CO NFIG_ID
              AND
              VehicleToBodySt yleConfig.Vehic leID = APPLICATION_ACE S.VEHICLE_ID
              AND
              BodyStyleConfig .BodyStyleConfi gID =
              VehicleToBodySt yleConfig.BodyS tyleConfigID
              AND
              BodyNumDoors.Bo dyNumDoorsID = BodyStyleConfig .BodyNumDoorsID
              AND
              BodyType.BodyTy peID = BodyStyleConfig .BodyTypeID
              AND
              VehicleToTransm ission.VehicleI D = APPLICATION_ACE S.VEHICLE_ID
              AND
              VehicleToTransm ission.Transmis sionID =
              APPLICATION_ACE S.TRANSMISSION_ ID
              AND
              Transmission.Tr ansmissionID =
              VehicleToTransm ission.Transmis sionID
              AND
              TransmissionBas e.TransmissionB aseID =
              Transmission.Tr ansmissionBaseI D
              AND
              TransmissionTyp e.TransmissionT ypeID =
              TransmissionBas e.TransmissionT ypeID
              AND
              TransmissionNum Speeds.Transmis sionNumSpeedsID =
              TransmissionBas e.TransmissionN umSpeedsID
              AND
              TransmissionCon trolType.Transm issionControlTy peID =
              TransmissionBas e.TransmissionC ontrolTypeID
              AND
              VehicleToEngine Config.VehicleI D = APPLICATION_ACE S.VEHICLE_ID
              AND
              VehicleToEngine Config.EngineCo nfigID =
              APPLICATION_ACE S.ENGINE_CONFIG _ID
              AND
              EngineConfig.En gineConfigID =
              VehicleToEngine Config.EngineCo nfigID
              AND
              EngineBase.Engi neBaseID = EngineConfig.En gineBaseID
              AND
              EngineDesignati on.EngineDesign ationID =
              EngineConfig.En gineDesignation ID
              AND
              Aspiration.Aspi rationID = EngineConfig.As pirationID
              AND
              FuelType.FuelTy peID = EngineConfig.Fu elTypeID
              AND
              VehicleToBrakeC onfig.VehicleID = APPLICATION_ACE S.VEHICLE_ID
              AND
              VehicleToBrakeC onfig.BrakeConf igID =
              APPLICATION_ACE S.BRAKE_CONFIG_ ID
              AND
              BrakeConfig.Bra keConfigID = VehicleToBrakeC onfig.BrakeConf igID
              AND
              BrakeType1.Brak eTypeID = BrakeConfig.Fro ntBrakeTypeID
              AND
              BrakeType2.Brak eTypeID = BrakeConfig.Rea rBrakeTypeID
              AND
              BrakeSystem.Bra keSystemID = BrakeConfig.Bra keSystemID
              AND
              BrakeABS.BrakeA BSID = BrakeConfig.Bra keABSID
              AND
              PART.PART_ID = APPLICATION_ACE S.PART_ID
              AND
              FITMENT_NOTES_L EGACY.FITMENT_N OTE_ID =
              APPLICATION_ACE S.FITMENT_NOTE_ ID
              ;
              >


              --
              Bob Quintal

              PA is y I've altered my email address.
              ** Posted from http://www.teranews.com **

                Comment

                • lyle fairfield
                  #5
                  Re: Access crash caused by large sql

                  Have you thought about a career as a comedy writer?

                  On Aug 24, 9:24 am, Harel <guyhare...@gma il.comwrote:
                  I have a report SQL which have been working for a few weeks. Its large
                  and the target database is large. It has 59 join, and I dont think
                  there is anything I can do about this, since the DB I use has been
                  normalized by pros.
                  >
                  My problem is I try to had another join to display another value, then
                  I get a crash report from MS-Access. Anything I can to do diagnose
                  this problem. My development depends on this SQL:
                  >
                  SELECT
                    BASE_VEHICLE_ID ,
                    VEHICLE_ID,
                    Trim(Make.MakeN ame) as Make,
                    Trim(Model.Mode lName) as Model,
                    YearID,
                    Trim(SubModel.S ubModelName) as Sub,
                    Trim(DriveType. DriveTypeName) as DriveType,
                    BodyNumDoors.Bo dyNumDoors + "DR" as Doors,
                    Trim(BodyType.B odyTypeName) as Body,
                    Trim(Transmissi onType.Transmis sionTypeName) as Trans,
                    Trim(Transmissi onNumSpeeds.Tra nsmissionNumSpe eds) as TransSpeeds,
                    Trim(Transmissi onControlType.T ransmissionCont rolTypeName) as
                  TransControl,
                    EngineBase.Lite r + "L" as EngLiter,
                    EngineBase.CC as EngCC,
                    EngineBase.Cyli nders + " CYL" as EngCyl,
                    Trim(EngineDesi gnation.EngineD esignationName) as EngineName,
                    Trim(Aspiration .AspirationName ) as Aspiration,
                    Trim(FuelType.F uelTypeName) as Fuel,
                    BrakeType1.Brak eTypeName as FrontBrake,
                    BrakeType2.Brak eTypeName as RearBrake,
                    BrakeSystem.Bra keSystemName as BrakeName,
                    BrakeABS.BrakeA BSName as ABS,
                    PART_NUMBER,
                    FITMENT_NOTE_DE SC
                  INTO
                    RAPPORT_MAPPING
                  FROM
                    APPLICATION_ACE S,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BaseVehicle,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Vehicle,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Make,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Model,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].SubModel,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].DriveType,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97
                  20080731.mdb].VehicleToBodyS tyleConfig,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyStyleConfi g,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyNumDoors,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyType,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].VehicleToTrans mission,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Transmission,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionBa se,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionTy pe,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionNu mSpeeds,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97
                  20080731.mdb].TransmissionCo ntrolType,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].VehicleToEngin eConfig,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineConfig,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineBase,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineDesignat ion,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Aspiration,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].FuelType,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].VehicleToBrake Config,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeConfig,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeType as
                  BrakeType1,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeType as
                  BrakeType2,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeSystem,
                    [MS Access;DATABASE =D:\Dossiers courants\Projet s\Absco\Absco AAIA DB
                  \ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeABS,
                    PART,
                    FITMENT_NOTES_L EGACY
                  WHERE
                    BaseVehicle.Bas eVehicleID = APPLICATION_ACE S.BASE_VEHICLE_ ID
                  AND
                    Make.MakeID = BaseVehicle.Mak eId
                  AND
                    Model.ModelID = BaseVehicle.Mod elId
                  AND
                    Vehicle.Vehicle ID = APPLICATION_ACE S.VEHICLE_ID
                  AND
                    Vehicle.BaseVeh icleID = BaseVehicle.Bas eVehicleID
                  AND
                    Vehicle.SubMode lId = APPLICATION_ACE S.SUBMODEL_ID
                  AND
                    SubModel.SubMod elId = Vehicle.SubMode lId
                  AND
                    DriveType.Drive TypeID = APPLICATION_ACE S.DRIVE_TYPE_ID
                  AND
                    VehicleToBodySt yleConfig.BodyS tyleConfigID =
                  APPLICATION_ACE S.BODY_STYLE_CO NFIG_ID
                  AND
                    VehicleToBodySt yleConfig.Vehic leID = APPLICATION_ACE S.VEHICLE_ID
                  AND
                    BodyStyleConfig .BodyStyleConfi gID =
                  VehicleToBodySt yleConfig.BodyS tyleConfigID
                  AND
                    BodyNumDoors.Bo dyNumDoorsID = BodyStyleConfig .BodyNumDoorsID
                  AND
                    BodyType.BodyTy peID = BodyStyleConfig .BodyTypeID
                  AND
                    VehicleToTransm ission.VehicleI D = APPLICATION_ACE S.VEHICLE_ID
                  AND
                    VehicleToTransm ission.Transmis sionID =
                  APPLICATION_ACE S.TRANSMISSION_ ID
                  AND
                    Transmission.Tr ansmissionID = VehicleToTransm ission.Transmis sionID
                  AND
                    TransmissionBas e.TransmissionB aseID =
                  Transmission.Tr ansmissionBaseI D
                  AND
                    TransmissionTyp e.TransmissionT ypeID =
                  TransmissionBas e.TransmissionT ypeID
                  AND
                    TransmissionNum Speeds.Transmis sionNumSpeedsID =
                  TransmissionBas e.TransmissionN umSpeedsID
                  AND
                    TransmissionCon trolType.Transm issionControlTy peID =
                  TransmissionBas e.TransmissionC ontrolTypeID
                  AND
                    VehicleToEngine Config.VehicleI D = APPLICATION_ACE S.VEHICLE_ID
                  AND
                    VehicleToEngine Config.EngineCo nfigID =
                  APPLICATION_ACE S.ENGINE_CONFIG _ID
                  AND
                    EngineConfig.En gineConfigID = VehicleToEngine Config.EngineCo nfigID
                  AND
                    EngineBase.Engi neBaseID = EngineConfig.En gineBaseID
                  AND
                    EngineDesignati on.EngineDesign ationID =
                  EngineConfig.En gineDesignation ID
                  AND
                    Aspiration.Aspi rationID = EngineConfig.As pirationID
                  AND
                    FuelType.FuelTy peID = EngineConfig.Fu elTypeID
                  AND
                    VehicleToBrakeC onfig.VehicleID = APPLICATION_ACE S.VEHICLE_ID
                  AND
                    VehicleToBrakeC onfig.BrakeConf igID =
                  APPLICATION_ACE S.BRAKE_CONFIG_ ID
                  AND
                    BrakeConfig.Bra keConfigID = VehicleToBrakeC onfig.BrakeConf igID
                  AND
                    BrakeType1.Brak eTypeID = BrakeConfig.Fro ntBrakeTypeID
                  AND
                    BrakeType2.Brak eTypeID = BrakeConfig.Rea rBrakeTypeID
                  AND
                    BrakeSystem.Bra keSystemID = BrakeConfig.Bra keSystemID
                  AND
                    BrakeABS.BrakeA BSID = BrakeConfig.Bra keABSID
                  AND
                    PART.PART_ID = APPLICATION_ACE S.PART_ID
                  AND
                    FITMENT_NOTES_L EGACY.FITMENT_N OTE_ID =
                  APPLICATION_ACE S.FITMENT_NOTE_ ID
                  ;

                    Comment

                    • Harel
                      #6
                      Re: Access crash caused by large sql

                      On 24 août, 12:58, lyle fairfield <lyle.fairfi... @gmail.comwrote :
                      Have you thought about a career as a comedy writer?
                      What is it you dont like about the post?

                        Comment

                        • lyle fairfield
                          #7
                          Re: Access crash caused by large sql

                          On Aug 24, 2:15 pm, Harel <guyhare...@gma il.comwrote:
                          On 24 août, 12:58, lyle fairfield <lyle.fairfi... @gmail.comwrote :
                          >
                          Have you thought about a career as a comedy writer?
                          >
                          What is it you dont like about the post?
                          I love the post. I also love Lucy, Seinfeld, Jackie, Red and Carole.

                            Comment

                            • Harel
                              #8
                              Re: Access crash caused by large sql

                              I love the post. I also love Lucy, Seinfeld, Jackie, Red and Carole.

                              Ok then. I prefer "tragedy" anyway...

                                Comment

                                • Bob Quintal
                                  #9
                                  Re: Access crash caused by large sql

                                  Harel <guyharel13@gma il.comwrote in news:141cc46b-faa8-404f-8c74-
                                  158165fd9ded@k3 7g2000hsf.googl egroups.com:
                                  >I love the post. I also love Lucy, Seinfeld, Jackie, Red and Carole.
                                  >
                                  Ok then. I prefer "tragedy" anyway...
                                  >
                                  The tragedy is in the way you wrote the SQL. :-)

                                  --
                                  Bob Quintal

                                  PA is y I've altered my email address.
                                  ** Posted from http://www.teranews.com **

                                    Comment

                                    • David W. Fenton
                                      #10
                                      Re: Access crash caused by large sql

                                      Bob Quintal <rquintal@sPAmp atico.cawrote in
                                      news:Xns9B0465C 6057F1BQuintal@ 66.175.223.2:
                                      Also, your code is using where clauses to enforce the
                                      relationshps. That way is obsolete, use Joins instead, as they are
                                      more efficient.
                                      That depends on the circumstances. Most WHERE clauses are optimized
                                      by the Jet query optimizer exactly the same way as a join.

                                      But not all of them.

                                      --
                                      David W. Fenton http://www.dfenton.com/
                                      usenet at dfenton dot com http://www.dfenton.com/DFA/

                                        Comment

                                        • David W. Fenton
                                          #11
                                          Re: Access crash caused by large sql

                                          I don't understand why you're specifying the path for all but three
                                          tables, when you really need to do it for the three. Seems to me it
                                          would be much simpler to do this:

                                          FROM [connect string for this db].APPLICATION_AC ES, [connect string
                                          for this db].PART, [connect string for this
                                          db].FITMENT_NOTES_ LEGACY, BaseVehicle, Vehicle, Make, Model,
                                          SubModel, DriveType, VehicleToBodySt yleConfig, BodyStyleConfig ,
                                          BodyNumDoors, BodyType, VehicleToTransm ission, Transmission,
                                          TransmissionBas e, TransmissionTyp e, TransmissionNum Speeds,
                                          TransmissionCon trolType, VehicleToEngine Config, EngineConfig,
                                          EngineBase, EngineDesignati on, Aspiration, FuelType,
                                          VehicleToBrakeC onfig, BrakeConfig, BrakeType as BrakeType1,
                                          BrakeType as BrakeType2, BrakeSystem, BrakeABS IN 'D:\Dossiers
                                          courants\Projet s\Absco\Absco AAIA DB\ACES\Vcdb\AA IA VCdb2006
                                          Access97 20080731.mdb'

                                          Since all your tables but 3 of them are in the same MDB, it makes
                                          more sense to specify the connect string for those THREE and use IN
                                          to specify the MDB for all the other tables.

                                          From there, you might be able to resolve your issue and use joins in
                                          place of a WHERE clause.

                                          --
                                          David W. Fenton http://www.dfenton.com/
                                          usenet at dfenton dot com http://www.dfenton.com/DFA/

                                            Comment

                                            • rumkus@hotmail.com
                                              #12
                                              Re: Access crash caused by large sql

                                              On database window
                                              Click "Queries" then click " Create query in Design view "
                                              And start from the beginning.
                                              Create your first query then save it.
                                              Click again " Create query in Design view "
                                              Create your second - if necessary onto the first one.
                                              Basing or joining other queries your previously created should be
                                              pencilled in your case.
                                              How many queries ? Well, looking at your sql I'd say more than one
                                              surely.
                                              I mean if the word of "diagnose " is creating some difficulty.

                                              kind rgds

                                                Comment

                                                Previous template Next
                                                Working...