problem querying excel file tables via odbc 
Author Message
 problem querying excel file tables via odbc

Hi Everyone,

I am trying to query an excel file from Visual C++ and
display it's contents on a listview control. I have found
that if I first create the excel file in visual c++ and
then try to open the file and display contents, everything
works well. However if I try to open a file that I have
not initially created through vc++, then I get an runtime
error saying the table in the file I requested could not
be found. That is, in case one, I create a table in a
excel file called "test" and then I query table "test". In
the second case I assume the table name is "sheet1" and my
query fails. My question is if an excel file is treated as
a database then where are the table names defined? And if
table names are indeed synonymous to Worksheets in Excel,
do you know what else could be causing the problem?

Also, if I wanted to not only display data in a listview
but also make it editable can I still use listview and
odbc or something else?

I have included parts of my code below.

Thanks in advance!
run time error message: "The Microsoft Jet database engine
could not find the object 'demo'. Make sure the object
exists and that you spell its name and path name
correctly."

//query table
ODBCExcelConnection dbCon;
CString myFileName;
CRecordset * rs;
CDatabase db;
CString sql, str, table, sExcelFile, sExcelDriver;      

sExcelFile.Format("C:\\files\\Nodes1.xls");
table.Format("demo");
sExcelDriver.Format("Microsoft Excel Driver (*.xls)");
   sql.Format ( "DRIVER=s};DSN='';READONLY=TRUE;DBQ=  s",
   sExcelDriver, sExcelFile );
rs = new CRecordset ( &db ) ;
try
{
   if ( db.OpenEx(sql, CDatabase::noOdbcDialog) )
   {
      sql.Format ( "SELECT * FROM %s", table );
      db.ExecuteSQL( sql );
      rs->Open(CRecordset::snapshot, sql,
         CRecordset::none );
...                    
    }
    rs-> Close( ) ;
    db.Close( ) ;
 }
 catch ( CDBException e )
 {
    MessageBox ( "Driver not installed: %s",
      sExcelDriver ) ;
 }

//create table
TRY
{
   // Build the creation string for access without DSN
   sSql.Format("DRIVER={s};DSN=''; FIRSTROWHASNAMES=1;
       READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",
       sExcelDriver, sExcelFile, sExcelFile);

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

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



Sun, 25 Dec 2005 20:28:08 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Query db using MFC via ODBC

2. Exporting Access Queries/Tables to Excel

3. Exporting Access Queries/Tables to Excel

4. Accessing an Ms Access 2000 Query using VC++, via ODBC

5. ReadOnly-Error when writing to Oracle 7 table via ODBC in C++

6. ?can't AddNew() when open a table via ODBC

7. No views only tables via ODBC

8. Database table locking problem - reposting the query

9. problem in using Recordset to open ORACLE database via ODBC system data source

10. problems with database access via ODBC in console app

11. ODBC 3.0 and Excel/Text Files HELP ME PLEASE

12. ODBC drivers to Excel files

 

 
Powered by phpBB® Forum Software