@Mackbear,
You are probably going to be flabbergasted when you convert your tables to SQL Server and find the app runs much slower than it did with Jet/ACE tables. Who knew? Jet/ACE are optimized to work with Access and are exceptionally fast so if you created your Access FE using "Access" techniques rather than client/server techniques, you WILL have to make FE changes. The biggest issue will be forms that are bound to tables or to queries that have no selection criteria. This technique forces Access to pull down the ENTIRE recordset to memory on the local PC where it can be filtered using form filters. If your tables have only a few thousand rows (you probably shouldn't be converting to SQL Server at this time), then you probably won't notice slowness. However, if you are converting because you have too much data to continue using Jet/ACE then the difference will be noticeable. The client/server technique is to always bind your forms to queries that include selection criteria that dramatically limits the rows returned. it is far more efficient to bring down 20 records, one at a time for the user to interact with than to bring down 200,000 and filter them locally to get to the few records that the user needs to access. Sometimes I create search forms if the search criteria can be extensive or complicated and the search form builds the WHERE clause and binds the query to a list form. The user then selects one record at a time from the list form to work with. In other cases, if there are only a couple of fields, I use static querydefs with WHERE clauses that reference the search fields on the form. These forms open empty. The use enters his criteria and then the single record he wants is retrieved. Access naturally "passes through" ALL queries so if your query requests 1 record, that is all the server will retrieve and return to you. This cuts down network traffic as well as reducing the memory requirements. Actual Pass thru queries are a good idea for bulk updates and bulk deletes because they do NOT run within a transaction so Access won't prompt you "100 records will be updated, do you want to continue?". It will just apply the update/delete. Pass through queries are slightly faster because there is no local overhead but not enough to switch to unbound forms and all that entails.
In some cases, you might find you have to create views to perform certain common joins to get some efficiency and in even rarer cases, you might need to create stored procedures to collect the data for complex reports. In NO case have I in 25+ years had to resort to using an unbound form for editable data. I did have one case where the search criteria was so convoluted that I did need a sp to populate the form so even though it was still technically bound, it wasn't updateable.
When I create apps, I always build them as if I might some day have to convert the BE to SQL Server. That means that when the time comes, I can do the conversion generally in under an hour as long as I don't have any conversion issues.