Using DAO to read data sources other than MS Access

MS Access (.mdb) is not the only data source you can use as a DAO database. Many other types can be opened directly without ODBC.

The secret lies in the parameters passed to CDaoDatabase::Open(). If you're opening an Access file, you can omit all but the first parameter:

CDaoDatabase db;
db.Open("c:\\test.mdb");

But there are three more parameters for Open(). Here's the prototype:

Open( LPCTSTR lpszName,
      BOOL bExclusive=FALSE,
      BOOL bReadOnly=FALSE,
      LPCTSTR lpszConnect="");

If lpszConnect is blank (the default value), it designates you're opening an Access file. The following table shows the connect string used to open all the types supported by DAO:

File Type Connect String
Access blank
dBASE III dBASE III;
dBASE IV dBASE IV;
dBASE 5 dBASE 5.0;
Paradox 3.x Paradox 3.x;
Paradox 4.x Paradox 4.x;
Paradox 5.x Paradox 5.x;
FoxPro 2.0 FoxPro 2.0;
FoxPro 2.5 FoxPro 2.5;
FoxPro 2.6 FoxPro 2.6;
Excel 3.0 Excel 3.0;
Excel 4.0 Excel 4.0;
Excel 5.0 or Excel 95 Excel 5.0;
Excel 97 Excel 97;
HTML Import HTML Import;
HTML Export HTML Export;
Text Text;

Opening an Excel spreadsheet in an MFC application

CDaoDatabase* pDB = new CDaoDatabase;
pDB->Open("c:\\test.xls",  //path + file name
     FALSE,           //exclusive?
     FALSE,           //read only?
     "Excel 5.0;");   //connect string
CDaoRecordSet rs(pDB);
rs.Open(dbOpenDynaset, "select * from [Sheet1$]");

Excel is handled a little differently than Access. DAO treats any named range in the sheet as a table, and each column in the range becomes a column in the table. The first row in the range is not included as a row in the table; instead, it is used to assign the column names. DAO also treats the entire sheet as a table, using the sheet name as the table name (as in the example above) and using the first row in the sheet to assign column names. A couple of things to note about using the sheet name as the table name: You must suffix it with a $ and enclose it in square brackets.

Opening a dBase, FoxPro or Paradox database

These databases differ from Access in that one file only contains one table. Opening one of these is similar to opening an Access database, except you only supply the path to the database as the first parameter to Open(), and you use the file name as the table name in your SQL. Here's an example of opening a FoxPro database:

CDaoDatabase* pDB = new CDaoDatabase;
pDB->Open("d:\\source\\bosold\\db",  //path only!
     TRUE,           //exclusive?
     TRUE,           //read only?
     "FoxPro 2.6;");   //connect string
CDaoRecordset rs(pDB);
rs.Open(dbOpenDynaset, "select CUST from [customer.dbf]");

Here's how you can modify the MFC sample app "DAOTable" to support multiple types.

1) add a selection "all files (*.*)" to the file dialog in CDAOTableDlg::OnButtonConnect():
CFileDialog dlg (TRUE,_T("mdb"),NULL,OFN_HIDEREADONLY,
   _T("Access Files (*.mdb)|*.mdb|all files (*.*)|*.*||"),this);
2) After calling DoModal(), get the extension of the selected file:
CString strDatabaseExt = dlg.GetFileExt();
3) Add a parameter to openDatabase() to receive the file extension:
[the declaration]
int openDatabase(CDaoDatabase **ppDatabase,
                 CString fileName,
                 BOOL bReportNoOpen = TRUE,
                 CString fileExt = "");

[the definition]
int openDatabase(CDaoDatabase **ppDatabase,
                 CString fileName,
                 BOOL bReportNoOpen,
                 CString fileExt)
4) Meanwhile back in OnButtonConnect(), pass the file extension to openDatabase():
// now open the database if possible
int retCode = openDatabase(&m_pDatabase,
              m_strDatabaseName,
              TRUE,
              strDatabaseExt);
5) Test the file extension in openDatabase() and set the connect string accordingly:
if (ppDatabase == NULL)
   return -1; // fatal error

fileExt.MakeLower();
CString strConnect;
	
if (fileExt == "xls")
   strConnect="Excel 5.0;";
else if (fileExt == "mdb")
   strConnect="";
else
   {
      AfxMessageBox("File type not supported");
      return -1;
   }

