This sounds like a BIOS error -- the BIOS isn't finding the right drive to boot from when in AHCI mode. I do not believe you're dealing with an OS issue.
Try stripping the system down to bare minimums.
Plug in only one bootable hard drive (the HDD or SSD, whichever contains the OS system partition) and no optical drives and try booting.
Plug in only one optical drive and no harddrives and try booting. If it doesn't work, disconnect and try the other optical drive.
Plug in only one bootable hard drive and one optical drive. You should be able to boot to the hard drive and the DVD.
When trying to boot from a single device, make sure you try all SATA ports on the motherboard before giving up.
If you can boot in those situations, your problem is the ports you've connected your drives to -- try rearranging them so they connect to different ports, to force the BIOS to enumerate them in the correct order.
If you can boot to the optical drive but not the hard drive, you may have a broken bootloader (eg, if the bootloader is installed to the non-system drive's MBR but not the system drive's MBR). You should be able to fix this from the Win-7 repair console.
If none of those work, make sure you have the latest BIOS firmware and try again. You may need to try resetting the BIOS to defaults and reselecting AHCI mode.
