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:

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.