MS Access have a nice possibility if you want to connect with SQL Server databases and manipulate with data in the same way as MS Access mdb files. The MS Access files that can connect to the SQL Server has the extension .adp (Access Data Project - file) and after the successful connection you can manipulate with data using Forms, Reports, Macros from Access side and Queries, as well as Store Procedures and UDFs.

The possibility for connection from MS Access adp files are since early Access version ( from 2000, XP, 2003, 2007), and here I will cover the Access 2010 the newest version of MS Office packet.

Create .adp file from MS Access:

Start the MS Access from Start menu > All Programs > Microsoft Office > MS Access. In the right of the Access windows, you have possibility to open the folder place where do you want to save the Access file, so you choose the folder name from dialog box but you should save it as type .adp file like in the fig below

After you created the adp file and saving it, in to the folder, now you can set up the connection to your SQL Server, opening that file and click to the File Red Menu:

After you click the Connection, you will see the dialog box that uses to put SQL Server name and other details for the successful connection to the SQL Server Database.
 

According to the dialog box for the connection, you should put SQL Server name, then to choose the one of the connection way (Windows Authentication or Windows username (no need password it will recognize from your windows user-name) or SQL Server Authentication thar requires the SQL User-name and Password, in this case you should choose to Allow saving Password, when you reopen that file. Finally, you should select the database that is located to your SQL Server then click Test (testing connection) and OK.

On the left side of the MS Access adp project file you will see the core objects that are in your SQL Server database such as tables, views, store procedures, functions etc. Now you will be able to use these objects that are in you SQL Server Database and publishing the data from your SQL Server using forms, also you will be able to create reports...etc.

All the MS Access users can manage and work with SQL Server databases and can use much more like Store Procedures...

Have a nice SQLing with MS Access...