A couple of months ago, after demonstrating due diligence (that is, consulting the readership), I decided to take a look at connecting from Access to SQL Server. There are essentially three choices.
First, you can use Access to connect to an existing SQL Server database. This allows you to use Access to build forms, run queries, and add and modify data but doesnt let you modify the data structure (add new tables and so on). Second, you can upsize an existing Access database to a SQL Server database. Or third, you can use Access to manipulate both the data and the data structure
The article continues at http://www.pcw.co.uk/personal-computer-world/features/2239334/access-sql-server-4398744