I believe the Dominion War officially started when the Cardassians threw in their lot with the Dominion and gave them a foothold in the Alpha Quadrant.
That did occur to me, but when I was growing up my sister actually had a poster of Rick on her wall. I am not shitting you.
Two hundred years later would Picard, Sisko and Janeway be remember at all? Or in their case, would they be obscure footnotes in history (as opposed to "famous"). Sisko would be famous to the Bajorian people, but the Federation seems to fight a lot of wars, the Dominion War would be better remembered with Admirals, not some Captain so many years later. Captain Sisko had a active few years, but not like the few decades of someone like Captain Kirk. Janeway is the girl who got kidnapped and had to walk home. Picard's historical position is firmer than Sisko and Janeway. But the events of the movies might taint his memory, he did denied the people of the Federation a important medical advance. If in the early 20th century Picard had denied the Human species access to penicillin, would he be remember for anything else today?
From the 26th Century... Archer, well without him there would be no Federation. Picard, essentially your typical perfect Captain. By then though there would have been a few more Enterprises and captains. They'd probably be a general study of Flagship History altogether. Sisko, practically won the Dominion War single-handed. So studying the Dominion War would have a huge chapter on him. Janeway, would likely be the focus of the first chapter on the Delta Quadrant. Of course by then there'll be multiple galaxies to study. So, Archer would be the most famous I'd say.
That might be taking things too far. Without Archer, Starfleet would have planted someone else in the centerseat. The series of events that transpired leading to the Romulan War and the later Federation would have still happened, even without Archer's existence. The crew of the Enterprise likely would have been identical, minus Archer, and plus (imho) Hernandez. Looking at the series, Hernandez would have been the obvious second choice for the Enterprise's command. With another Captain chosen for the Columbia.
If Hernandez had been captain instead of Archer.. that would have been incredibly wonderful. But maybe not worked well with the series plan to show how stupid humans were in the beginning.
We saw what happened in without Archer in "Shockwave" - an Earth in ruins, and the Federation never existed. Be may have been written extremely blandly (at first, anyway), but in-universe, the guy was essential.
But isn't Spock the only one to be dead at the end of a movie/episode then brought back later? I'm talking about on-screen.
I'm late to the thread, but I'd suggest it may depend on the state of the Federation (It might have changed to the point where Archer wouldn't be given credit for their government entity) and where the Federation citizen is living. If the Federation has outposts or colonies deep inside the Delta Quadrant somewhere alone the route Voyager took, Janeway may be famous for those Federation races living there... On Bajor, it may be Sisko. Let me give you an example. My wife grew up in Virginia. It would be hard for her and those who grew up with her not to know who Stonewall Jackson is. But where I grew up, back in good ol' New England, not that many people would know until perhaps college. In other words, if whoever helped found the UFP in 2161 or explored the unknown in the 23rd Century were not relevant to people on certain worlds, they wouldn't know who Archer and Kirk are. Kirk would be forgotten once there are more starfleet captains exploring the cosmos. Janeway explored the unknown more than he did.
ARCHER: Hello, I was the first Earth captain to travel through space and make first contact. SISKO: Hello, I lead the Federation to victory in the Dominion War, knocked Q on his ass, and became a god to the people of Bajor. JANEWAY: Hello, I got my crew stranded in the Delta Quadrant, but I made up for it by kicking the Borg's ass. PICARD: Hello, I have saved numerous worlds, sufficiently annoyed the hell out of Q, defeated the Borg after the battle of Wolf 395, all the while being an officer and a gentlemen. KIRK: Hello, my name is Kirk, James Kirk. I defeated the god Apollo, I have shagged every hot babe this side of the galaxy. I have personally spread the human seed to every M class planet, except the ones where the women were really really ugly.