Do you know that you can store data in SQL server database then link the SQL server from access 2016 in order to run queries, insert/update data, reports?
Linking to SQL server database from Access is much different to create a linked table to a backend Access database.
SQL server is much robust database management system than Access. Access doesn't have certain advantages over SQL Server like creating form and reports easily, even within a single click.
Here is complete guide to link to SQL server from Access 2016.
Click ODBC database in the Import & Link group from the External Data tab on the Ribbon.
Tap to the Link to the data source by creating a linked table, and then click OK.
Now you need to select the data source or provide the details of an existing one. If you have data source for SQL server, skip to step 12.
Otherwise, you will need to create a data source here. If in case, you will need to create the data source and then click new.
From the driver list Select sql server and then click Next..
Type name of the data source you will like to save the connection to and then click Next.
Review the information and click Finish.
Still you need to provide extra details for your connection to SQL server.
Add description for your data source. Select the SQL Server that you like to connect to and then click Next.
You can choose either Windows NT authentication or SQL Server authentication, and click Next.
Now you need to Change the default database to actual one that contains the tables/view you want to link to.
Make changes if any other setting is required and then click Next.
Modify the settings if required and then tap to the Finish option.
After reviewing the complete ODBC data source settings, click ok.
Now you are back to the original dialog box where you are prompted to select a data source.
So, now you have a DSN file we can continue on and connect to SQL server. Make sure that the DSN file folder is selected next to Look in and the DSN file is entered next to DSN Name, then click OK.
The wizard displays the table and views from SQL Server, so that you will easily select the one you require.
Select all the tables and view the one you want to use from within Access and then click Ok.
This dialogue box will appear only when Access was unable to determine which field/s are the unique identifiers for the table or view. So, select the fields that uniquely identify each record and then click ok.
In the left navigation pane, the complete listing of the linked table will appear. You can easily identify the linked table by the little arrow icon present to its left. Also the SQL Server tables use a globe icon, which is quite different from the table icon used for a linked access table.
The dbo_ prefix represents the schema name from the SQL server database. In sql server this is indicated by dbo. for example, dbo.Artists.
It's not necessary that the all table belongs to dbo schema, so your table can have a different prefix, depending on the schema that each table belongs to in sql server.
Now you can open up your linked table in datasheet view and update the data as needed. Any data update in access are seems updated in the SQL Server database. Just the same, any modification done from within SQL Server is also getting reflected in your linked tables in Access.
Now you can easily open the linked tables in design view but can't do any modification in the design. To make any changes to the design, it should be done to the source database.
