I've been using Access as an SQL frontend for over a decade, both as Access Projects (.adp) and Access Databases (older versions .mdb & new versions .accdb). Using Access can be as simple or as complicated as needed for the frontend. For the most part, Access builds the forms for you using wizards. First, let the wizard create the basic form. Then, you customize it as needed by creating combo boxes for lookup tables and command buttons to link to other forms by dragging and dropping to initiate the wizard. You don't need to know how to code a command button or create a lookup combo box. However, because I implement various business rules at the frontend level, I use the code-behind VBA of the forms to enforce these rules. For example, I'll disable certain fields based on the data of other fields. The only caveat to using Access is that sometimes it is tricky to use stored procedures, which you typically do have to create VBA code to execute. Further, for maintenance purposes, I find using Access to do quick data edits in a table or view easier than in SSMS. Of course, this is for when an Update query or stored procedure can't be used. I'm frequently having to clean up imported data, where the bad data is too inconsistent to use an Update query with a Where clause.

My decision to use a Project over a Database depends on my users. My SQL Server databases tend to contain data for several audiences. If the audience of the intended frontend uses only a few tables and views, I'll create an Access Database with an ODBC connection and link only the necessary items. However, I use an Access Project, which directly connects to the SQL Server, for when the audience uses several objects or when I need to use stored procedures as the record source for my forms. There are major differences, though, between a project and a database. An Access Project only allows you create and maintain forms, reports, macros and VBA modules. Whereas with an Access Database, you can also create frontend queries and tables that are useful with static lookup data (i.e., a lookup table of the 50 states for all State fields), which can minimize data transmission to/from the server. In addition, accessing stored procedures differs between the two depending on how you need to execute them.