SlideShare a Scribd company logo
1 of 4
Download to read offline
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

More Related Content

Viewers also liked

Auronix desayuno-distribuible
Auronix desayuno-distribuibleAuronix desayuno-distribuible
Auronix desayuno-distribuibleMartha Cepeda
 
Young marketers elite 2 YM Positioning - Mai Bang - Nhat Duy
Young marketers elite 2   YM Positioning - Mai Bang - Nhat DuyYoung marketers elite 2   YM Positioning - Mai Bang - Nhat Duy
Young marketers elite 2 YM Positioning - Mai Bang - Nhat DuyMai Bằng
 
20140514 team blender_v01 (Korean)
20140514 team blender_v01 (Korean)20140514 team blender_v01 (Korean)
20140514 team blender_v01 (Korean)Dongho Kim
 
Young marketers elite 2 Brand Innovation - Van Khai - Mai Bang
Young marketers elite 2   Brand Innovation - Van Khai - Mai BangYoung marketers elite 2   Brand Innovation - Van Khai - Mai Bang
Young marketers elite 2 Brand Innovation - Van Khai - Mai BangMai Bằng
 
He thong may cham cong kiem soat cua
He thong may cham cong kiem soat cuaHe thong may cham cong kiem soat cua
He thong may cham cong kiem soat cuaLe Tin
 
He thong may cham cong kiem soat cua
He thong may cham cong kiem soat cuaHe thong may cham cong kiem soat cua
He thong may cham cong kiem soat cuaLe Tin
 
He thong may cham cong kiem soat cua
He thong may cham cong kiem soat cuaHe thong may cham cong kiem soat cua
He thong may cham cong kiem soat cuaLe Tin
 
Habilidades siglo xxi
Habilidades siglo xxiHabilidades siglo xxi
Habilidades siglo xxiklaudia
 

Viewers also liked (11)

IoT Summit Phiilppines Post Event Report
IoT Summit Phiilppines Post Event Report IoT Summit Phiilppines Post Event Report
IoT Summit Phiilppines Post Event Report
 
Auronix desayuno-distribuible
Auronix desayuno-distribuibleAuronix desayuno-distribuible
Auronix desayuno-distribuible
 
Electrical circuit
Electrical circuitElectrical circuit
Electrical circuit
 
Young marketers elite 2 YM Positioning - Mai Bang - Nhat Duy
Young marketers elite 2   YM Positioning - Mai Bang - Nhat DuyYoung marketers elite 2   YM Positioning - Mai Bang - Nhat Duy
Young marketers elite 2 YM Positioning - Mai Bang - Nhat Duy
 
20140514 team blender_v01 (Korean)
20140514 team blender_v01 (Korean)20140514 team blender_v01 (Korean)
20140514 team blender_v01 (Korean)
 
Young marketers elite 2 Brand Innovation - Van Khai - Mai Bang
Young marketers elite 2   Brand Innovation - Van Khai - Mai BangYoung marketers elite 2   Brand Innovation - Van Khai - Mai Bang
Young marketers elite 2 Brand Innovation - Van Khai - Mai Bang
 
OTC drugs ppt
OTC drugs pptOTC drugs ppt
OTC drugs ppt
 
He thong may cham cong kiem soat cua
He thong may cham cong kiem soat cuaHe thong may cham cong kiem soat cua
He thong may cham cong kiem soat cua
 
He thong may cham cong kiem soat cua
He thong may cham cong kiem soat cuaHe thong may cham cong kiem soat cua
He thong may cham cong kiem soat cua
 
He thong may cham cong kiem soat cua
He thong may cham cong kiem soat cuaHe thong may cham cong kiem soat cua
He thong may cham cong kiem soat cua
 
Habilidades siglo xxi
Habilidades siglo xxiHabilidades siglo xxi
Habilidades siglo xxi
 

Recently uploaded

OpenShift Commons Paris - Choose Your Own Observability Adventure
OpenShift Commons Paris - Choose Your Own Observability AdventureOpenShift Commons Paris - Choose Your Own Observability Adventure
OpenShift Commons Paris - Choose Your Own Observability AdventureEric D. Schabell
 
Salesforce Miami User Group Event - 1st Quarter 2024
Salesforce Miami User Group Event - 1st Quarter 2024Salesforce Miami User Group Event - 1st Quarter 2024
Salesforce Miami User Group Event - 1st Quarter 2024SkyPlanner
 
Comparing Sidecar-less Service Mesh from Cilium and Istio
Comparing Sidecar-less Service Mesh from Cilium and IstioComparing Sidecar-less Service Mesh from Cilium and Istio
Comparing Sidecar-less Service Mesh from Cilium and IstioChristian Posta
 
