Sorry about the late reply - been a BUSY few days. If you are building a front-end to SQL Server, then you need to use ODBC to link to the tables, unless you plan to use only unbound forms and write lots of VBA to use ADO or DAO. If that's the case, then you have to embed the server name in the connect string that you use to talk to SQL Server.

Stored procedures are only useful in specific circumstances where you want to process lots of data quickly, and most developers using .accdb (or .mdb) Access databases end up not using them. What is more useful is linking to a SQL Server view, which is viewed by Access as just another table. So long as you used linked tables, the server name is stored in the ODBC connection string stored in the table details, and should not be a problem. Now to your combo box issue, the data source can be a query as you suggest, but I would actually create a view in SQL Server, link to it and then make the linked table the data source for the combo. If you want to have a specific sequence for the options, then you will want to make it an Access SQL String (query) and specify that it sorts on the appropriate field in the correct order. I'm actually surprised that the builder will give you the correct results when putting in the fully qualified name, but that may come from the .ADP use of the builders.

Access .adp files are completely different from other Access database formats, and you do have to most of the work in SQL Server other than the forms and reports. And because of synchronization issues between Access versions and SQL Server versions, the .adp format is no longer supported in Access 2013. See the article Changes in Access 2013 for more details on that decision.