Undoubtedly, you can use SQL server database as a back-end for an access database application. In such case, Microsoft access forms and reports works as a front end and the SQL server tables, view and stored procedure works as a back-end SQL server database. You can use either free version or the licensed version of the SQL Server database software. Problem with the free version is its smaller size limitation where as the licensed version have a very large size limitations of 524 PB (petabytes) for SQL Server 2008 R2.
Advantages:
• Advantages of using SQL server database with access include scalability, increased database capacity and security.
• SQL Server is more scalable database technology that allows more Access database users while maintaining performance.
• SQL Server has more robust security which includes integrated windows based that make it more suitable as backend database choice where data security is an issue.
• MS Access has a database size limitation of 2 GB, whereas SQL Server has a minimum size limitation of 4 GB for the free version and unlimited capacity for the licensed versions. Therefore database size will get raised.
• Besides this there are many more advantages of using SQL server backend with Microsoft access including, SQL Server Reporting Services, SQL Server Integration Services, .NET CLR Integration, etc.