Each of the previous tables has a corresponding model table: ETMSFlight – ModelFlight ETMS_InconsistentFlight – ModelFlight ETMS_ExpandedFlight – ModelFlight
This model table is always empty, but it stores the structure of the 3 tables previously described. To empty the tables, the following code is used:
'--------------------------------------------------------------------------------
DoCmd.DeleteObject acTable, "ETMSFlight"
DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentDb.Name, acTable, "ETMSFlight", "ModelFlight", True
DoCmd.DeleteObject acTable, "ETMS_InconsistentFlight"
DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentDb.Name, acTable, "ETMS_InconsistentFlight", "ModelFlight ", True
DoCmd.DeleteObject acTable, "ETMS_ExpandedFlight"
DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentDb.Name, acTable, "ETMS_ExpandedFlight", "ModelFlight", True
'--------------------------------------------------------------------------------
3.3.3.3. Step 3 -Import ETMS traffic file
To complete this step, five other sub steps must be executed.
3.3.3.3.1. Sub step 3.1 -Importing ETMS traffic text file into the temporary table ETMS_ImportedTraffic
This first sub step implies the physically importing of the ETMS traffic text file. To do that the following code is used:
'------------------------------------------------------------------------------
DoCmd.TransferText acImportDelim, "ETMS Traffic Import Specification", "ETMS_ImportedTraffic", FileName, False, ""
'------------------------------------------------------------------------------
The "ETMS Traffic Import Specification" is a predefined mode to import the ETMS traffic text file, which’s path\name is stored in the FileName variable. This specification selects from the text file only the fields that the AERO2K needs and also defines each one’s date type (see Table 55).
3.3.3.3.2. Sub step 3.2 – Check and correct the departure time
The second sub step purpose is to check the imported departure time and if inconsistent data is found, then the departure time is updated to the first message time value. The updating query SQL code is:
'------------------------------------------------------------------------------
UPDATE ETMS_ImportedTraffic SET ETMS_ImportedTraffic.DepartureTime = [ETMS_ImportedTraffic].[FirstMessageTime] WHERE (([ETMS_ImportedTraffic].[DepartureTime] Is Null) Or ([ETMS_ImportedTraffic].[DepartureTime]<#1/1/2000#));
'------------------------------------------------------------------------------
3.3.3.3.3. Sub step 3.3 – Check and correct the arrival time The third sub step purpose is to check the imported arrival time and if inconsistent data is found, then the arrival time is updated to the last message time value. The updating query SQL code is:
'------------------------------------------------------------------------------
UPDATE ETMS_ImportedTraffic SET ETMS_ImportedTraffic.MaxStopTime = [ETMS_ImportedTraffic].[LastMessageTime] WHERE (([ETMS_ImportedTraffic].[MaxStopTime] Is Null) Or ([ETMS_ImportedTraffic].[MaxStopTime]<#1/1/2000#));
'------------------------------------------------------------------------------
3.3.3.3.4. Sub step 3.4 - Changing imported traffic data to AERO2K specific data type This fourth sub step calls an appending query that saves the imported/corrected data in the traffic table (ETMSTraffic). The appending query SQL code is:
'------------------------------------------------------------------------------
INSERT INTO ETMSTraffic (CallSign, DepartureTime, DepartureAirport, ArrivalAirport, AircraftType, MaxStopTime) SELECT ETMS_ImportedTraffic.CallSign, ETMS_ImportedTraffic.DepartureTime, ETMS_ImportedTraffic.DepartureAirport, ETMS_ImportedTraffic.ArrivalAirport, ETMS_ImportedTraffic.AircraftType, ETMS_ImportedTraffic.MaxStopTime FROM ETMS_ImportedTraffic;
中国航空网 www.aero.cn
航空翻译 www.aviation.cn
本文链接地址：AERO2K Flight Movement Inventory Project Report(59)