// now open the database object with error checking
try
{
   (*ppDatabase)->Open(fileName,
                       FALSE,
                       FALSE,
                       strConnect);
}
catch (CDaoException *e)
{
   etc.

You can use this version of DAOTable to explore the structure of a file as seen by DAO.

Last updated: 27 July 1998



Comments

  • How can I open a Foxpro Table using VB6???

    Posted by Legacy on 02/21/2004 12:00am

    Originally posted by: charlie B.

    i need to load a Foxpro Table in VB6 then modify it or use it as a table in VB6.

    Reply
  • How Can I open a Foxpro Table in Vb and modify it?

    Posted by Legacy on 02/21/2004 12:00am

    Originally posted by: charlie

    i need to know how can i open a FoxPro Table in VB6 then modify it in VB6.

    Reply
  • How can i convert a MS Access file to Excel file

    Posted by Legacy on 01/23/2004 12:00am

    Originally posted by: Amena Afreen

    hi ,
    how can i convert a ms access file into excel file plz intimate me as soon as possible and also how can i get ans for all the queries sent here..plz help
    thanx..
    amena afreen

    Reply
  • How to convert an Oracle database into MS-Access ??

    Posted by Legacy on 01/14/2004 12:00am

    Originally posted by: Kris

    1.How can i convert the oracle database tables into msaccess database, actually i want to copy the oracle table structure to Access.

    2.Is there any way that i can know the relationships between the oracle tables.

    3.how can i create a HTML file using Oracle forms 6.i.

    TQ

    Reply
  • How Can You Create a .dbf Free Table Using VB?

    Posted by Legacy on 12/28/2003 12:00am

    Originally posted by: Christopher Z...

    Hi Everyone!

    I'm looking for a way to create a .dbf FreeTable using VB6 and ADO.

    Thanks!

    :)

    Christopher

    (take away the _no-spam_ on the email address link above to get the real e-mail address and replace .com with .info Thanks... )

    Reply
  • How will i convert my visual foxpro table into Oracle Table

    Posted by Legacy on 12/19/2003 12:00am

    Originally posted by: sunil

    How will i convert my visual foxpro table into Oracle Table
    

    Reply
  • "External table isn't in the expected format" Error

    Posted by Legacy on 12/04/2003 12:00am

    Originally posted by: Olga

    The following code:

    CDaoDatabase* pDB = new CDaoDatabase;
    pDB->Open("d:\\dbase",
    TRUE,
    TRUE,
    "Paradox 5.x;");
    CDaoRecordset rs(pDB);
    rs.Open(dbOpenDynaset, "select * from [les.db]");

    is accompanied with Error message
    "External table isn't in the expected format".

    How to fix this problem?

    Reply
  • Converting ACCESS file into Excel file

    Posted by Legacy on 11/20/2003 12:00am

    Originally posted by: SUDHIR NAIK

    Please tell me how to convert MSACCESS database into Excel Format?

    Thank you

    Reply
  • Pictures in oracle database through Access

    Posted by Legacy on 09/22/2003 12:00am

    Originally posted by: GR

    HOW CAN I STORE AND RETRIEVE A PICTURE IN AND FROM THE ORACLE DATABASE THROUGH AN ACCESS FORM

    Reply
  • How to export & import SQL tables to DBase(.dbf) file from VB

    Posted by Legacy on 08/13/2003 12:00am

    Originally posted by: Kamal Hemajith

    Here i need to know, how to prepare a vb code to export SQL Table to .dbf file format and import .dbf file to SQL Table.
    

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

Top White Papers and Webcasts

  • Live Event Date: December 11, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Market pressures to move more quickly and develop innovative applications are forcing organizations to rethink how they develop and release applications. The combination of public clouds and physical back-end infrastructures are a means to get applications out faster. However, these hybrid solutions complicate DevOps adoption, with application delivery pipelines that span across complex hybrid cloud and non-cloud environments. Check out this …

  • VMware vCloud® Government Service provided by Carpathia® is an enterprise-class hybrid cloud service that delivers the tried and tested VMware capabilities widely used by government organizations today, with the added security and compliance assurance of FedRAMP authorization. The hybrid cloud is becoming more and more prevalent – in fact, nearly three-fourths of large enterprises expect to have hybrid deployments by 2015, according to a recent Gartner analyst report. Learn about the benefits of …

Most Popular Programming Stories

More for Developers

RSS Feeds