Valere | Digital Solutions & AI Transformation Portfolio | 2024
Valere | Digital Solutions & AI Transformation Portfolio | 2024Valere | Digital Solutions & AI Transformation Portfolio | 2024
Valere | Digital Solutions & AI Transformation Portfolio | 2024Alexander Turgeon
 
Crea il tuo assistente AI con lo Stregatto (open source python framework)
Crea il tuo assistente AI con lo Stregatto (open source python framework)Crea il tuo assistente AI con lo Stregatto (open source python framework)
Crea il tuo assistente AI con lo Stregatto (open source python framework)Commit University
 
UiPath Studio Web workshop series - Day 6
UiPath Studio Web workshop series - Day 6UiPath Studio Web workshop series - Day 6
UiPath Studio Web workshop series - Day 6DianaGray10
 
IESVE Software for Florida Code Compliance Using ASHRAE 90.1-2019
IESVE Software for Florida Code Compliance Using ASHRAE 90.1-2019IESVE Software for Florida Code Compliance Using ASHRAE 90.1-2019
IESVE Software for Florida Code Compliance Using ASHRAE 90.1-2019IES VE
 
20230202 - Introduction to tis-py
20230202 - Introduction to tis-py20230202 - Introduction to tis-py
20230202 - Introduction to tis-pyJamie (Taka) Wang
 
IEEE Computer Society’s Strategic Activities and Products including SWEBOK Guide
IEEE Computer Society’s Strategic Activities and Products including SWEBOK GuideIEEE Computer Society’s Strategic Activities and Products including SWEBOK Guide
IEEE Computer Society’s Strategic Activities and Products including SWEBOK GuideHironori Washizaki
 
VoIP Service and Marketing using Odoo and Asterisk PBX
VoIP Service and Marketing using Odoo and Asterisk PBXVoIP Service and Marketing using Odoo and Asterisk PBX
VoIP Service and Marketing using Odoo and Asterisk PBXTarek Kalaji
 
Artificial Intelligence & SEO Trends for 2024
Artificial Intelligence & SEO Trends for 2024Artificial Intelligence & SEO Trends for 2024
Artificial Intelligence & SEO Trends for 2024D Cloud Solutions
 
Computer 10: Lesson 10 - Online Crimes and Hazards
Computer 10: Lesson 10 - Online Crimes and HazardsComputer 10: Lesson 10 - Online Crimes and Hazards
Computer 10: Lesson 10 - Online Crimes and HazardsSeth Reyes
 
Bird eye's view on Camunda open source ecosystem
Bird eye's view on Camunda open source ecosystemBird eye's view on Camunda open source ecosystem
Bird eye's view on Camunda open source ecosystemAsko Soukka
 
Igniting Next Level Productivity with AI-Infused Data Integration Workflows
Igniting Next Level Productivity with AI-Infused Data Integration WorkflowsIgniting Next Level Productivity with AI-Infused Data Integration Workflows
Igniting Next Level Productivity with AI-Infused Data Integration WorkflowsSafe Software
 
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdf
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdf
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdfJamie (Taka) Wang
 
The Kubernetes Gateway API and its role in Cloud Native API Management
The Kubernetes Gateway API and its role in Cloud Native API ManagementThe Kubernetes Gateway API and its role in Cloud Native API Management
The Kubernetes Gateway API and its role in Cloud Native API ManagementNuwan Dias
 
Videogame localization & technology_ how to enhance the power of translation.pdf
Videogame localization & technology_ how to enhance the power of translation.pdfVideogame localization & technology_ how to enhance the power of translation.pdf
Videogame localization & technology_ how to enhance the power of translation.pdfinfogdgmi
 
ADOPTING WEB 3 FOR YOUR BUSINESS: A STEP-BY-STEP GUIDE
ADOPTING WEB 3 FOR YOUR BUSINESS: A STEP-BY-STEP GUIDEADOPTING WEB 3 FOR YOUR BUSINESS: A STEP-BY-STEP GUIDE
ADOPTING WEB 3 FOR YOUR BUSINESS: A STEP-BY-STEP GUIDELiveplex
 

Recently uploaded (20)

OpenShift Commons Paris - Choose Your Own Observability Adventure
OpenShift Commons Paris - Choose Your Own Observability AdventureOpenShift Commons Paris - Choose Your Own Observability Adventure
OpenShift Commons Paris - Choose Your Own Observability Adventure
 
