大sql导致访问崩溃 [英] Access crash caused by large sql
问题描述
我有一份报告SQL已经工作了几周。它的大b / b $ b和目标数据库很大。它有59个加入，我不认为
我有什么可以做的，因为我使用的数据库已经由专业人士正常化了。
br />
我的问题是我尝试让另一个连接显示另一个值，然后
我从MS-Access收到崩溃报告。我可以做任何事情来诊断
这个问题。我的开发取决于这个SQL：
SELECT
BASE_VEHICLE_ID，
VEHICLE_ID，
修剪（ Make.MakeName）为Make，
修剪（Model.ModelName）为模型，
YearID，
修剪（SubModel.SubModelName）为Sub ，
修剪（DriveType.DriveTypeName）为DriveType，
BodyNumDoors.BodyNumDoors +" DR"作为门，
修剪（BodyType.BodyTypeName）为Body，
修剪（TransmissionType.TransmissionTypeName）为Trans，
修剪（TransmissionNumSpeeds.TransmissionNumSpeeds ）作为TransSpeeds，
修剪（TransmissionControlType.TransmissionControlTy peName）为
TransControl，
EngineBase.Liter +" L"作为EngLiter，
EngineBase.CC作为EngCC，
EngineBase.Cylinders +" CYL"作为EngCyl，
修剪（EngineDesignation.EngineDesignationName）作为EngineName，
修剪（Aspiration.AspirationName）as Aspiration，
修剪（FuelType.FuelTypeName ）作为燃料，
BrakeType1.BrakeTypeName作为FrontBrake，
BrakeType2.BrakeTypeName作为RearBrake，
BrakeSystem.BrakeSystemName作为BrakeName，
BrakeABS.BrakeABSName为ABS，
PART_NUMBER，
FITMENT_NOTE_DESC
INTO
RAPPORT_MAPPING
来自
APPLICATION_ACES，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES \Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .BaseVehicle，
[MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco \\ \\ Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .Vehicle，
[MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco \ Absco AAIA DB
\ACES \Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .Make，
[MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco \ Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .Model，
[MS Access; DATABASE = D：\ Dossiers courants \\ \\ Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .SubModel，
[MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco \ Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .DriveType，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb] .VehicleToBodyStyleConfig，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES \ _Vcdb\AAIA VCdb2006 Access97 2008 0731.mdb] .BodyStyleConfig，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco AAIA DB
\ ACES \\ \\ _Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .BodyNumDoors，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES \Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .BodyType，
[MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco\ Absco AAIA DB
\ACES \Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .VehicleToTransmission，
[MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .Transmission，
[MS Access; DATABASE = D ：\ Dossiers courants \ Projets\Absco \ Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .TransmissionBase，
[MS Access; DA TABASE = D：\ Dossiers courants\Projets\Absco \ Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .TransmissionType，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb]。 TransmissionNumSpeeds，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb] .TransmissionControlType，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES \Vcdb \ AAIA VCdb2006 Access97 20080731.mdb] .VehicleToEngineConfig，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco \ Absco AAIA DB
\ACES \Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .EngineConfig，
[MS Access; DATABASE = D：\ D ossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .EngineBase，
[MS Access ; DATABASE = D：\ Dossiers courants \ Projets\Absco \ Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .EngineDesignation，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .Aspiration，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\ AAIA VCdb2006 Access97 20080731.mdb] .FuelType，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco \ Absco AAIA DB
\\ \ nACES\Vcdb \ AAIA VCdb2006 Access97 20080731.mdb] .VehicleToBrakeConfig，
[MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco一个数据库
\ACES \Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .BrakeConfig，
[MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .BrakeType as
BrakeType1，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb]。 BrakeType为
BrakeType2，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .BrakeSystem，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco \ Absco AAIA DB
\ACES \Vcdb \ AAIA VCdb2006 Access97 20080731.mdb] .BrakeABS，
PART，
FITMENT_NOTES_LEGACY
WHERE
BaseVehicle.BaseVehicleID = APPLICATION_ ACES.BASE_VEHICLE_ID
AND
Make.MakeID = BaseVehicle.MakeId
AND
Model.ModelID = BaseVehicle。 ModelId
AND
Vehicle.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
Vehicle.BaseVehicleID = BaseVehicle.BaseVehicleID
AND
Vehicle.SubModelId = APPLICATION_ACES.SUBMODEL_ID
AND
SubModel.SubModelId = Vehicle.SubModelId
AND
DriveType.DriveTypeID = APPLICATION_ACES.DRIVE_TYPE_ID
AND
VehicleToBodyStyleConfig.BodyStyleConfigID =
APPLICATION_ACES.BODY_STYLE_CONFIG_ID
AND
VehicleToBodyStyleConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
BodyStyleConfig.BodyStyleConfigID =
VehicleToBodyStyleConfig.BodyStyleConfigID
AND
BodyNumDoors.BodyNumDoorsID = BodyStyleConfig.BodyNumDoorsID
AND
BodyType.BodyTypeID = BodyStyleConfig.BodyTy peid
AND
VehicleToTransmission.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToTransmission.TransmissionID =
APPLICATION_ACES.TRANSMISSION_ID
AND
Transmission.TransmissionID = VehicleToTransmission.TransmissionID
AND
TransmissionBase。 TransmissionBaseID =
Transmission.TransmissionBaseID
AND
TransmissionType.TransmissionTypeID =
TransmissionBase.TransmissionTypeID
AND
TransmissionNumSpeeds.TransmissionNumSpeedsID =
TransmissionBase.TransmissionNumSpeedsID
AND
TransmissionControlType.TransmissionControlTypeID =
TransmissionBase.TransmissionControlTypeID
AND
VehicleToEngineConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToEngineConfig.EngineConfigID =
APPLICATION_ACES.ENGINE_CONFIG_ID
AND
Engin eConfig.EngineConfigID = VehicleToEngineConfig.EngineConfigID
AND
EngineBase.EngineBaseID = EngineConfig.EngineBaseID
AND
EngineDesignation。 EngineDesignationID =
EngineConfig.EngineDesignationID
AND
Aspiration.AspirationID = EngineConfig.AspirationID
AND
FuelType.FuelTypeID = EngineConfig.FuelTypeID
AND
VehicleToBrakeConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToBrakeConfig.BrakeConfigID =
APPLICATION_ACES.BRAKE_CONFIG_ID
AND
BrakeConfig.BrakeConfigID = VehicleToBrakeConfig.BrakeConfigID
AND
BrakeType1.BrakeTypeID = BrakeConfig.FrontBrakeTypeID
AND
BrakeType2.BrakeTypeID = BrakeConfig.RearBrakeTypeID
AND
BrakeSystem.BrakeSystemID = BrakeConfig.BrakeSystemID
AND
BrakeABS.BrakeABSID = BrakeConfig.BrakeABSID
AND
PART.PART_ID = APPLICATION_ACES.PART_ID
AND
FITMENT_NOTES_LEGACY.FITMENT_NOTE_ID =
APPLICATION_ACES。 FITMENT_NOTE_ID
;
另一个问题：是否有办法解决问题你
即将插入xx记录，而另一个表格将被
压制......。由于查询需要完成的时间，我在床上是
...谢谢。
Harel< gu ** ****** @gmail.com在新闻中写道：b1752fc5-3616-4479-b62e-
4c ********** @ d45g2000hsc.googlegroups.com ：
另一个问题：是否有办法转向问题你是
即将插入xx记录，而另一个表格将是
被压制......。由于查询需要完成的时间，我在床上是
...谢谢。
在你之前的消息中你说SQL是用于报告的。
Access中的报告不使用MakeTable查询，所以你应该上面的消息没有获得
.
如果您正在制作一个新的表格在报告中使用，请使用
DoCmd .openQuery，前缀为DoCmd.setwarnings为false的语句，
跟随Docmd.setwarnings为真。
-
Bob Quintal
PA是我改变了我的电子邮件地址。
**发自 http://www.teranews.com **
你需要拆分将SQL转换成几个较小的查询，第一个
是一个插入查询，然后使用更新
查询更新相关字段。
此外，您的代码使用where子句来强制执行关系。
这种方式已过时，请使用连接，因为它们是更高效。
Q
Harel< gu ******** @ gmail.comwrote in
news：85c81575-13b2-4a99-9a0f-
7f ****** ****@j22g2000hsf.googlegroups.co
m：
我有一个报告SQL，它一直在为一个几个星期。它的b $ b大，目标数据库很大。它有59个加入，而且我不认为我有什么可以做的，因为我使用的数据库
已被专业人员规范化了。
我的问题是我尝试让另一个连接显示另一个值，
然后我从MS-Access收到崩溃报告。我能做的任何事情
诊断出这个问题。我的开发取决于这个SQL：
SELECT
BASE_VEHICLE_ID，
VEHICLE_ID，
修剪（ Make.MakeName）为Make，
修剪（Model.ModelName）为模型，
YearID，
修剪（SubModel.SubModelName）为Sub ，
修剪（DriveType.DriveTypeName）为DriveType，
BodyNumDoors.BodyNumDoors +" DR"作为门，
修剪（BodyType.BodyTypeName）为Body，
修剪（TransmissionType.TransmissionTypeName）为Trans，
修剪（TransmissionNumSpeeds.TransmissionNumSpeeds ）作为
TransSpeeds，
修剪（TransmissionControlType.TransmissionControlTy peName）为
TransControl，
EngineBase.Liter + >> 1"作为EngLiter，
EngineBase.CC作为EngCC，
EngineBase.Cylinders +" CYL"作为EngCyl，
修剪（EngineDesignation.EngineDesignationName）作为EngineName，
修剪（Aspiration.AspirationName）as Aspiration，
修剪（FuelType.FuelTypeName ）作为燃料，
BrakeType1.BrakeTypeName作为FrontBrake，
BrakeType2.BrakeTypeName作为RearBrake，
BrakeSystem.BrakeSystemName作为BrakeName，
BrakeABS.BrakeABSName为ABS，
PART_NUMBER，
FITMENT_NOTE_DESC
INTO
RAPPORT_MAPPING
FROM
APPLICATION_ACES，
[MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco\Absco
AAIA DB
\ACES \Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .BaseVehicle，
[MS Access; DATABASE = D：\ Dossiers counts\Projets\Absco \ Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .Vehicle，
[MS Access; DATABASE = D：\ Dossiers cou rants \ Projets\Absco \ Absco
AAIA DB
\ACES \Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .Make，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\\ \\ AAIA VCdb2006 Access97 20080731.mdb] .Model，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .SubModel，
[MS Access; DATABASE = D：\ Dossiers courants \ Projets \\ \\ Absco \ Absco
AAIA DB
\ACES \Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .DriveType，
[ MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco \ Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb] .VehicleToBodyStyleConfig，
[MS Access; DATABASE = D： \\ Dossiers courants \ Projets \ Absco \ Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .BodyStyleConfig，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES \ Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .BodyNumDoors，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .BodyType，
[MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb] .VehicleToTransmission ，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco
AAIA DB
\ ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb]。传输，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES \ Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .TransmissionBase，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .TransmissionType，
[MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb] .TransmissionNumSpeeds ，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco
AAIA DB
\ ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb] .TransmissionControlType，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco \ Absco
AAIA DB
\ ACES \\ Vcdb\AAIA VCdb2006 Access97
20080731.mdb] .VehicleToEngineConfig，
[MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco \ Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .EngineConfig，
[MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco \ Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .EngineBase ，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco
AAIA DB
\ ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .EngineDesignation，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES \Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .Aspiration，
[MS Access; DATABASE = D：\ Dossiers counts\Projets ;\\Absco \ Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .FuelType，
[MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb] .VehicleToBrakeConfig ，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco
AAIA DB
\ ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .BrakeConfig，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES \Vcdb\AAIA VCdb2006 Access97 20080731.mdb] .BrakeType as
BrakeType1，
[ MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco \ Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731 .mdb] .BrakeType为
BrakeType2，
[MS Access; DATABASE = D：\ Dossiers courants \ Projets\Absco \ Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb]。 BrakeSystem，
[MS Access; DATABASE = D：\ Dossiers courants\Projets\Absco\Absco
AAIA DB
\\ \ nACES\Vcdb \ AAIA VCdb2006 Access97 20080731.mdb] .BrakeABS，
PART，
FITMENT_NOTES_LEGACY
WHERE
BaseVehicle.BaseVehicleID = APPLICATION_ACES.BASE_VEHICLE_ID
AND
Make.MakeID = BaseVehicle.MakeId
AND
Model.ModelID = BaseVehicle.ModelId
AND
Vehicle.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
车辆.BaseVehicleID = BaseVehicle.BaseVehicleID
AND
Vehicle.SubModelId = APPLICATION_ACES.SUBMODEL_ID
AND
SubModel.SubModelId = Vehicle.SubModelId
AND
DriveType.DriveTypeID = APPLICATI ON_ACES.DRIVE_TYPE_ID
AND
VehicleToBodyStyleConfig.BodyStyleConfigID =
APPLICATION_ACES.BODY_STYLE_CONFIG_ID
AND
VehicleToBodyStyleConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
BodyStyleConfig.BodyStyleConfigID =
VehicleToBodyStyleConfig.BodyStyleConfigID
AND
BodyNumDoors.BodyNumDoorsID = BodyStyleConfig.BodyNumDoorsID
AND
BodyType.BodyTypeID = BodyStyleConfig.BodyTypeID
AND
VehicleToTransmission.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToTransmission.TransmissionID =
APPLICATION_ACES.TRANSMISSION_ID
AND
Transmission.TransmissionID =
VehicleToTransmission.TransmissionID
AND
TransmissionBase.TransmissionBaseID =
Transmission.TransmissionBaseID
AND
TransmissionType.TransmissionType ID =
TransmissionBase.TransmissionTypeID
AND
TransmissionNumSpeeds.TransmissionNumSpeedsID =
TransmissionBase.TransmissionNumSpeedsID
AND
TransmissionControlType.TransmissionControlTypeID =
TransmissionBase.TransmissionControlTypeID
AND
VehicleToEngineConfig.VehicleID = APPLICATION_ACES。 VEHICLE_ID
AND
VehicleToEngineConfig.EngineConfigID =
APPLICATION_ACES.ENGINE_CONFIG_ID
AND
EngineConfig.EngineConfigID =
VehicleToEngineConfig.EngineConfigID
AND
EngineBase.EngineBaseID = EngineConfig.EngineBaseID
AND
EngineDesignation.EngineDesignationID =
EngineConfig.EngineDesignationID
AND
Aspiration.AspirationID = EngineConfig.AspirationID
AND
FuelType.FuelTypeID = EngineConfig.FuelTypeID
AND
Ve hicleToBrakeConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToBrakeConfig.BrakeConfigID =
APPLICATION_ACES.BRAKE_CONFIG_ID
AND
BrakeConfig.BrakeConfigID = VehicleToBrakeConfig.BrakeConfigID
AND
BrakeType1.BrakeTypeID = BrakeConfig.FrontBrakeTypeID
AND
BrakeType2.BrakeTypeID = BrakeConfig.RearBrakeTypeID
AND
BrakeSystem.BrakeSystemID = BrakeConfig.BrakeSystemID
AND
BrakeABS.BrakeABSID = BrakeConfig.BrakeABSID
AND
PART.PART_ID = APPLICATION_ACES.PART_ID
AND
对于
$
Bob Quintal
PA我已经改变了我的电子邮件地址。
**发自 http：//www.te ranews.com **
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.MakeName) as Make,
Trim(Model.ModelName) as Model,
YearID,
Trim(SubModel.SubModelName) as Sub,
Trim(DriveType.DriveTypeName) as DriveType,
BodyNumDoors.BodyNumDoors + "DR" as Doors,
Trim(BodyType.BodyTypeName) as Body,
Trim(TransmissionType.TransmissionTypeName) as Trans,
Trim(TransmissionNumSpeeds.TransmissionNumSpeeds) as TransSpeeds,
Trim(TransmissionControlType.TransmissionControlTy peName) as
TransControl,
EngineBase.Liter + "L" as EngLiter,
EngineBase.CC as EngCC,
EngineBase.Cylinders + " CYL" as EngCyl,
Trim(EngineDesignation.EngineDesignationName) as EngineName,
Trim(Aspiration.AspirationName) as Aspiration,
Trim(FuelType.FuelTypeName) as Fuel,
BrakeType1.BrakeTypeName as FrontBrake,
BrakeType2.BrakeTypeName as RearBrake,
BrakeSystem.BrakeSystemName as BrakeName,
BrakeABS.BrakeABSName as ABS,
PART_NUMBER,
FITMENT_NOTE_DESC
INTO
RAPPORT_MAPPING
FROM
APPLICATION_ACES,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BaseVehicle,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Vehicle,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Make,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Model,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].SubModel,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].DriveType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].VehicleToBodyStyleConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyStyleConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyNumDoors,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].VehicleToTransmission,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Transmission,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionBase,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionNumSpeeds,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].TransmissionControlType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].VehicleToEngineConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineBase,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineDesignation,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Aspiration,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].FuelType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].VehicleToBrakeConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeType as
BrakeType1,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeType as
BrakeType2,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeSystem,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeABS,
PART,
FITMENT_NOTES_LEGACY
WHERE
BaseVehicle.BaseVehicleID = APPLICATION_ACES.BASE_VEHICLE_ID
AND
Make.MakeID = BaseVehicle.MakeId
AND
Model.ModelID = BaseVehicle.ModelId
AND
Vehicle.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
Vehicle.BaseVehicleID = BaseVehicle.BaseVehicleID
AND
Vehicle.SubModelId = APPLICATION_ACES.SUBMODEL_ID
AND
SubModel.SubModelId = Vehicle.SubModelId
AND
DriveType.DriveTypeID = APPLICATION_ACES.DRIVE_TYPE_ID
AND
VehicleToBodyStyleConfig.BodyStyleConfigID =
APPLICATION_ACES.BODY_STYLE_CONFIG_ID
AND
VehicleToBodyStyleConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
BodyStyleConfig.BodyStyleConfigID =
VehicleToBodyStyleConfig.BodyStyleConfigID
AND
BodyNumDoors.BodyNumDoorsID = BodyStyleConfig.BodyNumDoorsID
AND
BodyType.BodyTypeID = BodyStyleConfig.BodyTypeID
AND
VehicleToTransmission.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToTransmission.TransmissionID =
APPLICATION_ACES.TRANSMISSION_ID
AND
Transmission.TransmissionID = VehicleToTransmission.TransmissionID
AND
TransmissionBase.TransmissionBaseID =
Transmission.TransmissionBaseID
AND
TransmissionType.TransmissionTypeID =
TransmissionBase.TransmissionTypeID
AND
TransmissionNumSpeeds.TransmissionNumSpeedsID =
TransmissionBase.TransmissionNumSpeedsID
AND
TransmissionControlType.TransmissionControlTypeID =
TransmissionBase.TransmissionControlTypeID
AND
VehicleToEngineConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToEngineConfig.EngineConfigID =
APPLICATION_ACES.ENGINE_CONFIG_ID
AND
EngineConfig.EngineConfigID = VehicleToEngineConfig.EngineConfigID
AND
EngineBase.EngineBaseID = EngineConfig.EngineBaseID
AND
EngineDesignation.EngineDesignationID =
EngineConfig.EngineDesignationID
AND
Aspiration.AspirationID = EngineConfig.AspirationID
AND
FuelType.FuelTypeID = EngineConfig.FuelTypeID
AND
VehicleToBrakeConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToBrakeConfig.BrakeConfigID =
APPLICATION_ACES.BRAKE_CONFIG_ID
AND
BrakeConfig.BrakeConfigID = VehicleToBrakeConfig.BrakeConfigID
AND
BrakeType1.BrakeTypeID = BrakeConfig.FrontBrakeTypeID
AND
BrakeType2.BrakeTypeID = BrakeConfig.RearBrakeTypeID
AND
BrakeSystem.BrakeSystemID = BrakeConfig.BrakeSystemID
AND
BrakeABS.BrakeABSID = BrakeConfig.BrakeABSID
AND
PART.PART_ID = APPLICATION_ACES.PART_ID
AND
FITMENT_NOTES_LEGACY.FITMENT_NOTE_ID =
APPLICATION_ACES.FITMENT_NOTE_ID
;
解决方案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.
Harel <gu********@gmail.comwrote in news:b1752fc5-3616-4479-b62e-
4c**********@d45g2000hsc.googlegroups.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.setwarnings false, and
follow it with Docmd.setwarnings true.
--
Bob Quintal
PA is y I''ve altered my email address.
** Posted from http://www.teranews.com **
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********@gmail.comwrote in
news:85c81575-13b2-4a99-9a0f-
7f**********@j22g2000hsf.googlegroups.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.MakeName) as Make,
Trim(Model.ModelName) as Model,
YearID,
Trim(SubModel.SubModelName) as Sub,
Trim(DriveType.DriveTypeName) as DriveType,
BodyNumDoors.BodyNumDoors + "DR" as Doors,
Trim(BodyType.BodyTypeName) as Body,
Trim(TransmissionType.TransmissionTypeName) as Trans,
Trim(TransmissionNumSpeeds.TransmissionNumSpeeds) as
TransSpeeds,
Trim(TransmissionControlType.TransmissionControlTy peName) as
TransControl,
EngineBase.Liter + "L" as EngLiter,
EngineBase.CC as EngCC,
EngineBase.Cylinders + " CYL" as EngCyl,
Trim(EngineDesignation.EngineDesignationName) as EngineName,
Trim(Aspiration.AspirationName) as Aspiration,
Trim(FuelType.FuelTypeName) as Fuel,
BrakeType1.BrakeTypeName as FrontBrake,
BrakeType2.BrakeTypeName as RearBrake,
BrakeSystem.BrakeSystemName as BrakeName,
BrakeABS.BrakeABSName as ABS,
PART_NUMBER,
FITMENT_NOTE_DESC
INTO
RAPPORT_MAPPING
FROM
APPLICATION_ACES,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BaseVehicle,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Vehicle,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Make,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Model,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].SubModel,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].DriveType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].VehicleToBodyStyleConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyStyleConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyNumDoors,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].VehicleToTransmission,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Transmission,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionBase,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].TransmissionNumSpeeds,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].TransmissionControlType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].VehicleToEngineConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineBase,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineDesignation,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Aspiration,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].FuelType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].VehicleToBrakeConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeType as
BrakeType1,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeType as
BrakeType2,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeSystem,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeABS,
PART,
FITMENT_NOTES_LEGACY
WHERE
BaseVehicle.BaseVehicleID = APPLICATION_ACES.BASE_VEHICLE_ID
AND
Make.MakeID = BaseVehicle.MakeId
AND
Model.ModelID = BaseVehicle.ModelId
AND
Vehicle.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
Vehicle.BaseVehicleID = BaseVehicle.BaseVehicleID
AND
Vehicle.SubModelId = APPLICATION_ACES.SUBMODEL_ID
AND
SubModel.SubModelId = Vehicle.SubModelId
AND
DriveType.DriveTypeID = APPLICATION_ACES.DRIVE_TYPE_ID
AND
VehicleToBodyStyleConfig.BodyStyleConfigID =
APPLICATION_ACES.BODY_STYLE_CONFIG_ID
AND
VehicleToBodyStyleConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
BodyStyleConfig.BodyStyleConfigID =
VehicleToBodyStyleConfig.BodyStyleConfigID
AND
BodyNumDoors.BodyNumDoorsID = BodyStyleConfig.BodyNumDoorsID
AND
BodyType.BodyTypeID = BodyStyleConfig.BodyTypeID
AND
VehicleToTransmission.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToTransmission.TransmissionID =
APPLICATION_ACES.TRANSMISSION_ID
AND
Transmission.TransmissionID =
VehicleToTransmission.TransmissionID
AND
TransmissionBase.TransmissionBaseID =
Transmission.TransmissionBaseID
AND
TransmissionType.TransmissionTypeID =
TransmissionBase.TransmissionTypeID
AND
TransmissionNumSpeeds.TransmissionNumSpeedsID =
TransmissionBase.TransmissionNumSpeedsID
AND
TransmissionControlType.TransmissionControlTypeID =
TransmissionBase.TransmissionControlTypeID
AND
VehicleToEngineConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToEngineConfig.EngineConfigID =
APPLICATION_ACES.ENGINE_CONFIG_ID
AND
EngineConfig.EngineConfigID =
VehicleToEngineConfig.EngineConfigID
AND
EngineBase.EngineBaseID = EngineConfig.EngineBaseID
AND
EngineDesignation.EngineDesignationID =
EngineConfig.EngineDesignationID
AND
Aspiration.AspirationID = EngineConfig.AspirationID
AND
FuelType.FuelTypeID = EngineConfig.FuelTypeID
AND
VehicleToBrakeConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToBrakeConfig.BrakeConfigID =
APPLICATION_ACES.BRAKE_CONFIG_ID
AND
BrakeConfig.BrakeConfigID = VehicleToBrakeConfig.BrakeConfigID
AND
BrakeType1.BrakeTypeID = BrakeConfig.FrontBrakeTypeID
AND
BrakeType2.BrakeTypeID = BrakeConfig.RearBrakeTypeID
AND
BrakeSystem.BrakeSystemID = BrakeConfig.BrakeSystemID
AND
BrakeABS.BrakeABSID = BrakeConfig.BrakeABSID
AND
PART.PART_ID = APPLICATION_ACES.PART_ID
AND
FITMENT_NOTES_LEGACY.FITMENT_NOTE_ID =
APPLICATION_ACES.FITMENT_NOTE_ID
;
--
Bob Quintal
PA is y I''ve altered my email address.
** Posted from http://www.teranews.com **
这篇关于大sql导致访问崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
