Open Database Connectivity (ODBC) is a protocol used to connect a Microsoft Access database to an external data source, such as Microsoft SQL Server. This article provides an overview of ODBC data sources, how to create them, and how to connect to them using Microsoft Access. The steps you need to take depend on the databases and ODBC drivers you are using.

In this article

About ODBC Data Sources

A data source is a source that contains the data and connection information needed to access that data. The data source can be a SQL Server, an Oracle relational database, a spreadsheet, or a text file. The connection information may include the server location, database name, login ID, password, and various ODBC driver parameters that describe how to connect to the data source. You can obtain this information from the administrator of the database you want to connect to.

In the ODBC architecture, applications (such as Access) connect to an ODBC Driver Manager, which in turn uses a specific ODBC driver (such as Microsoft SQL ODBC) to connect to the data source. Access uses ODBC data sources to connect to external data sources that do not have built-in drivers.

To connect to such a data source, follow these steps:

Install the appropriate ODBC driver on the data source computer.

Determine the Data Source Name (DSN) using the program ODBC Data Source Administrator to store connection information in the Microsoft Windows registry or DSN file, or by using a connection string in Visual Basic code to pass 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. These data sources can only be used on the computer on which they are defined. There are two types of machine data sources: user and system. Custom data sources are available and visible only for the current user. System data sources can be used by all computer users. They are displayed for all these users and system services. Machine data sources are especially useful when you need to provide additional security because only logged-in users can view the machine data source. In addition, such a source cannot be remotely copied to another computer.

File data sources

File-based data sources (also called DSN files) store connection information in text files rather than in the Windows registry, and are generally more flexible to use than machine-based data sources. For example, a file data source can be copied to any computer with a suitable ODBC driver to ensure that the application has accurate and consistent connection information across all computers used. You can also put your file data source on a separate server, share it across multiple computers on your network, and easily manage centralized connection information.

Some file data sources cannot be shared. Such sources are located on a separate computer and point to a machine data source. They can be used to access existing machine data sources from file data sources.

Connection strings

You can define a formatted string with connection information in the module. The connection string passes connection information directly to the ODBC Driver Manager. This helps simplify the application by eliminating the need for the administrator or user to create a DSN before using the database.

For more information about the ODBC interface, see the MSDN ODBC Programmer's Reference.

Adding an ODBC Data Source

Before you continue, install the appropriate ODBC driver for the data source you want to connect to.

Note: To add or configure an ODBC data source, you must be a member of the Administrators group on the local computer.

For more information about individual options, click Reference in the ODBC dialog box.

The other day I encountered this problem: on Windows 7, especially in the starter and home versions, there are no ODBC data sources.

ODBC (Open Database Connectivity)- a database access program interface (API) developed by Microsoft, in collaboration with Simba Technologies, based on the Call Level Interface (CLI) specifications, which was developed by SQL Access Group, X/Open and Microsoft. The CLI was subsequently standardized by ISO ISO/IEC 9075-3:2003. (English) The CLI standard is designed to unify software interaction with the DBMS, making it independent of the DBMS vendor and hardware and software platform.



In early 1990, there were several database vendors, each with their own interface. If an application needed to communicate with multiple data sources, it was necessary to write its own code to interact with each of the databases. To solve this problem, Microsoft and a number of other companies have created a standard interface for receiving and sending various types of data sources. This interface was called Open Database Connectivity, or an open mechanism for interacting with databases.
With ODBC, application programmers could develop applications to use a single data access interface without having to worry about the intricacies of interacting with multiple sources.

That is, the ODBC tab itself is there, here is a screenshot:

And it even opens, but when adding “System DSN”

There is no choice of databases, as for example on Windows XP, there is a choice of only one type of database, this is SQL, this is not good at all. Because, for example, many bank clients require an ODBC data source from Microsoft (mdb). And if you don’t select it, how can you install the bank client on Windows 7 starter or home? Below are two screenshots, one is Windows 7 starter, the other is Windows XP.

I will say that there is a way out of this situation. Windows 7 starter or home is designed for the home, so it does not have the bells and whistles that are needed for professional programs. Here is the difference in price, if the home or starter version ranges from 2100 - 3000 rubles, then the PRO version costs at least twice as much. But you can still install the bank client in Windows 7 starter and home. Just install the bank client, it will automatically create an ODBC data source from Microsoft, although it will not be visible in the field. That is, in simple words, the field will be empty, and an mdb database will be created.

Installing the bank client

After installing the client bank, it started up perfectly and worked.

In this way, Windows is being a little cunning, that is, hinting to you that to install an ODBC data source you need to buy a more expensive version, although everything works in starter or home, there’s just no field.

And it even opens, but only when you add “System DSN”:

There is no choice of databases, such as on Windows XP. It turns out that you only have to choose one of the database types - SQL, and this is not at all good. Because, for example, many bank clients require ODBC data source exactly from Microsoft (mdb)!!! And if you don’t select it, how can you install the bank client on Windows 7 Starter or Windows 7 Home?



For comparison, look at two screenshots, one - Windows 7 Starter, another - Windows XP.

1) Windows 7 Starter:

2) Windows XP Professional:

Nevertheless, there is a way out of this situation! Versions Windows 7 Starter or Home, are designed for home use, so they don’t have the bells and whistles that are needed for professional programs. Here is the difference in price, for comparison: if the cost Home or Starter version of Windows ranges from 2100 – 3000 rubles, then PRO- the version costs at least twice as much.



But you can still put the client bank in Windows 7 Starter And Windows 7 Home. Just install the client bank, it will create it itself ODBC data source from Microsoft, although it won't be visible in the field. That is, in simple words, the field will be empty, and an mdb database will be created.

Installing the client bank:

