Advertisements

Import or link to data in an SQL Server database

Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

Newer versions

You can link to or import data from an SQL Database, which is a optimum-performing managed database used for mission-critical applications. For more information, see SQL Server 2016.

  • Once you link to data, Access forges a two-way connection that synchronises changes to data in Access and the SQL Database.
  • After you import data, Access designs a single-use, copy of the data, and thus edits to data in either Access or the SQL Database are unsynchronised.

Overview of connecting Access to SQL Server

Before you begin

Want things to go smoother? Then make the following preparations before you link or import:

  • Ascertain the tables or views that you want to link to or import, and uniquely-valued fields for linked tables. You can link to or import multiple tables or see in one operation.
  • Take account of the number of columns in each table or view. Access does not support further than 255 fields in a table, so Access links or imports merely the first 255 columns. As a solution, you can design a view in the SQL Server Database to reach the columns beyond the cap.
  • Influence the total amount of data being imported. The maximum size of an Access database is two gigabytes, minus the capacity required for system objects. If the SQL Server database has large tables, you may struggle to import them all into a single Access database. For such instances, be open to linking to the data rather than importing.
  • Secure your Access database and the connection information it occupies by using a trusted location and an Access database password. This is crucially relevant if you decide to save the SQL Server password in Access.
  • Plan for creating supplementary relationships. Access does not intuitively form relationships between associated tables at the end of an import operation. You can manually design the relationships between new and current tables by using the Relationships window. For more information, see What is the Relationships window? and Create, edit or delete a relationship.

Stage 1: Get started

  1. Pick External Data > New Data Source > From Database > From SQL Server.
  1. In the Get External Data – ODBC Database dialogue box, do one of the following:
  • To import data, click Import the source data into a new table in the current database.
  • To link to data, click Link the data source by creating a linked table.
  1. Press OK.

Stage 2: Create or reuse a DSN file

You can make a DSN file or reuse an existing one. Use a DSN file when you want to rely on the same connection information for various link and import operations or to share with another application that also uses DSN files. You can create a DSN file manually by using the Data Connection Manager. For more information, see Administer ODBC data sources.

Despite the fact that you can still use older versions of the SQL ODBC driver, we advise using version 13.1, equipped with numerous improvements, and supports new SQL Server 2016 features. For more information, see Microsoft ODBC Driver for SQL Server on Windows.

  1. Do one of the following:
  • If your desired DSN filet to use already exists, pick it from the list.
Select Data Source dialog box

Depending on which authentication method you typed the connection information, you might be prompted to specify a password again.

  • To create a new DSN file:
Create New Data Source dialog box

i. Click New.

ii. Choose ODBC Driver 13 for SQL Server, and then press Next.

iii. Type a name for the DSN file, or select Browse to produce the file in a new location.

  1. Select Next to inspect the summary information, and then pick Finish.

Stage 3: Use the Create a New Data Source to SQL Server wizard

In the Create a New Data Source to SQL Server wizard, do the following:

  1. On page one, input identification information:
  • In the Description box, optionally state documentary information about the DSN file.
  • In the Server box, type the name of the SQL Server. Do not select the down arrow.
  1. On page two, choose one of the following authentication methods:

  • With SQL Server authentication…  –  Connect with processed credentials that have been accepted in the database by stating the login ID and password.

  1. On pages three and four, pick different options to customise your connection. For more information about these options, see Microsoft ODBC Driver for SQL Server.
  1. A screen displays to verify your settings. Select Test Data Source to confirm your connection.
  1. You might have to log in to the database. In the SQL Server Login dialogue box, type the login ID and password. To edit additional settings, press Options.

Stage 4: Select tables and views to link to or import

  1. In the Link Tables or Import Objects dialogue box, below Tables, choose each preferred table or view that to link or import, and then pick OK.

List of tables to link or import

  1. In a link operation, consider whether to choose Save Password. Security  –  Confirming this option nullifies the obligation to type credentials every time you open Access and access the data. But, this stores an unencrypted password in the Access database, meaning people who can access the source contents can notice the user name and password. If you pick this option, we strongly advise storing the Access database in a trusted location and configuring an Access database password. For more information, see Decide whether to trust a database and Encrypt a database by using a database password.

Note  –  If you decide not to save the password, but then change your mind, you need to delete and re-create the linked table, and then select Save Password.

Stage 5: Create specifications and tasks (Import only)

Results

Once a link or import operation finishes, the tables launch in the Navigation Pane with the same name as the SQL Server table or view combined with the owner name. For example, if the SQL name is dbo.Product, the Access name is dbo_Product. If that name is already in use, Access appends “1” to the new table name — for example, dbo_Product1. If dbo_Product1 is also already in use, Access will create dbo_Product2, and so on. But you can rename the tables to something more meaningful.

In an import operation, Access never updates a table in the database. Although you cannot directly append SQL Server data to an existing table, you can produce an append query to append data after you have imported data from similar tables.

In a link operation, if columns are read-only in an SQL Server table, they are also read-only in Access.

Tip  –  To see the connection string, hover over the table in the Access navigation pane.

Update the linked table design

You can’t add, delete, or modify columns or change data types in a linked table. If you aim to make design changes, do it in the SQL Server database. To view the design changes in Access, update the linked tables:

  1. Click External Data > Linked Table Manager.
  1. Choose each linked table you want to update, press OK, and then click Close.

Compare data types

Access data types are uniquely named from SQL Server data types. For example, a SQL Server column of the bit data type is imported or linked into Access with the Yes/No data type. For more information, see Comparing Access and SQL Server data types.

Advertisements

Leave a Reply

%d bloggers like this: