Accessing Data from Microsoft Access Database Using ODBC in C++

Accessing Data from an ODBC Connection in C++

This tutorial demonstrates how to access data from a Microsoft Access database using the ODBC (Open Database Connectivity) protocol in C++. We will cover the basics of creating an ODBC connection, executing SQL queries, and retrieving results.

Prerequisites

A Microsoft Access database file (.mdb or .accdb)
The Microsoft Access Driver for ODBC
A C++ compiler (e.g., Visual Studio)

Step 1: Include Necessary Libraries and Set Up the Environment

First, let’s include the necessary libraries:

#include <iostream>
#include <string>
#include <vector>
#include <Windows.h>
#include <sqlext.h> // ODBC library

Next, set up your environment by linking against the ODBC library and the Microsoft Access Driver. The exact steps may vary depending on your build system.

Step 2: Create a Function to Display Errors

Create a function DisplayError that displays error messages from SQL:

void DisplayError(SQLSMALLINT t, SQLHSTMT h) {
    SQLWCHAR       SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH];
    SQLINTEGER    NativeError;
    SQLSMALLINT   i, MsgLen;
    SQLRETURN     rc;

    SQLLEN numRecs = 0;
    SQLGetDiagField(t, h, 0, SQL_DIAG_NUMBER, &amp;numRecs, 0, 0);

    // Get the status records.
    i = 1;
    while (i <= numRecs && (rc = SQLGetDiagRec(t, h, i, SqlState, &amp;NativeError,
        Msg, sizeof(Msg), &amp;MsgLen)) != SQL_NO_DATA) {
        std::wprintf(L"Error %d: %s\n", NativeError, Msg);
        i++;
    }
}

Step 3: Create a Function to Execute an SQL Query

Create a function ExecuteSql that executes an SQL query and returns the results:

std::vector<std::wstring> ExecuteSql(const WCHAR* sql) {
    std::vector<std::wstring> results = {};
    HENV hEnv = NULL;
    HDBC hDbc = NULL;
    HSTMT hStmt = NULL;
    int iConnStrLength2Ptr;
    WCHAR szConnStrOut[256];
    SQLINTEGER rowCount = 0;
    SQLSMALLINT fieldCount = 0;
    SQLWCHAR buf[128];
    SQLINTEGER ret;

    /* ODBC API return status */
    RETCODE rc;

    /* Allocate an environment handle */
    rc = SQLAllocEnv(&amp;hEnv);
    if (SQL_SUCCEEDED(rc)) {
        /* Allocate a connection handle */
        rc = SQLAllocConnect(hEnv, &amp;hDbc);

        if (SQL_SUCCEEDED(rc)) {
            /* Connect to the database */
            rc = SQLDriverConnect(hDbc, NULL, (WCHAR*)szDSN,
                SQL_NTS, (WCHAR*)szConnStrOut,
                255, (SQLSMALLINT*)&amp;iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);

            if (SQL_SUCCEEDED(rc)) {
                /* Prepare SQL query */
                rc = SQLAllocStmt(hDbc, &amp;hStmt);
                if (SQL_SUCCEEDED(rc)) {
                    rc = SQLPrepare(hStmt, (SQLWCHAR*)sql, SQL_NTS);

                    if (SQL_SUCCEEDED(rc)) {
                        /* Excecute the query */
                        rc = SQLExecute(hStmt);
                        if (SQL_SUCCEEDED(rc)) {
                            SQLNumResultCols(hStmt, &amp;fieldCount);

                            if (fieldCount > 0) {
                                /* Loop through the rows in the result set */

                                rc = SQLFetch(hStmt);

                                while (SQL_SUCCEEDED(rc))
                                {   
                                    //get data
                                    rc = SQLGetData(hStmt, 1, SQL_C_WCHAR, buf, sizeof(buf), &amp;ret);
                                    
                                    if (SQL_SUCCEEDED(rc) == FALSE) {
                                        std::wprintf(L"SQLGetData failed\n");
                                        continue;
                                    }

                                    //convert data to string
                                    std::wstring str;
                                    if (ret <= 0) {
                                        str = std::wstring(L"(null");
                                    }
                                    else {
                                        str = std::wstring(buf);
                                    }
                                    
                                    results.push_back(str);
                                    rc = SQLFetch(hStmt);
                                    rowCount++;
                                };

                                rc = SQLFreeStmt(hStmt, SQL_DROP);

                            } else {
                                std::wprintf(L"Error: Number of fields in the result set is 0.\n");
                            }

                        } else {
                            wprintf(L"SQL Failed\n");
                            DisplayError(SQL_HANDLE_STMT, hStmt);
                        }
                    }
                    else {
                        std::wprintf(L"Failed to prepare query\n");
                    }
                }
                else {
                    std::wprintf(L"Failed to allocate statement handle\n");
                }
            }
            else {
                std::wprintf(L"Failed to connect to database\n");
            }
        }
        else {
            std::wprintf(L"Failed to allocate connection handle\n");
        }
    }
    else {
        std::wprintf(L"Failed to allocate environment handle\n");
    }

    // Clean up
    if (hStmt != NULL) {
        SQLFreeStmt(hStmt, SQL_DROP);
    }
    if (hDbc != NULL) {
        SQLDisconnect(hDbc);
        SQLFreeConnect(hDbc);
    }
    if (hEnv != NULL) {
        SQLFreeEnv(hEnv);
    }

    return results;
}

Step 4: Execute an SQL Query and Print the Results

Finally, create a function that executes an SQL query and prints the results:

int main() {
    // Define the database connection string
    const WCHAR* szDSN = L"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\path\\to\\your\\database.mdb";

    // Execute the SQL query
    std::vector<std::wstring> results = ExecuteSql(L"SELECT * FROM your_table");

    // Print the results
    for (const auto& row : results) {
        std::wcout << row << std::endl;
    }

    return 0;
}

This code example demonstrates how to create an ODBC connection, execute an SQL query, and print the results. Make sure to replace C:\\path\\to\\your\\database.mdb with the actual path to your database file.

Conclusion

In this tutorial, we demonstrated how to access data from a Microsoft Access database using the ODBC protocol in C++. We created functions for displaying errors and executing SQL queries, which can be customized according to your specific requirements.

Last modified on 2024-10-30