Administer ODBC data sources
Applies ToAccess for Microsoft 365 Access 2024 Access 2021 Access 2019 Access 2016
Open Database Connectivity (ODBC) is a protocol that you can use to connect a Microsoft Access database to an external data source such as Microsoft SQL Server. This article contains general information about ODBC data sources, how to create them, and how to connect to them by using Microsoft Access. The procedure steps might vary depending on the specific database products and ODBC drivers used.
In this article
-
About ODBC data sources
-
Add an ODBC data source
About ODBC data sources
A data source is a source of data combined with the connection information that is required to access that data. Examples of data sources are SQL Server, Oracle RDBMS, a spreadsheet, and a text file. Examples of connection information include server location, database name, logon ID, password, and various ODBC driver options that describe how to connect to the data source. This information can be obtained from the administrator of the database to which you want to connect.
In the ODBC architecture, an application such as Access connects to the ODBC Driver Manager, which in turn uses a specific ODBC driver (for example, Microsoft SQL ODBC driver) to connect to a data source. In Access, you use ODBC data sources to connect to data sources external to Access that do not have built-in drivers.
To connect to these data sources, you must do the following:
-
Install the appropriate ODBC driver on the computer that contains the data source.
-
Define a data source name (DSN) by using either the ODBC Data Source Administrator to store the connection information in the Microsoft Windows registry or a DSN file, or a connect string in Visual Basic code to pass the connection information directly to the ODBC Driver Manager.
Machine data sources
Machine data sources store connection information in the Windows Registry on a specific computer. You can use machine data sources only on the computer they are defined on. There are two types of machine data sources — user and system. User data sources can be used only by the current user and are visible only to that user. System data sources can be used by all users on a computer and are visible to all users on the computer and system-wide services. A machine data source is especially useful when you want to provide added security, because only users who are logged on can view a machine data source and it cannot be copied by a remote user to another computer.
File data sources
File data sources (also called DSN files) store connection information in a text file, not the Windows registry, and are generally more flexible to use than machine data sources. For example you can copy a file data source to any computer that has the correct ODBC driver so that your application can rely on consistent and accurate connection information to all the computers it uses. Or you can place the file data source on a single server, share it between many computers on the network, and easily maintain the connection information in one location.
A file data source can also be unshareable. An unshareable file data source is contained on a single computer and points to a machine data source. You can use unshareable file data sources to access existing machine data sources from file data sources.
Connect strings
In a module, you can define a formatted connect string that specifies connection information. A connect string passes the connection information directly to the ODBC Driver Manager, and it helps simplify your application by removing the requirement that a system administrator or user first create a DSN before you use the database.
For more information about the ODBC interface, see the ODBC Programmer’s Reference section of MSDN.
Top of Page
Add an ODBC data source
Before proceeding, obtain and install the appropriate ODBC driver for the data source to which you want to connect.
Note: You must be a member of the Administrators group on your local machine in order to add or configure an ODBC data source.
-
Click Start, and then click Control Panel.
-
In the Control Panel, double-click Administrative Tools.
-
In the Administrative Tools dialog box, double-click Data Sources (ODBC).
The ODBC Data Source Administrator dialog box appears.
-
Click User DSN, System DSN, or File DSN, depending on the type of data source you want to add. For more information, see the section About ODBC data sources.
-
Click Add.
-
Select the driver that you want to use, and then click Finish or Next.
If the driver you want is not listed, contact the administrator of the database you are connecting to for information about how to obtain the correct driver.
-
Follow the instructions and enter the required connection information in any dialog boxes that follow.
Click Help in the ODBC dialog boxes for more information about the individual settings.
Top of Page
Need more help?
Want more options?
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
ODBC is basically a protocol generally used for connecting Microsoft Access Database with another outbound source like Microsoft SQL server. Open Database Connectivity (in short ODBC) is a driver permitting applications to access data from DBMS (Database Management Systems) with the help of SQL. So in this write-up today, we will show you a few most effective methods to start ODBC Data Sources in Windows 11 or 10.
Maximum number of interoperability is permissible by ODBC which eventually proves that; one program can retrieve various DBMS. It’s a significant Windows component and you may need to use it in order to create along with managing the ODBC data source.
Start ODBC Data Sources
Here is How to Start ODBC Data Sources in Windows 11 or 10 –
1] Use taskbar search
- Click the search icon located in the taskbar.
- Type odbc in the text bar.
- When ODBC Data Sources app for both 64-bit and 32-bit shows up in search result, click preferred one to launch.
2] Start ODBC Data Sources through Start menu
- Press Windows key to invoke the Start menu.
- Reach to Windows Administrative Tools by approaching alphabetically.
- Expand the same folder.
- From available ODBC Data Source (32-bit) and ODBC Data Source (64-bit), opt for the desired one.
3] Take help of Run dialog box
- Press Win+R at one go to directly open Run dialog box.
- Insert odbcad32 in the command box and click OK button.
Note – Whether your Windows 11 or 10 device runs 64-bit or 32-bit, the command will open the right version automatically.
4] By means of Control panel
- Press Win+S and type in control panel and hit Enter key to launch the same.
- Set View by option to Large icons and select Administrative Tools.
- When a separate window turns up, look for ODBC Data Sources.
- The app will be available in both 32 and 64 bit, hence click the one compatible with your system.
5] Access Command Prompt to open ODBC Data Sources
- Right-click on Start button and choose Run.
- Put cmd in the text field and hit Enter.
- Insert odbcad32 after the blinking cursor followed by pressing the Enter key.
6] Navigate the directory of ODBC
- Bring forth File Explorer with hotkey Win+E.
- In its address bar, insert
C:\WINDOWS\System32\odbcad32.exeand press Enter key. This will open ODBC that is compatible with 64-bit. - In the case of running 32-bit, you need to insert
C:\WINDOWS\SysWOW64\odbcad32.exedirectory instead.
7] Create a shortcut key for ODBC Data Sources
- Open File Explorer and go to the following directory –
C:\WINDOWS\System32
- After reaching there, press Ctrl+F to activate its search function.
- Type odbcad32 and press Enter key.
- When the search result appears, right-click on odbcad32.exe and choose Send to => Desktop (create shortcut).
8] With the assistance of PowerShell
- Press Win+X and select Windows PowerShell.
- Insert odbcad32.exe after the winking cursor and hit Enter.
Methods:
1] Use taskbar search
2] Open ODBC Data Sources through Start menu
3] Take help of Run dialog box
4] By means of Control panel
5] Access Command Prompt to open ODBC Data Sources
6] Navigate the directory of ODBC
7] Create a shortcut key for ODBC Data Sources
8] With the assistance of PowerShell
That’s all!!
ODBC , the short form of Open Database Connectivity is a standard Application Programming Interface. It allows applications to connect to various database systems such as Data Analytics and Reporting , Web Applications , Enterprise Systems like ERP,CRM , etc, using ODBC Drivers on Windows 11. ODBC is a programming interface that enables applications to access data in database management systems that use SQL as a data access standard. In this article, let us know more about ODBC, configuring ODBC, and more. Let’s get Started.
Installing ODBC on Windows 11 OS
Windows 11 comes with pre-installed ODBC Drivers , and hence, we don’t have to install it separately. We can access ODBC on Windows 11 and start using it. To open ODBC on your Windows 11, kindly execute the following command in the Run.
- Open the Run Command using the shortcut WinKey + R.
- In the Run, execute the following command
odbcad32
Execute the following command in the Run Command
- This command will open Open Database Connectivity. Almost all the modern systems use 64-bit architecture. By default, the above command will open ODBC Data Source Administrator (64-bit) in the latest systems. If you are using a 32-bit architecture system, then ODBC Data Source Administrator (32-bit).
ODBC Data Source Administrator running on Windows 11
- I am using a system that is based on 64-bit architecture. If you are not sure about your system architecture, then we have a detailed article explaining how to get to know about the System Architecture on Windows 11.
Download ODBC Drivers for Various Resources and Platforms
You can download various ODBC Drivers such as Microsoft SQL, MySQL, PostgreSQL, Oracle ODBC Driver, etc, from the official Microsoft Website. You can also install ODBC Driver for SQL Server for other Platforms such as Linux and macOS.
Various Options on ODBC Data Source Administrator on Windows 11
Let’s explore all the options on the ODBC Data Source Administrator on Windows 11 one by one.
User DSN
- The first Tab in the ODBC Data Source is the User DSN. User Data Source Name is a configuration that is unique to a single user account on Windows 11. An ODBC User Data Source stores information about how to connect to the indicated data provider. This User Data Source will only be visible to you and can only be used in the respective computer. In my Windows 11, I can see several ODBC Drivers dBASE Files , Excel Files , MS Access Database. These files are used for connecting legacy dBase databases, Microsoft Excel Spreadsheets, and MS Access Databases.
User DSN Drivers
- You can Add or Remove User DSN in ODBC here.
Click Add
Let’s add an ODBC SQL Server.
Install ODBC SQL Server
- Enter all the information here. Server Name, Description, Location of the SQL Server, Click Next.
Enter the ODBC SQL Server information
- In the next step, you choose the type of authentication. Whether you want o use a NT Authentication, or SQL Server Authentication, you can choose it here. Click Next to continue.
Choose the Authentication Type
- Here, we can configure several options such as Change the Default database, ANSI Quoted Identifiers , ANSI Pulls , Paddings, Warnings, and more.Click Next.
ANSI Information
- You can configure more options such as Use Strong Encryption for Data, Save Logs, ODBC Logging, and more. Finally, Click Finish to create the new ODBC SQL Server.
Click Finish
- ODBC SQL Driver is created now. The system will show the Driver Summary. Click OK to complete the process.
ODBC Microsoft SQL Server Setup Summary
Let’s move to the next Tab.
System DSN
In System DSN, you can find information about System Data Sources. You can also add System DSN ODBC Drivers by clicking on the Add Button.
System DSN Data Sources
File DSN
The next tab in the ODBC Data Source Administrator is File DSN. An ODBC File Data Source Allows you to connect to a Data Provider. These Files Data Source Name can be shared by users who have the same drivers installed. Set Directory will allows users to set the default file DSN Directory.
Exploring File DSN and setting the default File DSN Directory
Drivers
In this section, you will get information about the ODBC-Enabled Programs to get information from ODBC Data Sources. For this article, I have installed SQL Server to the User DSN, which we can find it here in the Drivers Tab.
Information about ODBC Drivers Installed
Tracing
ODBC Tracing allows users to create logs of the calls to ODBC Drivers for use by support personnel or to aid you in debugging your applications. Click Start Tracing Now to start the tracing process. If you want to do the Tracing for All User Identities, the enable the checkbox “ Machine-wide tracing for all user identities “. By default, the system uses custom trace Dynamic Link Library as odbctrac.dll. If you want to choose a different DLL, then you can click on Select DLL and choose accordingly.
Tracing tab in the ODBC Data Sources
Connection Pooling
Connection Pooling allows an application to reuse open connection handles, which will help save round-trips to he server. If you want Performance Monitor to track and analyze ODBC connection pooling behaviour, then you can enable the checkbox of “ Enable ODBC Connection Pooling PerfMon counters for troubleshooting “.
Connection Pooling
About Tab
Finally, the About Tab of the ODBC Data Sources Administrator. Here you can find information the ODBC Core Components such as Administrator Version , Control Panel Startup Version , Cursor Library , Driver Manager , Localised Resouce DLL , Unicode Cursor Library , and their corresponding DLL file locations.
Information about the ODBC Core Components
Take Away:
ODBC on Windows 11 is a crucial interface that allows seamless communication between applications and databases and features like Custom Trace DLL , Connection Pooling , Performance Monitoring , etc will enhance troubleshooting and optimzation. Open Database Connectivity can be reliable and essential tool for Database Connectivity on Windows 11. If you have any queries , kindly let us know in the comment section. For more interesting articles, stay tuned to Winsides.com. Happy Computing! Peace Out!
The Microsoft ODBC Data Sources tool helps you connect your apps to various database management systems. But how does this tool work, and what are the different ways to access it? Let’s dive in and find out.
How Does the ODBC Data Sources App Work?
ODBC (Open Database Connectivity) is a system that connects apps to database management systems. For example, if you use an app that accesses data in an SQL database, the ODBC Data Sources tool lets you connect that app to a different data source (such as Microsoft Excel).
Let’s now check out how you can use the ODBC Data Sources tool:
- Press Win + R to open the Run command dialog box.
-
Type odbcad32.exe and press Enter.
- Navigate to the User DSN tab.
A DSN establishes a connection to a target database through an ODBC driver. It can also help you pull information from a database.
-
Click the Add button on the right to start creating a User DSN.
- On the next screen, select the relevant ODBC driver and click the Finish button. From there, enter the details of your data source in the next window.
- Follow the on-screen steps and then click the Finish button.
Once you’ve created a User DSN, you can explore some cool tricks with the ODBC tool. For example, you can check out how to connect your databases with VBScript by creating a VBScript ODBC Connection.
Want to discover the various ways to access the ODBC Data Sources tool? Let’s help you out!
One of the easiest ways to access the ODBC Data Sources tool (or any other app) is by using the Start menu search bar.
Here are the steps you need to apply:
- Press Win + S to access the Start menu search bar. Alternatively, Press the Windows search bar icon on the taskbar.
-
Type ODBC Data Sources and select the Best match.
You can also easily access the ODBC Data Sources tool from the Start menu. So, let’s check out the steps you need to apply:
- Press the Windows key or click the Start Menu icon on the taskbar to access the Start menu.
- Select the Windows Administrative Tools folder from the menu items.
-
Locate and select one of the ODBC Data Sources options.
3. Access the ODBC Data Sources App Using the Control Panel
The Control Panel is a handy tool that you can use for various purposes. For example, you can use it to troubleshoot system issues or configure some settings.
Interestingly, the Control Panel can also help you open most apps on your PC. So, let’s check out how you can use it to access the ODBC Data Sources app:
- Press Win + R to open the Run command dialog box.
- Type Control Panel and press Enter.
- Click the View by drop-down menu and select Large icons.
- Select Administrative Tools from the options.
-
Double-click on one of the ODBC Data Sources options.
4. Access the ODBC Data Sources App Using the File Explorer Address Bar
The File Explorer address bar is a convenient tool that helps you locate your file or folder paths. But did you know that you can use this tool to access various apps?
Here’s how you can access the ODBC Data Sources app using the File Explorer address bar:
- Press Win + E to open File Explorer.
-
Type odbcad32.exe in the address bar and press Enter.
5. Access the ODBC Data Sources App Using the Task Manager
The Task Manager is usually the first tool you might think of whenever you want to close faulty apps on your PC. But did you know that this tool can also help you access almost any app on Windows?
Here’s how you can open the ODBC Data Sources app using the Task Manager:
- Type Task Manager in the Start menu search bar and select the Best match.
- Click the File tab in the top-left corner of the screen.
- Select the Run new task option.
-
Type odbcad32.exe in the search box and press OK to open the ODBC Data Sources app.
6. Access the ODBC Data Sources App From the Windows System32 Directory
You can also open the ODBC Data Sources app by clicking the relevant file in the Windows System32 folder.
Here are the steps you need to apply:
- Press Win + E to open File Explorer. Alternatively, click the File Explorer icon on the taskbar.
- Select This PC on the left.
- Select the Local Disk (C:) option on the right.
- Open the Windows folder, scroll down, and open the System32 folder.
-
Double-click on the odbcad32 option to open the ODBC Data Sources app.
7. Access the ODBC Data Sources App Using the Command Prompt or PowerShell
The Command Prompt and PowerShell can also help you access the ODBC Data Sources app.
Let’s start by checking out the steps for opening the ODBC Data Sources app using the Command Prompt:
- Press Win + R to open the Run command dialog box.
- Type CMD and press Ctrl + Shift + Enter to open an elevated Command Prompt window.
- Type the following command and press Enter.
odbcad32.exe
Let’s now check out the steps for accessing the ODBC Data Sources app using PowerShell:
- Type Windows PowerShell in the Start menu search bar.
- Right-click on the Best match result and select Run as administrator.
- Type the following command and press Enter.
odbcad32.exe
8. Access the ODBC Data Sources App Using a Desktop Shortcut
Using desktop shortcuts is one of the best ways to access your apps. So, let’s explore how you can create an ODBC Data Sources desktop shortcut:
- Press Win + D to access the desktop.
- Right-click on a blank space and select New > Shortcut.
-
Type %windir%\system32\odbcad32.exe in the location box and press Next to continue.
Type ODBC Data Sources App in the «name» box and then click the Finish button. Now, you can easily access the ODBC Data Sources app using the desktop shortcut you’ve just created.
Accessing the Windows ODBC Data Sources App Is Quite Easy
Need a Windows tool that can help you connect your apps to various database management systems? Try the ODBC Data sources app! The good news is that this tool is free and easy to use.
And how do you access the Microsoft ODBC Data sources tool? Simply apply any of the methods we’ve covered. From there, you can start exploring some cool «database creation» tricks, such as creating a database in Microsoft Access.
ODBC, or Open Database Connectivity, is a standardized way for outside programs to connect to database management systems. In this article, we will show you how to set up an ODBC data source connection to a Microsoft SQL Server instance on Windows.
Opening the Program
The first step is to find and open the ODBC Data Sources program in Windows. This can be found in the Control Panel under Administrative Tools or under C:ProgramDataMicrosoftWindowsStart MenuProgramsAdministrative Tools. In the screenshot below, you’ll notice that two versions of the program exist, one for 32-bit and one for 64-bit. The choice of program you’ll want to use is the one that matches the application you’re trying to connect to the database. For example, if you have a 32-bit version of excel, you’ll want to use the 32-bit ODBC Data Source.
Selecting a DSN Type
After opening the program, you will see a few tabs with the name DSN (Data Source Name):
User DSN: An ODBC data source for the specific windows user account. Other users will not be able to see the connection.
System DSN: An ODBC data source that is system-wide and can be leveraged by any user with the proper rights.
File DSN: An ODBC data source that is set up using files with a .DSN file extension.
In this example, we will focus on setting up a new System DSN. To do this, click on the System DSN tab and then click on the Add button.
Choosing a Driver
After clicking the Add button, you will be prompted to pick a driver to set up your data source. Drivers allow the system to translate SQL and database functions from the application you are using to the database management system you are trying to connect to. If you do not see the driver you are looking for, don’t fret, while the drivers for each database don’t come pre-installed on your system, they can often be downloaded from the website associated with each database for free. In this case, we will be using the SQL Server driver to connect to our Microsoft SQL Server instance. Click “Finish” to begin setting up the connection.
Note: Depending on the driver chosen, the next steps may look different, but will require similar information.
Configuring the Connection
On this screen, you’ll first want to give your new DSN a meaningful name, this will be the name you use to reference the connection in your applications. There is also space to put a description of the driver, which can be used to add more details about the connection. Lastly, you’ll enter the address of the SQL server you are trying to connect to. In this case, it’s the SQLEXPRESS SQL Server instance that’s located on the Windows server with IP address 10.3.0.4. Click “Next” to continue.
The next step involves setting up the way the ODBC will authenticate your connection to the SQL Server. For Microsoft SQL Server, you can choose to use Windows authentication or the user authentication native to Microsoft SQL Server. If you’ve chosen SQL Server authentication, which has been selected in the screenshot below, it will want you to enter a specific login here. This will not be the only login you can use to connect to the server with the ODBC connection, but it will be used to test the connection and to automatically obtain some default settings for the remainder of the ODBC configuration, if the box is checked. You’ll also notice the Client Configuration button, the functionality of this is described in the next section. Once everything is set, click “Next” to continue.
By clicking on Client Configuration in the authentication section, you will be brought to the screen below. This screen allows you to choose an alias for your server, as well as the port number and communication library being used.
After you finish setting up authentication for your connection, you will land on this screen. Your first option here is to change the default database for the connection to the SQL server. This determines which group of tables the system will try to query when you don’t reference the database schema in your query. If there is a specific database on the SQL Server your application will be leveraging, you’ll want to pick it as your default. The second option allows you to attach a detached database to the database you’ll be connecting to, but this is not always best practice to use. In addition to those options, the first two radio buttons allow you to choose whether the connection adheres to ANSI conventions and the third option will allow a mirror SQL Server to be used as a failover. Click “Next” to continue.
Lastly, you’ll want to pick some final options for the connection. Here you can choose language, translation, data encryption, and logging options. Strong encryption is highly suggested if you are connecting from outside a safe network. After you have selected the options you want, you can click “Finish”.
Testing 1, 2, 3
Once you click finish, you will be shown a prompt with the information that you have filled in regarding the database connection. If anything looks wrong, you can click cancel and change your selections. Otherwise, it is suggested that you try “Test Data Source…”. This will attempt to connect to the database using the login you had provided earlier.
After clicking the Test Data Source button and seeing “TESTS COMPLETED SUCCESSFULLY”, congratulations, you have successfully set up an ODBC Data Source connection!
The new ODBC connection, now be seen among the other connections:
