Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010 Access 2007.
Open Database Connectivity (ODBC) is a protocol that you can apply to connect a Microsoft Access database to an external data source like Microsoft SQL Server. This article has background information about ODBC data sources, how to make them, and how to connect to them by utilising Microsoft Access. The procedure steps may differ according to the distinct database products and ODBC drivers applied.
In this article
About ODBC data sources
A data source is a source of data merged with the connection information that is a prerequisite to reach that data. Examples of data sources are SQL Server, Oracle RDBMS, a spreadsheet, and a text file. Examples of connection information comprise server location, database name, logon ID, password, and multiple ODBC driver options that outline how to connect to the data source. This information can be acquired from your intended database administrator whereby you seek to connect.
In the ODBC architecture, an application like Access connects to the ODBC Driver Manager, which gradually applies a specific ODBC driver (for example, Microsoft SQL ODBC driver) to connect to a data source. In Access, you employ ODBC data sources to connect to data sources outside Access with no built-in drivers.
To connect to these data sources, you must do the following:
- Install the suitable ODBC driver on the computer that has the data source.
- Set a data source name (DSN) by utilising 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 transfer the connection information directly to the ODBC Driver Manager.
Machine data sources
Machine data sources store connection information in the Windows Registry on a particular computer. You can employ machine data sources solely on the computer they are set on. There are two varieties of machine data sources — user and system. User data sources can be relied upon only by the present user and are seen merely to that user. System data sources can be utilised by all users on a computer and are noticeable to all users on the computer and system-wide services. A machine data source is notably helpful anytime you seek to supply extra layers of security, since only logged-on users can see a machine data source and it is inachievable for this to be duplicated 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 usually more versatile to use than machine data sources. example, you can replicate a file data source to any computer with the right correct ODBC driver, enabling your application to depend on regular and credible connection information to every computer it deplots. Or you can arrange the file data source on a single server, share it between multiple computers on the network, and naturally keep the connection information in one location.
A file data source can even be unshareable. An unshareable file data source is retained on a single computer and points to a machine data source. You can employ unshareable file data sources to enter current machine data sources from file data sources.
In a module, you can determine a formatted connect string that elaborates connection information. A connect string transmits the connection information directly to the ODBC Driver Manager, and it assists to reduce the complexity of your application by deleting the requirement that a system administrator or user first produce a DSN before you deploy the database.
For more information about the ODBC interface, see the ODBC Programmer’s Reference section of MSDN.
Add an ODBC data source
Before continuing, acquire and install the suitable ODBC driver for your preferred data source to connect.
Note: You must be a member of the Administrators group on your local machine in order to insert or structure an ODBC data source.
- Select Start, and then pick Control Panel.
- In the Control Panel, double-click Administrative Tools.
- In the Administrative Tools dialogue box, double-click Data Sources (ODBC). The ODBC Data Source Administrator dialogue box emerges.
- Choose User DSN, System DSN, or File DSN, according to the data type of your desired source to add. For further information, see the section About ODBC data sources.
- Press Add.
- Pick your sought driver to use, and then select Finish or Next. If your specific driver is unlisted, contact the administrator of the database you are connecting to for details regarding how to acquire the correct driver.
- Follow the instructions and type the necessary connection information in any dialogue boxes that follow.
Press Help in the ODBC dialogue boxes for greater information about the separate settings.