Access crash caused by large sql - Microsoft Access / VBA

Harel

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
;

Aug 24 '08 #1

Subscribe Reply

2384

Harel

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.

Aug 24 '08 #2

Bob Quintal

Harel <gu********@gma il.comwrote in news:b1752fc5-3616-4479-b62e-
4c**********@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 **

Aug 24 '08 #3

Bob Quintal

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 <gu********@gma il.comwrote in
news:85c81575-13b2-4a99-9a0f-
7f**********@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 **

Aug 24 '08 #4

lyle fairfield

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
;

Aug 24 '08 #5

Harel

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?

Aug 24 '08 #6

lyle fairfield

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.

Aug 24 '08 #7

Harel

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

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

Aug 24 '08 #8

Bob Quintal

Harel <gu********@gma il.comwrote in news:141cc46b-faa8-404f-8c74-
15**********@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 **

Aug 24 '08 #9

David W. Fenton

Bob Quintal <rq******@sPAmp atico.cawrote in
news:Xn******** **************@ 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/

Aug 24 '08 #10

Similar topics

3424

Can't access class members from function

by: Stuart P | last post by:

Hello, I am making my first real game in C++ and there is a problem with the class used to contain a level. The level::load(filename) function gives an Access Violation error when trying to access the class's member variables. Here is the class definition in level.h: class level { BYTE w,h; WORD *boarddata;

C / C++

4732

Error Trapping In Access 2000

by: Peter Frost | last post by:

Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much appreciated. Thanks in advance

Microsoft Access / VBA

1675

netware popup causing access to crash?

by: Jamma | last post by:

Hello, I have an Access 2000 file working on XP machines and a NT network. The files are split and 95% of the time all is well and stable. The main issue arises when the app prints,a novell netware pop up shows and declares which printer the file is going to and when printing completes, Access crashes. This is really starting to tick off the client users. I see that there may be two possible solutions - first, I have some way of...

Microsoft Access / VBA

2250

memory issues in ms access

by: russ | last post by:

Hi I need some advice about ms-access limitations. i have some databases written by others i have been working with that keeps crashing. different on different computers, so it sounds like a memory thing. i have some tables which are deveral hundred thousand records long and a few tens of records wide. will this cause a problem? is there a total max no records or database/file size? any links to websites discussing this would be...

Microsoft Access / VBA

3199

Access Scalability - records vs. filesize

by: deko | last post by:

Do I get more scalability if I split my database? The way I calculate things now, I'll be lucky to get 100,000 records in my Access 2003 mdb. Here some math: Max mdb/mde size = 2000 x 1024 = 2,048,000k Let's say on average each record in the database consumes 15k 2,048,000/15 = 136,533 records

Microsoft Access / VBA

9733

VB.Net file access problem with UNC

by: Sarah | last post by:

I need to access some data on a server. I can access it directly using UNC (i.e. \\ComputerName\ShareName\Path\FileName) or using a mapped network drive resource (S:\Path\FileName). Here is my problem: my vb.net program has problems with UNC. If the UNC server is restarted or goes off-line, my VB.net program crashes. The code for UNC access to the file is included below and is put in the tick event of a form timer control running every...

Visual Basic .NET

3170

slow / server crash when adding a column to a big table

by: bennett | last post by:

MySQL Database

4401

Ms Access 2003

by: Mell via AccessMonster.com | last post by:

Is there a way to find out where an application was created from? i.e. - work or home i.e. - if application sits on a (work) server/network, the IT people know the application is sitting there, but is there a way they can find out if that application was put there from a CD or email or created at work? Hint: It's not on a client/server database, just native jet database mdb created on Access 2003 (default 2000)...

Microsoft Access / VBA

5562

Access Violation Errors - Can Memory Leaks cause these?

by: nmehring | last post by:

I have an MFC app with 2000 users. I have one user that experiences a crash in our software anywhere from 1 to 5 times a week when opening a particular module. No other users have reported this particular crash so I don't think anyone else is experiencing it but I know other users are doing exactly what she is doing because it is our most popular module. I have analyzed the dmp files from several of this user's crashes using windbg...

C / C++

7899

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

8397

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8264

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

5850

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5438

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

3897

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

2412

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1504

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1250

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General