opening query in design view closes application

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

We created an Access DB in Access 2003 and converted it to 2000 for use by
another person. All of the queries seem to execute properly, but a few of
them cause the application to close when trying to open them in Design view.
An example of one of the queries is below.

Any ideas?

Thanks,
Jessica

Query EXAMPLE:
INSERT INTO tbl_STAGING_PBV ( Measure, Location, Period, Data )
SELECT DISTINCTROW tbl_Measurement_Lookup.Measure & " " &
tbl_Service_Lookup.Service AS Measure,
tbl_Mainframe_Agencies_Reference.Agency_Acronym & " " &
Right(tbl_Operations_Mainframe_SubEnvironment_Performance.MainframeId,1) & "
" & tbl_Operations_Mainframe_SubEnvironment_Performance.LPARSystem & " " &
tbl_Operations_Mainframe_SubEnvironment_Performance.SubEnvironment AS
Location,
Format$(tbl_Operations_Mainframe_SubEnvironment_Performance.Date,'yyyy/mm')
AS [Date By Month],
IIf(Sum(tbl_Operations_Mainframe_SubEnvironment_Performance.ScheduledSubEnvironmentUptime)=0,0.0001,Sum(tbl_Operations_Mainframe_SubEnvironment_Performance.ScheduledSubEnvironmentUptime)) AS MonthlyScheduledSubEnvironmentUptime
FROM tbl_Measurement_Lookup, tbl_Service_Lookup,
tbl_Operations_Mainframe_SubEnvironment_Performance INNER JOIN
tbl_Mainframe_Agencies_Reference ON
(tbl_Operations_Mainframe_SubEnvironment_Performance.LPARSystem=tbl_Mainframe_Agencies_Reference.LPAR)
AND
(tbl_Operations_Mainframe_SubEnvironment_Performance.Environment=tbl_Mainframe_Agencies_Reference.Environment)
AND
(tbl_Operations_Mainframe_SubEnvironment_Performance.SubEnvironment=tbl_Mainframe_Agencies_Reference.SubEnvironment)
AND
(tbl_Operations_Mainframe_SubEnvironment_Performance.MainframeID=tbl_Mainframe_Agencies_Reference.Mainframe)
WHERE
((Format$(tbl_Operations_Mainframe_SubEnvironment_Performance.Date,'yyyy/mm'))=Forms!frm_Monthly_Report_PBViews!txt_MonthlyReport)
GROUP BY tbl_Measurement_Lookup.Measure & " " & tbl_Service_Lookup.Service,
tbl_Mainframe_Agencies_Reference.Agency_Acronym & " " &
Right(tbl_Operations_Mainframe_SubEnvironment_Performance.MainframeId,1) & "
" & tbl_Operations_Mainframe_SubEnvironment_Performance.LPARSystem & " " &
tbl_Operations_Mainframe_SubEnvironment_Performance.SubEnvironment,
Format$(tbl_Operations_Mainframe_SubEnvironment_Performance.Date,'yyyy/mm'),
Format$(tbl_Operations_Mainframe_SubEnvironment_Performance.Date,'mmmm'),
tbl_Operations_Mainframe_SubEnvironment_Performance.MainframeID,
tbl_Operations_Mainframe_SubEnvironment_Performance.LPARSystem,
tbl_Operations_Mainframe_SubEnvironment_Performance.Environment,
tbl_Operations_Mainframe_SubEnvironment_Performance.SubEnvironment,
Year(tbl_Operations_Mainframe_SubEnvironment_Performance.Date)*12+DatePart('m',tbl_Operations_Mainframe_SubEnvironment_Performance.Date)-1,
tbl_Measurement_Lookup.Measure_Code, tbl_Service_Lookup.Service_Code
HAVING (((tbl_Measurement_Lookup.Measure_Code)="SPU") AND
((tbl_Service_Lookup.Service_Code)="MF"));
 
Based on some of the other postings, I tried de-selecting the "Name
AutoCorrect" option. This appears to correct the issue of the query closing
the DB when selecting design view.

