Hello everyone,

Can someone please tell me to how to connect MS-Access database using C. Are there any drivers or libraries that I would have to download? Please specify in detail.

Regards,
Rubeea

I tried connecting the database but I am getting an error. Please tell me what am I doing wrong?
Here's the code:

#include<sqlext.h>
#include<stdio.h>
#include<sql.h>





    int main(void){



char szDSN[256] = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=E:\\student.mdb;";

/* Data Access Method used in this sample */
const char* DAM = "Direct ODBC";

HENV    hEnv;
HDBC    hDbc;


/* ODBC API return status */
RETCODE rc;    

int     iConnStrLength2Ptr;
char    szConnStrOut[256];

unsigned char* query = "SELECT faculty.[name], faculty.[remarks] FROM faculty;";

SQLCHAR         chval1[128], chval2[128], colName[128];
int             ret1;
int             ret2;

/* Number of rows and columns in result set */
SQLINTEGER      rowCount = 0;
SQLSMALLINT     fieldCount = 0, currentField = 0;
HSTMT           hStmt;

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

/* Connect to the 'student.accdb' database */
rc = SQLDriverConnect(hDbc, NULL, (unsigned char*)szDSN, 
    SQL_NTS, (unsigned char*)szConnStrOut, 
    255, (SQLSMALLINT*)&iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
if (SQL_SUCCEEDED(rc)) 
{
    printf("%s: Successfully connected to database. Data source name: \n  %s\n", 
       DAM, szConnStrOut);

    /* Prepare SQL query */
    printf("%s: SQL query:\n  %s\n", DAM, query);

    rc = SQLAllocStmt(hDbc,&hStmt);
    rc = SQLPrepare(hStmt, query, SQL_NTS);

    /* Bind result set columns to the local buffers */ 
    rc = SQLBindCol(hStmt, 1, SQL_C_CHAR, chval1, 128, (SQLINTEGER*)&ret1);
    rc = SQLBindCol(hStmt, 2, SQL_C_CHAR, chval2, 128, (SQLINTEGER*)&ret2);

    /* Execute the query and create a record set */
    rc = SQLExecute(hStmt); 
    if (SQL_SUCCEEDED(rc)) 
    {
        printf("%s: Retrieve schema info for the given result set:\n", DAM);
        SQLNumResultCols(hStmt, &fieldCount);
        if (fieldCount > 0)
        {
            for (currentField=1; currentField <= fieldCount; currentField++)
            {
                SQLDescribeCol(hStmt, currentField,
                    colName, sizeof(colName), 0, 0, 0, 0, 0);
                printf(" | %s", colName);    
            }
            printf("\n");
        }
        else
        {
            printf("%s: Error: Number of fields in the result set is 0.\n", DAM);
        }

        printf("%s: Fetch the actual data:\n", DAM);
        /* Loop through the rows in the result set */
        rc = SQLFetch(hStmt);
        while (SQL_SUCCEEDED(rc)) 
        {
            printf(" | %s | %s\n", chval1, chval2);
            rc = SQLFetch(hStmt);
            rowCount++;
        };



    printf("%s: Total Row Count: %d\n", DAM, rowCount);
    rc = SQLFreeStmt(hStmt, SQL_DROP);
}


}
else
{
    printf("%s: Couldn't connect to %s.\n", DAM, szDSN);
}

/* Disconnect and free up allocated handles */
SQLDisconnect(hDbc);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);


printf("%s: Cleanup. Done.\n", DAM);

return 0;
}

I am getting an error, when I run this file using netbeans(C/C++ supported) with MinGW compiler at the header file:

include<sqlext.h>

Yes you can if you have the Windows SDK installed. I just successfully compiled your program with Code::Blocks and MinGW compiler after adding windows.h as the first include file.

Edited 2 Years Ago by Ancient Dragon

I included Windows.h as the first header file. But now I am getting a different error as:
[Error] C:\Users\rubeea\Documents\C-Free\Temp\Untitled1.cpp:26: error: invalid conversion from const char*' tounsigned char*'

