Ok, my motherboard has a single SATA controller (Intel 975X / ICH7R). It has 3 internal SATA ports and one eSATA port. In the BIOS, I have to set the SATA chipset to AHCI to enable hotplug support for the eSATA hard drive in Windows. Will having the SATA chipset in AHCI mode affect any SATA optical drive I install in my system?

Yes, it can be a problem for optical drives, they will prefer IDE mode. Many moainboards have two SATA controllers which alows you to run SATA2 hard drives in AHCI on the main (Southbridge) and opticals in legacy mode on the auxiliary controller. A SATA PCI(-X)-card may be a solution for you.