However, what issues can I expect to encounter now that Name AutoCorrect is
off? If I change an object name, will I need to make manual updates
throughout the DB?

Jessica Huie said:
Hello,

We created an Access DB in Access 2003 and converted it to 2000 for use by
another person. All of the queries seem to execute properly, but a few of
them cause the application to close when trying to open them in Design view.
An example of one of the queries is below.

Any ideas?

Thanks,
Jessica

Query EXAMPLE:
INSERT INTO tbl_STAGING_PBV ( Measure, Location, Period, Data )
SELECT DISTINCTROW tbl_Measurement_Lookup.Measure & " " &
tbl_Service_Lookup.Service AS Measure,
tbl_Mainframe_Agencies_Reference.Agency_Acronym & " " &
Right(tbl_Operations_Mainframe_SubEnvironment_Performance.MainframeId,1) & "
" & tbl_Operations_Mainframe_SubEnvironment_Performance.LPARSystem & " " &
tbl_Operations_Mainframe_SubEnvironment_Performance.SubEnvironment AS
Location,
Format$(tbl_Operations_Mainframe_SubEnvironment_Performance.Date,'yyyy/mm')
AS [Date By Month],
IIf(Sum(tbl_Operations_Mainframe_SubEnvironment_Performance.ScheduledSubEnvironmentUptime)=0,0.0001,Sum(tbl_Operations_Mainframe_SubEnvironment_Performance.ScheduledSubEnvironmentUptime)) AS MonthlyScheduledSubEnvironmentUptime
FROM tbl_Measurement_Lookup, tbl_Service_Lookup,
tbl_Operations_Mainframe_SubEnvironment_Performance INNER JOIN
tbl_Mainframe_Agencies_Reference ON
(tbl_Operations_Mainframe_SubEnvironment_Performance.LPARSystem=tbl_Mainframe_Agencies_Reference.LPAR)
AND
(tbl_Operations_Mainframe_SubEnvironment_Performance.Environment=tbl_Mainframe_Agencies_Reference.Environment)
AND
(tbl_Operations_Mainframe_SubEnvironment_Performance.SubEnvironment=tbl_Mainframe_Agencies_Reference.SubEnvironment)
AND
(tbl_Operations_Mainframe_SubEnvironment_Performance.MainframeID=tbl_Mainframe_Agencies_Reference.Mainframe)
WHERE
((Format$(tbl_Operations_Mainframe_SubEnvironment_Performance.Date,'yyyy/mm'))=Forms!frm_Monthly_Report_PBViews!txt_MonthlyReport)
GROUP BY tbl_Measurement_Lookup.Measure & " " & tbl_Service_Lookup.Service,
tbl_Mainframe_Agencies_Reference.Agency_Acronym & " " &
Right(tbl_Operations_Mainframe_SubEnvironment_Performance.MainframeId,1) & "
" & tbl_Operations_Mainframe_SubEnvironment_Performance.LPARSystem & " " &
tbl_Operations_Mainframe_SubEnvironment_Performance.SubEnvironment,
Format$(tbl_Operations_Mainframe_SubEnvironment_Performance.Date,'yyyy/mm'),
Format$(tbl_Operations_Mainframe_SubEnvironment_Performance.Date,'mmmm'),
tbl_Operations_Mainframe_SubEnvironment_Performance.MainframeID,
tbl_Operations_Mainframe_SubEnvironment_Performance.LPARSystem,
tbl_Operations_Mainframe_SubEnvironment_Performance.Environment,
tbl_Operations_Mainframe_SubEnvironment_Performance.SubEnvironment,
Year(tbl_Operations_Mainframe_SubEnvironment_Performance.Date)*12+DatePart('m',tbl_Operations_Mainframe_SubEnvironment_Performance.Date)-1,
tbl_Measurement_Lookup.Measure_Code, tbl_Service_Lookup.Service_Code
HAVING (((tbl_Measurement_Lookup.Measure_Code)="SPU") AND
((tbl_Service_Lookup.Service_Code)="MF"));
Click to expand...
 
Back
Top