Problem

Many apps fancy an export function. So wouldn´t it be nice to be able to easily save that result you´ve got there as an Excel sheet?
ODBC does make this possible, but there´s one little drawback: Using ODBC the usual way there has to be a registered datasource (DSN) in the ODBC manager.
This is not very useful because you´d have to install that DSN locally on every machine that should support your export function.

Omiting the DSN tag in the connect string of CDatabase:OpenEx() gives us the opportunity to refer the ODBC-Driver directly using its name so we don´t have to have a DSN registered.
This, of course, implies that the name of the ODBC-Driver is exactly known.

If you just want to test if a certain driver is present (to show the supported extensions in the CFileOpenDlg for example) just try to CDatabase:OpenEx() it.
If it isn´t installed an exception gets thrown.

To create and write to that Excel sheet you simply use SQL as shown in the code sample below.

In order to get the code below going you have to

1. have <afxdb.h> included
2. have an installed ODBC-driver called "MICROSOFT EXCEL DRIVER (*.XLS)"

// this example creates the Excel file C:\DEMO.XLS, puts in a worksheet with two
// columns (one text the other numeric) an appends three no-sense records.   
//  
void MyDemo::Put2Excel()
{
  CDatabase database;
  CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // exactly the same name as in the ODBC-Manager
  CString sExcelFile = "c:\\demo.xls";                // Filename and path for the file to be created
  CString sSql;
    
  TRY
  {
    // Build the creation string for access without DSN
       
    sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s", sDriver,sExcelFile,sExcelFile);

    // Create the database (i.e. Excel sheet)
    if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
    {
      // Create table structure
      sSql = "CREATE TABLE demo (Name TEXT,Age NUMBER)";
      database.ExecuteSQL(sSql);

      // Insert data
      sSql = "INSERT INTO demo (Name,Age) VALUES ('Bruno Brutalinsky',45)";
      database.ExecuteSQL(sSql);

      sSql = "INSERT INTO demo (Name,Age) VALUES ('Fritz Pappenheimer',30)";
      database.ExecuteSQL(sSql);

      sSql = "INSERT INTO demo (Name,Age) VALUES ('Hella Wahnsinn',28)";
      database.ExecuteSQL(sSql);
    }      

    // Close database
    database.Close();
  }
  CATCH_ALL(e)
  {
    TRACE1("Driver not installed: %s",sDriver);
  }
  END_CATCH_ALL;
}

Date Posted: Feb. 03, 1999

Comments:

• It won't work for 3.50 (only 3.51 and above) - Tim Leo (2000/04/07)
• where do you find .... - aryong (1999/11/26)
• Pos toavía no lo probao - Paquito (1999/09/21)
• Where can I find the upgrade for the ODBC-Admin Version?? - Bela Orosz (1999/03/08)
• Heres a way to find all available drivers on a system... - Christopher W. Backen (1999/02/11)
• Works only with ODBC-Admin Version >= 3.5x - Alexander Mikula (1999/02/05)
• Is Microsoft Excell ODBC driver support transcation? - Rochmad Setyadi (1999/02/03)

Add Comment

INSTANT EXPERTS!
Savvy IT Pros report on SupportSource. Take a look inside the largest vault of multi-vendor computing specs ever assembled. Click here for an In-depth Report,.

For tons of Windows technology resources, check out the Windows Programming directory.

Searching the Web for tech topics just got better with
TechCrawler where all results come only from popular technical sites.

WINNER!!!
Congratulations to James Hole, of New Mexico! The grand prize winner of EarthWeb's Flatten the Obstacles Sweepstakes and now a proud Hummer(R) owner.