"The question that I have for the community is if a SATA M.2 SSD will just not work in the Z97-A, or if it will work with updated drivers, software, workarounds, etc? (I did install the latest BIOS version.)"
The answer is no, SATA M,2 SSDs do not work with that board's slot. Only PCIe.