How to Import a SQL Server Database into Access 2016
Access 2016 provides a wizard to help you import a SQL Server database into Access.
You can import a whole SQL Server database, or part of it, into an Access database. When you import a database into Access, you effectively take a copy of the database objects and its data. Therefore, any updates you make in Access is not reflected in the original SQL Server database. You can update the data and objects and it won't impact on the source database.
If you need all data changes to be reflected in the source database, you need to create linked tables to SQL Server instead. Doing that will keep allow you to update the data in the source database. However, you won't be able to change the table definitions from Access. Any structural changes need to be done on the SQL Server end.
Below is a step-by-step guide to importing a SQL Server database into Access 2016 (and creating a new data source in the process).
Select Unique Identifier
This screen is only displayed if Access can't find a unique identifier for a table.
If Access is unable to determine which field or fields uniquely identify a table or view, you will be prompted to set the unique identifier for that table.
Select the field/s that uniquely identify each record, then click OK.
You will need to do this for each table where the unique identifier can't be identified.
This step will set the selected fields as primary keys in Access. You can verify this by viewing the table in Design View later.
About the SQL Server Database in the Above Example
The SQL Server database used in the above example is the WideWorldImporters database. This is a sample database provided by Microsoft's SQL Server team for the purposes of demonstrations, training, etc. If you'd like to install this database to SQL Server, I have step-by-step instructions here.