System DSN to Access DB


This article was contributed by J. Smits.

Environment: VC6 SP5, Win 2K SP2

The code below shows how you can add a Data Source Name at run-time.

In this sample, we use the MS Access Driver to connect to an MDB File. I have implemented the code in a class so it can easily be reused in different applications. Follow the steps below:
  1. Add the classes from the download to your project. Alternatively, paste the code below into the spot of your choice.
  2. Make sure you have the following include-statements in your project:
    #include <afxdb.h>       //Needed for WriteProfile
    #include <odbcinst.h>    //Needed for SQLConfigDataSource etc
    
  3. Create an object from the class you have included, or, if you have not done so, declare the function "CreateDSN".
  4. Compile and run the program.
  5. Check whether the DSN has been created. Be mindful that if you keep the ODBC-panel open, it doesn't refresh, even if you click a different tab. For that reason, consider using the Registry editor (see illustration). Just press F5 and new entries are listed!

Please supply any suggestions to improve my code to this site! Thanx!

BOOL CODBC::CreateDSN( CString sDBPath,
                       CString sProjectName,
                       CString sDescription)
{
 //Function creates System DSN.
 //Projectname should be one word ("Demo")
 //DBPath should be full path without 
 //       filename: ("C:\Tests")
 //Description: optional parameter.

 //Check in ODBC.INI (In your Windows DIR) if DSN already exists:
 HKEY hKey; 
 if (ERROR_SUCCESS == RegOpenKeyEx(HKEY_LOCAL_MACHINE,
             TEXT("Software\\ODBC\\ODBC.INI\\" + sProjectName),
             0L,
             KEY_QUERY_VALUE,
             &hKey))
 {
   RegCloseKey(hKey); 
   AfxMessageBox("Data Source is already registered");
   return FALSE; 
 } 
 
 char MdbFile[_MAX_FNAME];
 char DSNName[_MAX_FNAME];
 char Description[_MAX_PATH];

 char* szDesc;
 char* szAttributes;
 int mlen, i=0, j=0;

 lstrcpy(DSNName, sProjectName);
 sProjectName=sDBPath + "\\" + sProjectName +
                      ".mdb";
 lstrcpy(MdbFile, sProjectName);
 lstrcpy(Description,sDescription);

 szDesc = new char[256];
 szAttributes = new char[256];

 //Use Hexadecimal 'FF' (=255) as temporary place holder
 wsprintf( szDesc,
     "DSN=%s \xFF DESCRIPTION=%s \xFF DBQ=%s: FIL=MS Access;\xFF \xFF ",
     DSNName,
     Description,
     MdbFile);
 mlen = strlen(szDesc);
 
 //Loop to replace "FF" by "\0"(so as to store multiple 
 //   strings into one):
 while(i < mlen-1)
 {
   if ((szDesc[i] == '\xFF') && (szDesc[i+1] == ' '))
   {
     szAttributes[j] = '\0';
     i++;
   }
   else
   {
     szAttributes[j] = szDesc[i];
   }
   i++;
   j++;
 }

 //Create DSN:
 if (!SQLConfigDataSource( NULL,
                           ODBC_ADD_SYS_DSN,
                           "Microsoft Access Driver (*.mdb)\0",
                           (LPCSTR)szAttributes))
 {
   AfxMessageBox( "Failed to add Data Source\nBe sure" + 
                  " you are logged on as an Administrator");
   delete [] szDesc;
   delete [] szAttributes;
   return FALSE;
 }
 else
 {
   AfxMessageBox("Data Source Name was added successfully");
 }
   
 delete [] szDesc;
 delete [] szAttributes;

 return TRUE;
}

Downloads

Download demo project - 30 Kb
Download source - 2 Kb


Comments

  • create DSN having datasource on a different computer

    Posted by Legacy on 03/14/2002 12:00am

    Originally posted by: Komila

    My access database file is on one PC. I want to create DSN on another pc, specifiying my database file on another Computer. How can i do this?

    -Komila.

    Reply
  • error

    Posted by Legacy on 11/22/2001 12:00am

    Originally posted by: Anonymous

    I think there is an error:
    
    the following code

    wsprintf( szDesc,
    "DSN=%s \xFF DESCRIPTION=%s \xFF DBQ=%s: FIL=MS Access;\xFF \xFF ",
    DSNName,
    Description,
    MdbFile);
    mlen = strlen(szDesc);

    should be

    wsprintf( szDesc,
    "DSN=%s \xFF DESCRIPTION=%s \xFF DBQ=%s; FIL=MS Access;\xFF \xFF ",
    DSNName,
    Description,
    MdbFile);
    mlen = strlen(szDesc);


    DBQ=%s: ==> DBQ=%s;


    Reply
  • Timely

    Posted by Legacy on 11/21/2001 12:00am

    Originally posted by: Neal Horman

    How timely.... I was just thinking about how this could be done. I didn't want to take a day to figure this out right now, so I was going to leave it for later.

    Thank you.

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • The explosion in mobile devices and applications has generated a great deal of interest in APIs. Today's businesses are under increased pressure to make it easy to build apps, supply tools to help developers work more quickly, and deploy operational analytics so they can track users, developers, application performance, and more. Apigee Edge provides comprehensive API delivery tools and both operational and business-level analytics in an integrated platform. It is available as on-premise software or through …

  • Java developers know that testing code changes can be a huge pain, and waiting for an application to redeploy after a code fix can take an eternity. Wouldn't it be great if you could see your code changes immediately, fine-tune, debug, explore and deploy code without waiting for ages? In this white paper, find out how that's possible with a Java plugin that drastically changes the way you develop, test and run Java applications. Discover the advantages of this plugin, and the changes you can expect to see …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds