Access Automation from Visual C++

Lets face it, out of the MS office and Visual Studio MS Access has the best reporting facilities. Very complicated reports can be made in access very easily, where as the same report can be made in VB and VC with much more effort. The worst is VC even with the document-View Architecture creating a report requires lot of coding.

We can connect Access with our backend database and can really create some cool reports in a jiffy. You can print this good looking report and user need not no what did you do behind the scene. I wanted to use the Access report for preview and print, but with out user clicking a extra button, and the reports should come so smooth that user shouldn't no that the access came in to play and after the work closed quietly. Our old beloved Automation came to rescue us from this situation. We wrote a Active X component which can be used to use the access reports from your application.

The components work in two modes, once is user can quietly print a report and no access will be shown on the screen, and in other the access report screen can be maximized and user can see the report in preview mode and if he wants he can print the report from the preview screen.

ATL Component

The component in ATL is called 'AccessAutomation' and to use it from the VB user has to write the following code after he has referenced the component in his application :

Dim sSql As String
Dim aa As New ACCESSAUTOMATIONLib.Access
Dim sDatabaseName = "c:\\Chip\\lab\\lab.mdb"
sSql = " SELECT * from customer where customerID < 1000, "

aa.Initialize sDatabaseName
aa.FixString "QueryNameinAccess", sSql
aa.PrintReport "YourReporName",True
aa.CloseAccess

ATL Code snippet for Print Report

STDMETHODIMP CAccess::PrintReport( BSTR ReportName , BOOL Preview = FALSE) 
{
 AFX_MANAGE_STATE(AfxGetStaticModuleState())
 m_Preview = Preview;     //used in shutting down.
 if (SysStringLen (ReportName) == 0 ) 
  return S_FALSE;

 //Start Automation
 _bstr_t bRptName = SysAllocString (ReportName);

 try {
  LPDISPATCH lDoCommand = app.GetDoCmd ();
  IDoCmd DoCmd (lDoCommand);

  COleVariant vReportName (bRptName);
  COleVariant  Rc ;  COleVariant  Re ;

  if (m_Preview) { //for preview mode
   app.SetVisible (TRUE);  // Show Access
   DoCmd.Restore ();
   DoCmd.Maximize ();

   DoCmd.OpenReport ( vReportName,2L,Rc,Re);      //2L - acPreview  
   LPDISPATCH lRpts = app.GetReports ();
   Reports Rpts (lRpts);
   LPDISPATCH   lRpt = Rpts.GetItem (vReportName);
   _Report rpt(lRpt);
   long hwnd = rpt.GetHwnd ();
   SetFocus ((HWND) hwnd);

   //Remain in loop till report is open  
   do  {
   } while (IsWindow ( (HWND) hwnd ));        rpt.DetachDispatch ();

   Rpts.DetachDispatch ();
  } else {
   app.SetVisible (FALSE);
   DoCmd.OpenReport (vReportName,0L,Rc,Re);
  }
 } catch (...) {
  TRACE("AccessAutomation : Error in Printing %s\n ", (LPCSTR) bRptName);
  return S_FALSE;
 }
 return S_OK;
}    

STDMETHODIMP CAccess::FixQuery( BSTR QueryName, BSTR QueryString )
{
 AFX_MANAGE_STATE(AfxGetStaticModuleState())

 if ( (SysStringLen (QueryName) == 0 )
 || (SysStringLen (QueryString) == 0)) 
  return S_FALSE;   //if not fixing Query No need to come here

 if (m_DatabaseName.Length () == 0 ) 
  return FALSE;

 CDaoDatabase m_database;
 CBstr databaseName( static_cast <_bstr_t> ( m_DatabaseName));
 try {
  if ( SysStringLen (QueryString) > 0 ) {
   char a_database[255];
   lstrcpy (a_database, databaseName);

   if (!m_database.IsOpen())
    m_database.Open(a_database,FALSE,FALSE,_T("")); 

   CDaoQueryDef qdef (&m_database);
   CBstr sQueryName ( static_cast <_bstr_t> ( QueryName));
   qdef.Open ( sQueryName );
   CBstr sSql (  static_cast <_bstr_t> ( QueryString));
   qdef.SetSQL (sSql);
   qdef.Close ();
   m_database.Close ();
   AfxDaoTerm  ();
  }     
 } catch (...) {
  TRACE ("ERROR IN INITIALIZING QUERY %s", QueryName);
  return S_FALSE;
 }
 return S_OK;
}

STDMETHODIMP CAccess::Initialize( BSTR DatabaseName )
{
 AFX_MANAGE_STATE(AfxGetStaticModuleState())

 m_DatabaseName = SysAllocString (DatabaseName);
 
 CBstr dbName( static_cast <_bstr_t> ( m_DatabaseName));

 try {
  if (!app.CreateDispatch ("Access.Application.8")) {
   TRACE ("Could't start the Access Application"); 
   return S_FALSE;
  }
  app.OpenCurrentDatabase  ( dbName  ,  false );
 } catch (...) {
  TRACE ("AccessAutomation : ERROR in opening db" );
  return S_FALSE;
 }
 return S_OK;
}

STDMETHODIMP CAccess::CloseAccess()
{
 AFX_MANAGE_STATE(AfxGetStaticModuleState())

 try {
  if (!m_Preview) 
   app.CloseCurrentDatabase ();

  app.Quit (2L);
  app.DetachDispatch ();  
 } catch (...) {
  TRACE ("AccessAutomation : Error in closing .");
  return S_FALSE;
 }

 return S_OK;
}

Downloads

Download demo project - 60 Kb