and when I cast it explicitly I get a series of errors in which all the SQL variables are undefined!! Please help me I am now using C-Free 5.0 to run the code and I also have Windows SDK installed!!

I just compiled it with VS 2013, all I had to do was compile as C program and do some typecasting. Make sure the file name has *.c file extension, not *cpp.

Edited 2 Years Ago by Ancient Dragon

One more thing, the insert query has the format "insert into scores (Player,Score) values ('"+name+"','"+sc+"')";

in java. But if I want to use it in C++ to insert the value of sc variable f type char. How would I specify it in the values clause. Pls help

I would use sprintf() to format the string

char namne[20], sc[5];
char query[255] = {0};
// fill in name and sc not shown here

sprintf(query,"INSERT INTO scores (Player,Score) VALUES ('%s','%s');",
  name,sc);

Okay and when I am executing the query using
rc = SQLPrepare(hStmt, query, SQL_NTS);
rc = SQLExecute(hStmt);
Though, there are no errors but the data isn't inserted in the database. Please specify a way for that as well!

Most likely you got some sql error(s), for example you might be attempting to insert a new row with data that duplicates a row already in the table and the column is marked as unique.

First check the table to make sure it is not trying to insert a new row with duplicate data. If that's ok then print out the contents of the query just before calling SQLPrepare() so you can see what it contains.

Edited 2 Years Ago by Ancient Dragon

Thank you the code works fine. One more I want to insert values into database after each minutes. Can anybody help please!

use a timer to invoke the function then the time expires. How to do that might depend on the compiler. Usually you would create another thread which has an infinite loop. Inside the loop sleep() for the time you want then when it wakes up do the sql stuff and return to sleep.

You asked me to use sprintf. Well I did but the problem is that sprintf takes a char array named query and that char array is not compatible with rc = SQLPrepare(hStmt, query, SQL_NTS);
because SQLPrepare needs an SQL CHAR* or unsigned char*. I tried the type casting but it doesnot work. Pls help

if(counter>=500){

               file1=fopen("log.txt","r+");

               while ((c = fgetc(file1)) != EOF) {
                   for (j = 0; j<22; j++) 
                       currentLine[j]= (char)c;

               }

                sprintf(query,"INSERT INTO faculty (name,remarks) VALUES ('%s','checkk');",
                currentLine);
                rc = SQLPrepareA(hStmt, (SQLCHAR*)query, SQL_NTS); 

                rc= SQLExecute(hStmt);
                if((SQL_SUCCEEDED(rc))) 
                {   
                    printf("inserted\n");
                }
                SQLDisconnect(hDbc);
                SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
                SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

           }

I am using the above code to insert the value from a file into an array called currentline and then formatting the query to contain value of that array. The query doesnot executes. What am I doing wrong?

line 7: why are you repeating the same character 22 times in currentLine? Isn't once enough?

int j = 0;
while ((c = fgetc(file1)) != EOF) {
     currentLine[j++]= (char)c;
currentLine[j] = 0;

line 10: you need to NULL terminate currentLine before calling sprintf().

Edited 2 Years Ago by Ancient Dragon

One more thing. I am using an unsigned array of 200 characters to store strings. But I want to store more than 200 characters. Whenever I specify a char array with more than 200 characters it gives me an error that this large index is not possible. Please help

you need to post the code that you tried, you should be able to declare arrays in excess of several thousand, depending on the compiler. If you are using old Turbo C then you may have run out of memory. It has limited memory and a very small stack.

spintf() and many othere functions found in string.h expect strings to be null-terminated. That's how those functions know where the end-of-string is. Without that null terminator the functions will just keep looking through memory until it finds a byte whose value is 0, very possibly causing your program to crash.

I am using Visual Studio 2010.

then you should have no problem declaring an array such as unsigned char currentLine[20000]

This article has been dead for over six months. Start a new discussion instead.