Importing MS SQL Data to MS Access using ODBC Step-by-Step guide powered by Microsoft Office Support and Accede Holdings Pty. Ltd.

Programmers consider this option because of these two reasons:

1. You are thinking of permanently moving your SQL Server data to an Access database because

you no longer need the data in your SQL Server database. To do so, you may import the data

into Access and then delete it from the SQL Server database.

2. Your department or workgroup uses Access, but always pointed to a SQL Server database for

additional data. Therefore, the next option is to merge SQL data into one of your Access

databases.

Whatever your reason is, this guide will help you import SQL MS Access the easiest way possible.

First, prepare the SQL data that your want to import:

1. Locate the SQL Server database that contains the data that you want to copy. Contact the

administrator of the database for connection information.

2. Identify the tables or views that you want to copy to the Access database. You can import

multiple objects in a single import operation.

3. Review the source data and keep the following considerations in mind:

o Access does not support more than 255 fields in a table, so Access imports only the first

255 columns.

o 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.

o 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 on the Relationships tab. To display the

Relationships tab:

On the Database Tools tab, in the Show/Hide group, click Relationships.

4. 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 blank database by clicking the Microsoft Office

Button , and then clicking New.

5. 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.

When it is done perfectly, move into the Import stage:

Import the data

1. Open the destination database.

On the External Data tab, in the Import group, click More.

2. Click ODBC Database .

3. Click Import the source data into a new table in the current database, and then click OK.

4. 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.

a. Click New to create a new data source name (DSN).

The Create New Data Source Wizard starts.

b. In the wizard, select SQL Server in the list of drivers, and then click Next.

c. 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.

d. Click Next, review the summary information, and then click Finish to complete the

wizard.

The Create a New Data Source to SQL Server Wizard starts.

e. In the wizard, type a description of the data source in the Description box. This step is

optional.

f. 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.

g. On this page of the wizard, you might need to get information from the SQL Server

database administrator, such as determining whether to use Microsoft Windows NT

authentication or SQL Server authentication. Click Next to continue.

h. On the next page of the wizard, you might need to get more information from the SQL

Server database administrator before proceeding. 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.

i. Click Finish. Review the summary information, and then click Test Data Source.

j. 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 make changes to your settings.

5. Click OK to close the Select Data Source dialog box.

Access displays the Import Objects dialog box.

6. Under Tables, click each table or view that you want to import, and then click OK.

7. 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. Go to the next section of this

article to complete that task. If you do not want to save the details of the import specification, click

Close under Save Import Steps in the Get External Data - ODBC Database dialog box. Access completes

the import operation and displays the new table or tables in the Navigation Pane.

Save the import steps as a specification

1. Under Save Import Steps in the Get External Data - ODBC Database dialog box, select the Save

import steps check box.

A set of additional controls appears.

2. In the Save as box, type a name for the import specification.

3. Type a description in the Description box. This step is optional.

4. If you want to perform the operation at fixed intervals (such as weekly or monthly), select the

Create Outlook Task check box. This creates a task in Microsoft Office Outlook 2007 that lets

you run the specification.

5. Click Save Import.

Source:

https://support.office.com/en-au/article/Import-or-link-to-SQL-Server-data-a5a3b4eb-57b9-45a0-b732-

77bc6089b84e