Using the CDatabase class to read an Access databases

• Download demo project - 32 Kb

Introduction

This is a very simple code snippet that demonstrates how to read a Microsoft Access database using the CDatabase class.

The main features it demonstrates are:

• Retrieving data from Microsoft Access database
• Connecting without the need for an ODBC data source to be set up.
• Populate a List view Control with the data

//
// Part of the source code
void CReadDBDlg::OnRead() 
{
	// TODO: Add your control notification handler code here
	CDatabase database;
	CString SqlString;
	CString sCatID, sCategory;
	CString sDriver = "MICROSOFT ACCESS DRIVER (*.mdb)";
	CString sDsn;
	CString sFile = "d:\\works\\ReadDB\\Test.mdb";
	// You must change above path if it's different
	int iRec = 0; 	
	
	// Build ODBC connection string
	sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",sDriver,sFile);
	TRY
	{
		// Open the database
		database.Open(NULL,false,false,sDsn);
		
		// Allocate the recordset
		CRecordset recset( &database );

		// Build the SQL statement
		SqlString =  "SELECT CatID, Category "
				"FROM Categories";

		// Execute the query
		recset.Open(CRecordset::forwardOnly,SqlString,CRecordset::readOnly);
		// Reset List control if there is any data
		ResetListControl();
		// populate Grids
		ListView_SetExtendedListViewStyle(m_ListControl,LVS_EX_GRIDLINES);
 
		// Column width and heading
		m_ListControl.InsertColumn(0,"Category Id",LVCFMT_LEFT,-1,0);
		m_ListControl.InsertColumn(1,"Category",LVCFMT_LEFT,-1,1);
		m_ListControl.SetColumnWidth(0, 120);
		m_ListControl.SetColumnWidth(1, 200);

		// Loop through each record
		while( !recset.IsEOF() )
		{
			// Copy each column into a variable
			recset.GetFieldValue("CatID",sCatID);
			recset.GetFieldValue("Category",sCategory);

			// Insert values into the list control
			iRec = m_ListControl.InsertItem(0,sCatID,0);
			m_ListControl.SetItemText(0,1,sCategory);

			// goto next record
			recset.MoveNext();
		}
		// Close the database
		database.Close();
	}
	CATCH(CDBException, e)
	{
		// If a database exception occured, show error msg
		AfxMessageBox("Database error: "+e->m_strError);
	}
	END_CATCH;
}
	
// Reset List control
void CReadDBDlg::ResetListControl()
{
	m_ListControl.DeleteAllItems();
	int iNbrOfColumns;
	CHeaderCtrl* pHeader = (CHeaderCtrl*)m_ListControl.GetDlgItem(0);
	if (pHeader)
	{
		iNbrOfColumns = pHeader->GetItemCount();
	}
	for (int i = iNbrOfColumns; i >= 0; i--)
	{
		m_ListControl.DeleteColumn(i);
	}
}