In 2368, the Hornet joined Captain Jean-Luc Picard's fleet that blockaded the Klingon-Romulan border during the Klingon Civil War. The ship was one of twenty-three assembled for the fleet and was one of the seventeen chosen for the tachyon network. Identified by its name and registry number, the ship's position in the network was monitored from the USS Enterprise-D.
A plan was hatched by Picard, which he hoped would lure the Romulans through the detection grid. When Gowron launched a full attack against the forces of the House of Duras, the Romulan ships would rush to their aid. During this, the USS Excalibur would fall back with feigned engine troubles, taking the USS Akagi and the Hornet. In doing so, this would open up a small gap in the detection net for the Romulans to attempt to pass through. As their ships passed through the gap, the Enterprise-D and the USS Tian An Men would swing around, close the gap, and catch the Romulans in the act.
The plan was foiled when the network was disrupted by a high-energy burst released by the Romulans, the net was no longer effective in a radius of ten million kilometers around the USS Sutherland. Subsequently, Picard ordered the fleet to proceed to Gamma Eridon at maximum warp, where the fleet would attempt to reestablish the net before the Romulans got there. (TNG: "Redemption II")
In 2375, during the Dominion War, Hornet put in for repairs at Deep Space 9. However, in order to bring forward repairs of the Romulan warbirds Dividices and Genorex, Hornet, as well as the IKS Rotarran, had to stand by. (DS9: "Inter Arma Enim Silent Leges")
The ship's registry appeared in the okudagram depicting the tachyon detection network in "Redemption II". According to the Star Trek Encyclopedia (4th ed., vol. 1, p. 348), the Hornet was a Renaissance-class starship. In the first edition of the Star Trek Encyclopedia (p. 320), it was mistakenly stated that the registry number of this ship was NCC-10523.
According to the Encyclopedia (4th ed., vol. 1, p. 348), the ship was "named for an American aircraft carrier that fought at the Battle of Midway in World War II. Years later, another ship with that name was the recovery vessel for the Apollo 11 moon landing mission."
Purposely, the producers had Starfleet's Hornet serve alongside USS Akagi (named after a Japanese carrier that fought against the real life Hornet at Midway) in "Redemption II" – episode Writer Ronald D. Moore thought it fitting that, in a future where Humanity had become united by its own accord, ships whose namesakes had fought one another would now fight side-by-side. (Encyclopedia (4th ed., vol. 1, p. 14))
Hornet was a starship name considered by producers D.C. Fontana and Robert Justman at the start of second season of Star Trek: The Original Series for inclusion in the definitive fourteen ship list belonging to the Constitution-class, at the time still referred to as Starship-class by them. It was mentioned in the first two, 8 and 9 August 1967 proposals, but ultimately did not make the cut. Hornet's historical class-sisters, USS Yorktown and USS Enterprise (both having fought alongside their sister at Midway), after whom Gene Roddenberry had named his original Star Trek starship in succession, had made the cut however. (The Making of Star Trek, pp. 164-165) "Redemption II" marked the occasion that Hornet too was finally inducted into canon.
According to the Star Fleet Technical Manual, there was a Constitution-class USS Hornet in service in the 23rd century with the registry NCC-1714 (although the game supplement Federation Ship Recognition Manual gives its as registry NCC-1778). This would seem to be the same Hornet that was mentioned as a combatant in the novel Dreadnought!.