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 Server 2016.
-
When you link to data, Access creates a two-way connection that synchronizes changes to data in Access and the SQL Database.
-
When you import data, Access creates a one-time, copy of the data, and so changes to data in either Access or the SQL Database are not synchronized.
Before you begin
Want things to go smoother? Then make the following preparations before you link or import:
-
Locate the SQL Server database server name, identify necessary connection information, and choose an authentication method (Windows or SQL Server). For more information on the methods of authentication, see Connect to Server (Database Engine) and Securing your database.
-
Identify 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 more than one table or view in a single operation.
-
Consider the number of columns in each table or view. Access does not support more than 255 fields in a table, so Access links or imports only the first 255 columns. As a workaround, you can create a view in the SQL Server Database to access the columns beyond the limit.
-
Determine 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 SQL Server database contains large tables, you might not be able to import them all into a single Access database. In this case, consider linking to the data instead of importing.
-
Secure your Access database and the connection information it contains by using a trusted location and an Access database password. This is especially important if you choose to save the SQL Server password in Access.
-
Plan for making additional relationships. Access does not automatically create relationships between related tables at the end of an import operation. You can manually create the relationships between new and existing 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
-
Select External Data > New Data Source > From Database > From SQL Server.
-
In the Get External Data – ODBC Database dialog box, do one of the following:
-
To import data, select Import the source data into a new table in the current database.
-
To link to data, select Link the data source by creating a linked table.
-
-
Select OK.
Stage 2: Create or reuse a DSN file
You can create a DSN file or reuse an existing one. Use a DSN file when you want to rely on the same connection information for different link and import operations or to share with a different application that also uses DSN files. You can create a DSN file directly by using the Data Connection Manager. For more information, see Administer ODBC data sources.
Although you can still use prior versions of the SQL ODBC driver, we recommend using version 13.1, which has many improvements, and supports new SQL Server 2016 features. For more information, see Microsoft ODBC Driver for SQL Server on Windows.
-
Do one of the following:
-
If the DSN file you want to use already exists, select it from the list.
Depending on which authentication method you entered in the connection information, you may need to enter a password again.
-
To create a new DSN file:
-
Select New.
-
Select ODBC Driver 13 for SQL Server, and then select Next.
-
Enter a name for the DSN file, or click Browse to create the file in a different location.
-
-
-
Click Next to review the summary information, and then click 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:
-
On page one, enter identification information:
-
In the Description box, optionally enter documentary information about the DSN file.
-
In the Server box, enter the name of the SQL Server. Do not click the down arrow.
-
-
On page two, select one of the following authentication methods:
-
With Integrated Windows authentication Connect through a Windows user account. Optionally, enter a Service Principle name (SPN). For more information, see Service Principal Names (SPNs) in Client Connections (ODBC).
-
With SQL Server authentication… Connect with credentials that have been set up in the database by entering the login ID and password.
-
-
On pages three and four, select various options to customize your connection. For more information about these options, see Microsoft ODBC Driver for SQL Server.
-
A screen appears to confirm your settings. Select Test Data Source to confirm your connection.
-
You may need to login to the database. In the SQL Server Login dialog box, enter the login ID and password. To change additional settings, select Options.
Stage 4: Select tables and views to link to or import
-
In the Link Tables or Import Objects dialog box, under Tables, select each table or view that you want to link or import, and then click OK.
-
In a link operation, decide whether to select Save Password.
Security Selecting this option eliminates the need to enter credentials each time you open Access and access the data. But, this stores an unencrypted password in the Access database, which means people who can access the source contents can see the user name and password. If you select this option, we strongly recommend storing the Access database in a trusted location and creating 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 dialog box, you can save the import steps as a specification and create an Outlook task to automate the import operation on a regular basis. For more information, see Save the details of an import or export operation as a specification.
Results
When a link or import operation completes, the tables appear 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 overwrites a table in the database. Although you cannot directly append SQL Server data to an existing table, you can create 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 want to make design changes, do it in the SQL Server database. To see the design changes in Access, update the linked tables:
-
Select External Data > Linked Table Manager.
-
Select each linked table you want to update, select OK, and then select Close.
Compare data types
Access data types are differently 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.
You can work with data stored in SQL Server either by linking to it or importing the data into an Access database. Linking is a better option if you share the data with others because the data is stored in a centralized location and you can view the most current data, add or edit the data, and run queries or reports in Access.
Step 1: Preparation for linking
-
Locate the SQL Server database that you want to link to. If necessary, contact the database administrator for connection information.
-
Identify the tables and views you'll be linking to in the SQL database. You can link to multiple objects at a time.
Review the source data for the following considerations:
-
Access supports up to 255 fields (columns) in a table, so the linked table will include only the first 255 fields of the object you link to.
-
The columns that are read-only in a SQL Server table will also be read-only in Access.
-
To create the linked table in a new database: Click File > New >Blank desktop database. To create the linked tables in an existing Access database, make sure that you have the necessary permissions to add data to the database.
Note: A linked table created in an existing Access database, gets the same name as in the source object. So, if you already have another table with the same name, the new linked table name has a "1" added to it — for example, Contacts1. (If Contacts1 is also already in use, Access will create Contacts2, and so on.)
Step 2: Linking to data
When linking to a table or view in a SQL Server database, Access creates a new table (known as a linked table) that reflects the structure and contents of the source table. You can change the data either in SQL Server, or in Datasheet view or Form view from Access and the changes are reflected in both SQL and Access. Any structural changes to linked tables like removing or changing columns, have to be made from the SQL Server and not Access.
-
Open the destination Access database.
-
On the External Data tab, click ODBC Database.
-
Click Link to the data source by creating a linked table > OK and follow the steps in the wizard.In the Select Data Source box, if the .dsn file you want to use already exists, click the file in the list.
To create a new .dsn file:
In the Select Data Source box, click New> SQL Server > Next.
-
Type a name for the .dsn file, or click Browse.
Note: You need write permissions to the folder to save the .dsn file.
-
Click Next to review the summary information, and click Finish.
Follow the steps in the Create a New Data Source to SQL Server Wizard.
-
-
Click OK and under Tables, click each table or view that you want to link to, and then click OK.
If you see the Select Unique Record Identifier, it means that Access was unable to determine which field or fields uniquely identify each row of the source data. Just select the field or combination of fields that is unique for each row, and if you are not sure, check with the SQL Server database administrator.
When the linking operation is complete, you can see the new linked table or tables in the Navigation Pane.
Apply the latest SQL Server object structure
When you open either a linked table or the source object, you see the latest data. However, if any structural changes are made to a SQL Server object, you'll need to update the linked table(s) to see those changes.
-
Right-click the table in the Navigation Pane, and then click Linked Table Manager on the shortcut menu.
-
Select the check box next to each linked table that you want to update, or click Select All to select all of the linked tables.
-
Click OK > Close.
Note: Since Access data types differ from SQL Server data types, Access links to the most appropriate data type for each column. You can only review not change the assigned data types in Access.
For more information, see ways to share an Access desktop database.
If your department or workgroup uses Microsoft SQL Server to store data, you might have to work with some SQL Server data in Access.
You can bring data from SQL Server objects (tables or views) into Access in either of two ways — by importing, or by linking. The difference between the two processes is as follows:
-
When you import the data, Access creates a copy of the SQL Server data and any later changes that are made to the data in your Access database are not reflected in the SQL Server database. Likewise, any later changes made in the SQL Server table or view are not reflected in Access.
-
When you link to the SQL Server data, you are connecting directly to the source data so any later changes that are made to data in Access are reflected in the SQL Server, and vice versa.
This article describes how to either import or link to SQL Server data.
Decide whether to import or to link
Situations when importing is suitable
Typically, you import SQL Server data to an Access database for these reasons:
-
To permanently move SQL Server data to an Access database because you no longer need the data in the SQL Server database. After you import the data into Access, you can delete the data from the SQL Server database.
-
Your department or workgroup uses Access, but you are occasionally pointed to a SQL Server database for additional data that must be merged into one of your Access databases.
Since importing SQL Server data creates a copy of the data in your Access database, during the import process, you specify the tables or views that you want copied.
Situations when linking is suitable
Typically, you link to SQL Server data for these reasons:
-
To connect directly to the source data to be able to view and edit the latest information both in the SQL Server database and in your Access database.
-
The SQL Server database contains many large tables, and you are not be able to import them all into a single .accdb file. The maximum size of an Access database is 2 gigabytes, minus the space needed for system objects.
-
You want to run queries and generate reports based on data from the SQL Server without making a copy of the data, consider linking to the SQL Server.
-
Your department or workgroup uses Access for reporting and querying and uses SQL Server for data storage. Individual teams can create SQL Server tables and views for centralized storage, but often this data must be brought into desktop programs for aggregation and reporting. Linking is the appropriate choice, because it allows users of both the SQL Server database and the Access database to add and update data, and to always view and work with the latest data.
-
You are an Access user who recently started using SQL Server. You migrated several of your databases to SQL Server, and most of the tables in these databases are linked tables. From now on, instead of creating Access tables, you will create tables and views in SQL Server and then link to them from your Access databases.
-
You want to continue storing your data in SQL Server, but you also want to work with the most recent data inside Access in order to run queries and print reports that you designed in Access.
Import data from SQL Server
Prepare to import
During the import operation, Access creates a table and then copies the data from the SQL Server database into that table. At the end of the import operation, you can choose to save the details of the import operation as a specification.
Note: An import specification helps you to repeat the import operation in the future without having to step through the Import Wizard each time.
-
Locate the SQL Server database that contains the data that you want import. Contact the administrator of the database for connection information.
-
Identify the tables or views that you want to import. You can import multiple objects in a single import operation.
-
Review the source data and keep the following considerations in mind:
-
Access does not support more than 255 fields in a table, so Access imports only the first 255 columns.
-
The maximum size of an Access database is 2 gigabytes, minus the space needed for system objects. If the SQL Server database contains many large tables, you might not be able to import them all into a single .accdb file. In this case, you might want to consider linking the data to your Access database instead.
-
Access does not automatically create relationships between related tables at the end of an import operation. You must manually create the relationships between the various new and existing tables by using the options in the Relationships window. To display the Relationships window:
-
Click Database Tools > Relationships.
-
-
-
Identify the Access database into which you want to import the SQL Server data.
Ensure that you have the necessary permissions to add data to the Access database. If you don't want to store the data in any of your existing databases, create a new blank database.
-
Review the tables, if any exist, in the Access database.
The import operation creates a table with the same name as the SQL Server object. If that name is already in use, Access appends "1" to the new table name — for example, Contacts1. (If Contacts1 is also already in use, Access will create Contacts2, and so on.)
Note: Access never overwrites a table in the database as part of an import operation, and you cannot append SQL Server data to an existing table.
Import the data
-
Open the destination database.
On the External Data tab, in the Import & Link group, click ODBC Database.
-
Click Import the source data into a new table in the current database, and then click OK.
-
In the Select Data Source dialog box, if the .dsn file that you want to use already exists, click the file in the list.
I need to create a new .dsn file
Note: The steps in this procedure might vary slightly for you, depending on the software that is installed on your computer.
-
Click New to create a new data source name (DSN).
The Create New Data Source Wizard starts.
-
In the wizard, select SQL Server in the list of drivers, and then click Next.
-
Type a name for the .dsn file, or click Browse to save the file to a different location.
Note: You must have write permissions to the folder to save the .dsn file.
-
Click Next, review the summary information, and then click Finish to complete the wizard.
Create a New Data Source to SQL Server dialog box appears.
-
Type a description of the data source in the Description box. This step is optional.
-
Under Which SQL Server do you want to connect to, in the Server box, type or select the name of the SQL Server to which you want to connect, and then click Next to continue.
-
You might require information from the SQL Server database administrator, such as whether to use Microsoft Windows NT authentication or SQL Server authentication. Click Next to continue.
-
If you want to connect to a specific database, ensure that the Change the default database to check box is selected. Then select the database that you want to work with, and then click Next.
-
Click Finish.
-
Review the summary information and click Test Data Source.
-
Review the test results, and then click OK to close the dialog box.
If the test was successful, click OK again, or click Cancel to change your settings.
-
-
Click OK to close the Select Data Source dialog box.
Access displays the Import Objects dialog box.
-
Under Tables, click each table or view that you want to import, and then click OK.
-
If the Select Unique Record Identifier dialog box appears, Access was unable to determine which field or fields uniquely identify each row of a particular object. In this case, select the field or combination of fields that is unique for each row, and then click OK. If you are not sure, check with the SQL Server database administrator.
Access imports the data. If you plan to repeat the import operation later, you can save the import steps as an import specification and easily rerun the same import steps later. You require Microsoft Office Outlook installed on your computer to create a task.
-
Click Close under Save Import Steps in the Get External Data - ODBC Database dialog box. Access finishes the import operation and displays the new table or tables in the Navigation Pane.
If you want to save the import as a task for reuse, continue to the next section.
Link to SQL Server data
Since data is stored in tables, when you link to a table or view in a SQL Server database, Access creates a new table (often known as a linked table) that reflects the structure and contents of the source object. You can change data either in SQL Server, or in Datasheet view or Form view from Access. The changes that you make to data in one location are reflected in the other. However, if you want to make structural changes, such as removing or changing a column, you must do so from the SQL Server database, or from an Access project that is connected to that database. You cannot add, delete, or change the fields in a linked table while you are working in Access.
Prepare to link
-
Locate the SQL Server database that has the data to which you want to link. Contact the database administrator for connection information.
-
Identify the tables and views to which you want to link. You can link to multiple objects in a single linking operation.
-
Review the source data and keep the following considerations in mind:
-
Access does not support more than 255 fields in a table, so the linked table will include only the first 255 fields of the object you link to.
-
The columns that are read-only in a SQL Server object will continue to be read-only in Access.
-
You will not be able to add, delete, or modify columns in the linked table in Access.
-
-
Identify the Access database in which you want to create the linked tables. Ensure that you have the necessary permissions to add data to the database. If you don't want to store the data in any of your existing databases, create a new, blank database by clicking the Filetab, and then on the New tab, clicking Blank Database.
-
Review the tables in the Access database. When you link to a SQL Server table or view, Access creates a linked table with the same name as the source object. If that name is already in use, Access will append "1" to the new linked table name — for example, Contacts1. (If Contacts1 is also already in use, Access will create Contacts2, and so on.)
-
To link to the data, open the destination database.
-
On the External Data tab, in the Import & Link group, click ODBC Database.
-
Click Link to the data source by creating a linked table, and then click OK.
-
In the Select Data Source dialog box, click the .dsn file that you want to use, or click New to create a new data source name (DSN).
-
In the Select Data Source dialog box, if the .dsn file you want to use already exists, click the file in the list.
I need to create a new .dsn file
Note: The steps in this procedure might vary slightly for you, depending on the software that is installed on your computer.
-
Click New to create a new data source name (DSN).
The Create New Data Source Wizard starts.
-
In the wizard, select SQL Server in the list of drivers, and then click Next.
-
Type a name for the .dsn file, or click Browse to save the file to a different location.
Note: You must have write permissions to the folder to save the .dsn file.
-
Click Next, review the summary information, and then click Finish to complete the Create New Data Source Wizard.
The Create a New Data Source to SQL Server Wizard starts.
-
In the wizard, type a description of the data source in the Description box. This step is optional.
-
Under Which SQL Server do you want to connect to, in the Server box, type or select the name of the SQL Server computer to which you want to connect, and then click Next to continue.
-
On this page of the wizard, you might need to get information from the SQL Server database administrator, such as whether to use Windows NT authentication or SQL Server authentication. Click Next to continue.
-
On the next page of the wizard, you might need to get more information from the SQL Server database administrator. If you want to connect to a specific database, ensure that the Change the default database to check box is selected, select the SQL Server database that you want to work with, and then click Next.
-
Click Finish. Review the summary information, and then click Test Data Source.
-
Review the test results, and then click OK to close the SQL Server ODBC Data Source Test dialog box.
If the test was successful, click OK again to complete the wizard, or click Cancel to return to the wizard and change your settings.
-
-
Click OK.
Access displays the Link Tables dialog box.
-
Under Tables, click each table or view that you want to link to, and then click OK.
-
If the Select Unique Record Identifier dialog box appears, Access was unable to determine which field or fields uniquely identify each row of the source data. In this case, select the field or combination of fields that is unique for each row, and then click OK. If you are not sure, check with the SQL Server database administrator.
-
Access finishes the linking operation and displays the new linked table or tables in the Navigation Pane.
Important: Each time you open either a linked table or the source object, you see the latest data displayed in it. However, structural changes made to a SQL Server object are not automatically reflected in a linked table.
Update a linked table by applying the latest SQL Server object structure
-
Right-click the table in the Navigation Pane, and then click Linked Table Manager on the shortcut menu.
-
Select the check box next to each linked table that you want to update, or click Select All to select all of the linked tables.
-
Click OK.
If the update is successful, Access displays a message to that effect. Otherwise, Access displays an error message.
-
Click Close to close the Linked Table Manager.
What else should I know?
-
For information on how to save the details of your import into a specification that you can reuse later, see the article Save the details of an import or export operation as a specification.
-
For information on how to run saved import specifications, see the article Run a saved import or export operation.
-
For information on how to schedule specifications to run at specific times, see the article Schedule an import or export operation.
-
For information on how to change a specification name, delete specifications, or update the names of source files in specifications, see the article Manage Data Tasks.
How Access sees SQL Server data types
Because Access data types differ from SQL Server data types, Access must determine the most appropriate Access data type to use for each column of each SQL Server table or view that you import or link to. For example, a SQL Server column of the data type bit is imported or linked into Access with the data type Yes/No. Another example, a SQL Server column of the data type nvarchar(255) (or smaller) is imported or linked with the data type Text, but a column of the data type nvarchar(256) (or larger) is imported as an Access Memo field. After completing an import or linking operation, you can open the table in Design view and confirm which data types Access assigned to its fields. You can change data types of fields in imported tables. However, you cannot change data types of fields in linked tables, except by changing them in the SQL Server database itself or in an Access project that is connected to that database.
The following table lists the main SQL Server data types. The second and third columns show how Access interprets each type.
| SQL Server data type | Access data type | Access field size |
|---|---|---|
| bigint | Text | 255 |
| binary( field size ) | Binary | Same as SQL Server field size |
| bit | Yes/No | |
| char( field size ), where field size is less than or equal to 255 | Text | Same as SQL Server field size |
| char( field size ), where field size is greater than 255 | Memo | |
| datetime | Date/Time | |
| decimal( precision , scale ) | Number | Decimal (Access Precision and Scale properties match SQL Server precision and scale.) |
| float | Number | Double |
| image | OLE Object | |
| int | Number | Long Integer |
| money | Currency | |
| nchar( field size ), where field size is less than or equal to 255 | Text | Same as SQL Server field size |
| nchar( field size ), where field size is greater than 255 | Memo | |
| ntext | Memo | |
| numeric( precision , scale ) | Number | Decimal (Access Precision and Scale properties match SQL Server precision and scale.) |
| nvarchar( field size ), where field size is less than or equal to 255 | Text | Same as SQL Server field size |
| nvarchar( field size ), where field size is greater than 255 | Memo | |
| nvarchar(MAX) | Memo | |
| real | Number | Single |
| smalldatetime | Date/Time | |
| smallint | Number | Integer |
| smallmoney | Currency | |
| sql_variant | Text | 255 |
| text | Memo | |
| timestamp | Binary | 8 |
| tinyint | Number | Byte |
| uniqueidentifier | Number | Replication ID |
| varbinary | Binary | Same as SQL Server field size |
| varbinary(MAX) | OLE Object | |
| varchar( field size ), where field size is less than or equal to 255 | Text | Same as SQL Server field size |
| varchar( field size), where field size is greater than 255 | Memo | |
| varchar(MAX) | Memo | |
| xml | Memo |
No comments:
Post a Comment