How to Link an Access Database to SQL Server in Access 2016
How to Link an Access Database to SQL Server in Access 2016
You can store data in a SQL Server database, then link to SQL Server from Access 2016 in order to run queries, reports, and even insert/update data.
Linking to a SQL Server database from Access is not much different to creating a linked table to a backend Access database.
SQL Server is a more robust database management system than Access. However, Access does have certain advantages over SQL Server, like being able to create forms and reports easily — even within a single click.
Also, if users are well trained in Access but not SQL Server, and there's no plan to retrain them to use SQL Server, using Access as the frontend and SQL Server as the backend can make a lot of sense.
Below is a step-by-step guide to linking to SQL Server from Access 2016 (and creating a new data source file in the process).
Launch the Import/Link Wizard
Click ODBC Database in the Import & Link group from the External Data tab on the Ribbon.
You can start from an existing Access database, or you can create a blank database first.
Select the "Link" Option
Select Link to the data source by creating a linked table, then click OK.
Select the Data Source
This screen is asking for a data source. You can either create a new data source, or provide the details of an existing one.
If you already have a data source for SQL Server, skip to step 12.
Otherwise, you will need to create a data source here.
For this example, we will create a new data source.
To create a data source, click New....
Select a Driver
Select SQL Server from the list of drivers, then click Next >.
The following steps may differ slightly, depending on the software installed on your machine.
Enter the Data Source Name
Type the name of the data source you'd like to save the connection to (or browse to a location using the Browse... button), then click Next >.
Create the Data Source you just Configured
Review the information and click Finish.
The "Create a New Data Source to SQL Server" Wizard
We're not quite finished yet though. We still need to provide some extra details for our connection to SQL Server.
Provide a description for your data source, select the SQL Server that you'd like to connect to, then click Next >.
If the database is on the same server you can select (local).
Choose an Authentication Method
Select either Windows NT authentication or SQL Server authentication, then click Next >.
Client Configuration
Click Client Configuration if you want the connection to use a network library other than the client's default network library.
Also use this option if the actual network address of the server must be specified for a successful connection. For example, when using the TCP/IP Net-Library, you might need to specify the port and socket address of the server. If a SQL Server instance is listening on an alternate named pipe, you must specify the pipe name in the advanced entry.
Set the Default Database
Change the default database to the actual database that contains the tables/views you want to link to.
Change any other settings if required.
Click Next >.
When ANSI quoted identifiers is selected, SQL Server enforces ANSI rules regarding quote marks. Double quotes can only be used for identifiers, such as column and table names. Character strings must be enclosed in single quotes.
Selecting ANSI nulls, paddings, and warnings specifies that the ANSI_NULLS, ANSI_WARNINGS, and ANSI_PADDINGS options be set on when the SQL Server driver connects.
ANSI_NULLS requires that IS NULL or IS NOT NULL is used for all NULL comparisons. The Transact-SQL syntax = NULL is not supported.
ANSI_WARNINGS specifies that SQL Server issues warning messages for conditions that violate ANSI rules but do not violate the rules of Transact-SQL.
ANSI_PADDING specifies that trailing blanks on varchar values and trailing zeroes on varbinary values are not automatically trimmed.
Specify any Extra Options
Modify any settings as required, then click Finish.
Review the ODBC Data Source Settings
Review the summary, then click OK.
You can (optionally) click Test Data Source... first to check to see if it's going to work.
Select Data Source
We're now back at the original dialog box that asks us to select a data source.
We now have a DSN file so we can continue on and connect to SQL Server.
Ensure that the DSN file's folder is selected next to Look in and the DSN file is entered next to DSN Name, then click OK.
Select which Tables/Views to add to Access
The wizard displays the tables and views from SQL Server so that you can select the ones you require.
Select all tables and/or views that you want to be able to use from within Access, then click OK.
Linked views will appear as linked tables in Access. They appear under the Tables node just as all other tables do.
You can also open them in Design View and review their fields, data types, properties, etc.
Select Unique Identifier
You will only see this dialog box if Access was unable to determine which field/s are the unique identifier for the table or view.
Select the field/s that uniquely identify each record, then click OK.
This step will set the selected fields as primary keys in Access. You can verify this by viewing the table in Design View later.
The Linked Tables
The linked tables now appear in the left Navigation Pane.
You can always identify a linked table by the little arrow icon to its left. Also, the SQL Server tables use a globe icon, which is different to 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 would be represented by dbo. (for example, dbo.Artists.
Not all tables will necessarily belong to the dbo schema, so your tables may include a different prefix, depending on the schema that each table belongs to in SQL Server
You can now open the linked tables in Datasheet View, and update the data as required. Any data updates you do in Access are updated in the SQL Server database. Likewise, any updates done from within SQL Server are also reflected in your linked tables in Access.
You can also open the linked tables in Design View, but you won't be able to change the design. Any design changes need to be done to the source database.
