I have seen Access Forms running over the top of SQL server, it works but its far from ideal.

Is the intention to port the Access application directly to SQL server? If it is then I would suggest you write an application to sit over the top and act as the User interface.