Link to or import data from an Azure SQL Server Database

Applies to: Access for Microsoft 365, Access 2019.

You can link to or import data from an SQL Database, which is a high-performing managed database used for mission-critical applications. For more information, see SQL Database – Cloud Database as a Service.

• After you link to data, Access forms a two-way connection that synchronises changes to data in Access and the SQL Database.

• Once you import data, Access produces a single-use, copy of the data, and so changes to data in either Access or the SQL Database are unsynchronised.

Before you begin

Make basic preparations

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

• Find the Azure SQL Server database server name, identify necessary connection information, and choose an authentication method (Windows or SQL Server). For more information on the four methods of authentication, see Connect to Server (Database Engine) and Securing your database.

• Decide which tables or views that you want to link to or import, and uniquely-valued fields for linked tables. You can link to or import more than one table or view in a single operation.

• Carefully weigh up the number of columns in each table or view. Access only supports over 255 fields in a table, so Access links or imports only the first 255 columns. As an alternative, you can design a view in the Azure SQL Server Database to access the columns beyond the limit.

• Verify the total amount of data being imported. The maximum size of an Access database is two gigabytes, minus the space needed for system objects. If the Azure SQL Server database includes large tables, you may only manage to import some of them into a single Access database. In this case, attempt linking to the data rather than importing.

• Secure your Access database and the connection information it embodies by using a trusted location and an Access database password. This is particularly significant if you decide to save the SQL Server password in Access.

• Plan for creating further relationships. Access does not instinctively forge relationships between related 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.

Configure your Azure SQL Server environment

Your organisation might previously own a Microsoft Azure account and an Azure SQL Server database that you can use. If that’s not the case, you can do the following:

1. Make an Azure account. For more information, see Create your free Azure account today.

2. If you don’t have convenient access to an Azure SQL Server database, you can design your own. For more information, see Create an Azure SQL database in the Azure portal.

Ensure you retain important information so you don’t forget it, such as credentials and account names.

Create a firewall rule

Before connecting to a Microsoft Azure SQL Database server, the database administrator must formulate server-level firewall rules. These rules set the public Internet Protocol (IP) address that allows client access for each device to the server through the Azure firewall. In your organisation, check to see what kind of public IP address you are using, either static or dynamic:

• If your public IP address is static, then it is constantly congruent. You can determine a firewall rule with a single public IP address.

• If your public IP address is dynamic, then it can evolve periodically. You must elaborate a firewall rule with a range of public IP addresses. Remember that the public IP address used by your device to connect to Azure SQL Database may vary from the public IP address displayed in your computer public IP configuration settings.

To avoid misinterpretation, we recommend using the following procedures.

1. Log into your Microsoft Azure account, and make your way to the Windows Azure portal.

2. On the Microsoft Azure SQL Databases page, select your database.

3. In the quick glance pane, click Manage allowed IP addresses, and then do one of the following: Create a firewall rule for a single device – Adopt this approach for testing and development, or in a small business environment, and once you know that your public IP address is static.

• In the Allowed IP Addresses section, choose Add to allowed IP addresses to allow your public IP address to access the database through the firewall. The Azure portal presents the correct public IP address for your client device above the rule names section.

Create a firewall rule for a range of IP Addresses – Employ this approach to grant access for several users in an on-premises environment, or once you know your public IP address is dynamic. Contact your IT department or your Internet Provider to obtain a block of public IP addresses.

a. Below RULE NAME, type a purposeful name for the rule.

b. Beneath START IP, state the beginning public IP address number of the range.

c. Below END IP, input the ending public IP address number of the range.

It could take up to five minutes for the firewall rule to be enforced. For more information, see Azure SQL Database firewall rules.

Stage 1: Get started

1. Press External Data > New Data Source > From Database > From SQL Server.

2. 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, choose Link the data source by creating a linked table.

3. Press OK.

Stage 2: Create or reuse a DSN file

You can design a DSN file or reuse an existing one. Utilise a DSN file once you want to rely on the equal connection information for various link and import operations or to share with another application that also employs DSN files. You can generate a DSN file physically by using the Data Connection Manager. For more information, see Administer ODBC data sources.

Even though you can still use older versions of the SQL ODBC driver, we advise using version 13.1, which contains multiple 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 target DSN file to use is pre-existent, pick it from the list.

According to the specific type of authentication method you stated in the connection information, you might be required to write a password again.

• To make a new DSN file:

i. Choose New.

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

iii. Input a name for the DSN file, or select Browse to generate the file in a different location.

2. Pick Next to review the summary information, and then select 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, state identification information:

• In the Description box, optionally enter documentary information about the DSN file.

• In the Server box, type the name of the Azure SQL Server Database. For example, enter “myAzureDB.database.windows.net”. Do not press the down arrow.

2. On page two, choose one of the following authentication methods:

• With Integrated Windows authentication  –  Connect through a Windows user account. Optionally, write a Service Principle name (SPN). For more information, see Service Principal Names (SPNs) in Client Connections (ODBC).

• With SQL Server authentication…  –  Connect with established credentials applied to the database by entering the login ID and password.

• With Active Directory Integrated authentication – Connect to the Azure SQL Server Database by using Azure Active Directory. Once you have configured Azure Active Directory authentication, no additional login and password is required. For more information, see Connecting to SQL Database by Using Azure Active Directory Authentication.

• With Active Directory Password authentication… – Connect with credentials that have been set up in the Azure Active Directory by inputting the login name and password. For more information, see Connecting to SQL Database by Using Azure Active Directory Authentication.

3. On pages three and four, pick differing options to customise your connection. For more information about these options, see Microsoft ODBC Driver for SQL Server.

4. A screen displays to verify your settings. Pick Test Data Source to accept your connection.

5. You might have to login 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 table or view that you want to link or import, and then pick OK.

1. In a link operation, choose whether to click Save Password. Security – Picking this option dismisses the need to input credentials each time you open Access and access the data. But, this stores an unencrypted password in the Access database, leaving your credentials exposed to any users who access the source contents. If you click this option, we strongly advise storing the Access database in a trusted location and generating 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)

• In the Get External Data – ODBC Database dialogue box, you can save the import steps as a specification and design an Outlook task to automate the import operation frequently. For more information, see Save the details of an import or export operation as a specification.

Results

After a link or import operation completes, the tables emerge in the Navigation Pane with the identical 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 produce dbo_Product2, and so on. But you can rename the tables to something more distinctive.

In an import operation, Access avoids revising a table in the database. Although you cannot directly integrate SQL Server data to an existing table, you can create an append query to insert data after you have imported data from similar tables.

In a link operation, if columns are read-only in an Azure 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 wish to apply design changes, do it in the Azure SQL Server database. To view the design changes in Access, amend the linked tables:

1. Choose External Data > Linked Table Manager.

2. Pick each linked table you want to update, press OK, and then click Close.

Compare data types

Access data types are individually named from Azure SQL Server data types. For example, an Azure 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.