After installing the bank client, it started up perfectly and worked.

Thus, the Windows company is a little cunning, as if hinting to you that to install ODBC data source you need to buy a more expensive version, although everything works in the Starter or Home versions, there’s just no field.

Creating an application that uses a database

Abstract to the subsection

The purpose of the subsection is to ensure the completion of laboratory work on creating a database application·

Aliases are often used to work with databases. A database alias is the name of a database, which is written in the settings in such a way that to access the tables of this database it is enough to specify alias. This convenience allows you to easily change the database type without changing the program text.

Let's consider the principle of determining an alias for a Microsoft Access database stored in a file on a hard drive. Let's say we want the borey alias to correspond to a database in the borey.mdb file. We can do this programmatically using the SQLConfigDataSource ODBC API function.

To set an alias manually, you must run the ODBC Data Source Administrator (Start\Settings\Control Panel\Administrative Tools\Data Sources (ODBC)). The administrator window is shown in Figure 6.

Figure 6 – ODBC Data Source Administrator

Next, on the “User DSN” or “System DSN” tab (depending on whether the data source is being created for the current user or for all system users, respectively), click the “Add” button. ·

A list of installed drivers and their description will be displayed (Figure 7).

Figure 7 – ODBC Data Source Administrator. Dialog for creating a new data source

·After clicking on the “Finish” button in the window that opens (Figure 8), you must specify the source name “db” and the path to the file.

Figure 8 – ODBC Data Source Administrator. Dialog for creating a new data source

· By clicking the “Ok” button, the system will display the “borey” alias for the database, which can be used when accessing the borey.mdb file (Figure 9). It should be noted that in a similar way you can specify an alias for other DBMSs.

Figure 9 – ODBC Data Source Administrator. Dialog for creating a new data source

2.2 Basic information about the C++ language (wikipedia)

·C++ is a compiled, statically typed general-purpose programming language.

Supports the following programming paradigms:

Procedural programming,

Object-oriented programming,

Generalized programming.

Provides:

Modularity,

Separate compilation

Exception handling

Data abstraction

Declaration of types (classes) of objects,

Built-in functions

Virtual functions. ·

The standard library includes, among other things, commonly used containers and algorithms. C++ combines features of both high-level and low-level languages. Compared to its predecessor, the C language, the greatest attention is paid to supporting object-oriented and generic programming. C++ syntax is inherited from the C language.

The latest C++ language standard, called “C++11,” was adopted in 2011. It included significant additions to the core of the language and an expansion of the standard library. ·

The bottom line: you need to connect to a database created in the MS Access DBMS via ODBC in your own Delphi 7 application.

First, go to the section “Control Panel – Administration – Data Sources (ODBC)”. Add a new data source.

Rice. 1. ODBC Data Source Administrator

Then select "Driver do Microsoft Access".

Rice. 2. Create a new data source

Then, in the “Install ODBC Driver ...” window, set the name, description and select the path to the database, for example, “Bus station. mdb».

Rice. 3. Installing the ODBC driver for MS Access

After this, we go to the original “Data Source Administrator...” window and see that “Bus Station” has been added to the source.

Rice. 4. Initial window “Data Source Administrator...”

Then we launch the Delphi 7 application and place the components on the form: ADOConnection, ADOTable, DataSource, DBGrid. Configuring components for connection via ODBC.

First, click on the component “ ADOConnection 1" and set up the connection string, select "Microsoft OLE DB Provider for ODBC". Click the “Next” button and go to the “Connection” tab.

Rice. 5. Setting up a connection string in Delphi 7

On the “Connection” tab, select the “Bus Station” data source and check the connection by clicking on the “Test connection” button.

Now, if you set the property “Active = True” for the component “ ADOTable 1", then the table set in the property " TableName" will be displayed on the component " DBGrid 1".

Rice. 7. Table “Days of movement” in our application

Thus, we connected to the “Bus Station” database via “ODBC”, which means “Open Data Base Connectivity”.

The book describes the Windows Script Host (WSH) script server version 5.6, which is one of the standard components of Windows XP and can also be installed on earlier versions of Windows. Provides VBScript and JScript scripts that illustrate the use of standard WSH 5.6 objects, including creating child processes from scripts and running scripts on remote computers. More complex examples include scenarios that work with databases, manage Microsoft Office applications, organize a graphical user interface, and use ADSI (Active Directory Service Interface) and WMI (Windows Management Instrumentation) technologies to solve administration tasks. Issues of practical work with XML files and creation of COM objects are covered. Particular attention is paid to security issues when working with scripts, and specific examples of security policy settings are provided. The book comes with a floppy disk containing the source code for most of the examples.

Book:

To access the created DBF table from a WSH script, we will use ODBC (Open DataBase Connectivity) technology. ODBC is a standard Microsoft tool for working with relational databases of various formats and manufacturers, capable of processing database queries in SQL (Structured Query Language).

For an initial introduction to the SQL language, we can recommend the book.

First, we will need to create an ODBC record in the system to connect with our database, i.e. create a new DSN (Data Source Name, data source name). In Windows XP this is done as follows.

Let's download Control Panel(Control Panel) Windows (Menu Start(Start)) and switch to the classic view (Fig. 9.6).

Rice. 9.6. Classic view of the Windows XP control panel

Let's select the points sequentially Administration(Administrative tools) and Data sources (ODBC)(Data sources (ODBC)). In the dialog box that appears, select the tab System DSN(System DSN), which will allow you to create a DSN that is accessible to all computer users (Fig. 9.7).

Rice. 9.7. ODBC Data Source Administrator in Windows XP

Let's press the button Add(Add) and in the window that appears, select the Microsoft dBase Driver (*.dbf) driver (Fig. 9.8).