Using DAO to read data sources other than MS Access


Full Text Search: The Key to Better Natural Language Queries for NoSQL in Node.js

Date: 1/31/2018 @ 2 p.m. ET

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;

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 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,
5) Test the file extension in openDatabase() and set the connect string accordingly:
if (ppDatabase == NULL)
   return -1; // fatal error

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

// now open the database object with error checking
catch (CDaoException *e)

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

Last updated: 27 July 1998


  • 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.

  • 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.

  • 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
    amena afreen

  • 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. can i create a HTML file using Oracle forms 6.i.


  • 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.




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

  • 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

  • "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;
    "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?

  • 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

  • Pictures in oracle database through Access

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

    Originally posted by: GR


  • 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.

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

Top White Papers and Webcasts

  • As all sorts of data becomes available for storage, analysis and retrieval - so called 'Big Data' - there are potentially huge benefits, but equally huge challenges...
  • The agile organization needs knowledge to act on, quickly and effectively. Though many organizations are clamouring for "Big Data", not nearly as many know what to do with it...
  • Cloud-based integration solutions can be confusing. Adding to the confusion are the multiple ways IT departments can deliver such integration...

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date