Salesforce Miami User Group Event - 1st Quarter 2024
Salesforce Miami User Group Event - 1st Quarter 2024Salesforce Miami User Group Event - 1st Quarter 2024
Salesforce Miami User Group Event - 1st Quarter 2024
 
Comparing Sidecar-less Service Mesh from Cilium and Istio
Comparing Sidecar-less Service Mesh from Cilium and IstioComparing Sidecar-less Service Mesh from Cilium and Istio
Comparing Sidecar-less Service Mesh from Cilium and Istio
 
Valere | Digital Solutions & AI Transformation Portfolio | 2024
Valere | Digital Solutions & AI Transformation Portfolio | 2024Valere | Digital Solutions & AI Transformation Portfolio | 2024
Valere | Digital Solutions & AI Transformation Portfolio | 2024
 
Crea il tuo assistente AI con lo Stregatto (open source python framework)
Crea il tuo assistente AI con lo Stregatto (open source python framework)Crea il tuo assistente AI con lo Stregatto (open source python framework)
Crea il tuo assistente AI con lo Stregatto (open source python framework)
 
UiPath Studio Web workshop series - Day 6
UiPath Studio Web workshop series - Day 6UiPath Studio Web workshop series - Day 6
UiPath Studio Web workshop series - Day 6
 
IESVE Software for Florida Code Compliance Using ASHRAE 90.1-2019
IESVE Software for Florida Code Compliance Using ASHRAE 90.1-2019IESVE Software for Florida Code Compliance Using ASHRAE 90.1-2019
IESVE Software for Florida Code Compliance Using ASHRAE 90.1-2019
 
20230104 - machine vision
20230104 - machine vision20230104 - machine vision
20230104 - machine vision
 
20230202 - Introduction to tis-py
20230202 - Introduction to tis-py20230202 - Introduction to tis-py
20230202 - Introduction to tis-py
 
IEEE Computer Society’s Strategic Activities and Products including SWEBOK Guide
IEEE Computer Society’s Strategic Activities and Products including SWEBOK GuideIEEE Computer Society’s Strategic Activities and Products including SWEBOK Guide
IEEE Computer Society’s Strategic Activities and Products including SWEBOK Guide
 
VoIP Service and Marketing using Odoo and Asterisk PBX
VoIP Service and Marketing using Odoo and Asterisk PBXVoIP Service and Marketing using Odoo and Asterisk PBX
VoIP Service and Marketing using Odoo and Asterisk PBX
 
201610817 - edge part1
201610817 - edge part1201610817 - edge part1
201610817 - edge part1
 
Artificial Intelligence & SEO Trends for 2024
Artificial Intelligence & SEO Trends for 2024Artificial Intelligence & SEO Trends for 2024
Artificial Intelligence & SEO Trends for 2024
 
Computer 10: Lesson 10 - Online Crimes and Hazards
Computer 10: Lesson 10 - Online Crimes and HazardsComputer 10: Lesson 10 - Online Crimes and Hazards
Computer 10: Lesson 10 - Online Crimes and Hazards
 
Bird eye's view on Camunda open source ecosystem
Bird eye's view on Camunda open source ecosystemBird eye's view on Camunda open source ecosystem
Bird eye's view on Camunda open source ecosystem
 
Igniting Next Level Productivity with AI-Infused Data Integration Workflows
Igniting Next Level Productivity with AI-Infused Data Integration WorkflowsIgniting Next Level Productivity with AI-Infused Data Integration Workflows
Igniting Next Level Productivity with AI-Infused Data Integration Workflows
 
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdf
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdf
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdf
 
The Kubernetes Gateway API and its role in Cloud Native API Management
The Kubernetes Gateway API and its role in Cloud Native API ManagementThe Kubernetes Gateway API and its role in Cloud Native API Management
The Kubernetes Gateway API and its role in Cloud Native API Management
 
Videogame localization & technology_ how to enhance the power of translation.pdf
Videogame localization & technology_ how to enhance the power of translation.pdfVideogame localization & technology_ how to enhance the power of translation.pdf
Videogame localization & technology_ how to enhance the power of translation.pdf
 
ADOPTING WEB 3 FOR YOUR BUSINESS: A STEP-BY-STEP GUIDE
ADOPTING WEB 3 FOR YOUR BUSINESS: A STEP-BY-STEP GUIDEADOPTING WEB 3 FOR YOUR BUSINESS: A STEP-BY-STEP GUIDE
ADOPTING WEB 3 FOR YOUR BUSINESS: A STEP-BY-STEP GUIDE
 

Importing SQL to MS Access using ODBC

  • 1. 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.
  • 2. 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.
  • 3. 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
  • 4. 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