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


Comments

  • Reports from VB

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

    Originally posted by: maeve

    Hi,
    Does anyone know I can open a access report from within a Vb application.
    Thanks

    Reply
  • how can i access the MS access database using SQL through COM (using VC++)

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

    Originally posted by: prabath

    hi 
    
    

    i want to access the MS access databases using COM and also i want to know how can i write SQL statement within the COM module

    thank you
    prabath

    Reply
  • Secured database

    Posted by Legacy on 03/21/2003 12:00am

    Originally posted by: Peter Mortier

    Hello,
    
    

    I have tried some of this code and it works fine on
    non-secured databases, but if a database is secured
    with a password, when calling the OpenCurrentDatabase
    method, Access comes up with a dialog box asking for a
    password.

    Is there any way on avoiding to fill in any password?

    Microsoft has shown a way on how to do this in Visual Basic
    (Microsoft knowledge base article 235422 HOW TO: Open a
    Password-Protected Database Through Automation in Access
    2000).
    http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B235422
    Is this also possible in Visual C++?

    Best regards,
    Peter

    Reply
  • Making Setup from vb using Vc++ dll

    Posted by Legacy on 01/15/2003 12:00am

    Originally posted by: Majed

    I make a Programm in vb using DAO and VC++ DLL, i made a setup.EXE, and istalled it on my computer it's running (WIN 98), but wenn i istall it under WIN_NT, it dosent work, there is allways Error " cant find VCI.DLL ", but the VCI.DLL is installed in System32, but he cant find it.
    What can i do.

    Reply
  • Access Report to Crystal Report converter.

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

    Originally posted by: bhavesh

    Can u help me for Access Report to Crystal Report converter.

    i want to convert all reports made in ms access, convert all reports in crystal report.

    Reply
  • How to use in VC ?

    Posted by Legacy on 08/07/2002 12:00am

    Originally posted by: Hermann

    Hi. After download and reading your article, I tried to test your demo project. But -
    How to do ? Your demo project creates a DLL !? You discribed the way to use in VB. Can you also tell me, how to use in VC ?
    Thank you.

    Reply
  • How can I generate reports in VC++

    Posted by Legacy on 06/20/2002 12:00am

    Originally posted by: Prateeti

    You discribed the way to use in VB. Can you also tell me, how to use in VC ?
    Thank you.

    Reply
  • No component files ??

    Posted by Legacy on 04/09/2002 12:00am

    Originally posted by: TimmEH

    Hello,

    When I try to add a component to vb, I cannot find any OCX files from the zip file you've provided.

    What am I doing wrong?

    Cheers,


    Tim

    Reply
  • How can i see a report from access in VC++ using ADO?

    Posted by Legacy on 01/20/2002 12:00am

    Originally posted by: Nuno Rapaz

    How can i see a report from access in VC++ using ADO?
    I don't no very well how reports do, but what i want is to execute querys from VC++ in reports from access.
    I can do it in tables but i don't no if it's possible to do it in reports.

    thanks

    Reply
  • Wont compile using VCPP 6.0

    Posted by Legacy on 01/16/2002 12:00am

    Originally posted by: Michael B. Pliam

    The download wont compile for me using VCPP 6.0. The following error messages are generated:

    Compiling...
    Access.cpp
    C:\Program Files\Microsoft Visual Studio\MyProjects\database\XAccess\Chip\NewChip\AccessAutomation\Access.cpp(79) : error C2664: 'lstrcpyW' : cannot convert parameter 1 from 'char [255]' to 'unsigned short *'
    Types pointed to are unrelated; conversion requires reinterpret_cast, C-style cast or function-style cast
    C:\Program Files\Microsoft Visual Studio\MyProjects\database\XAccess\Chip\NewChip\AccessAutomation\Access.cpp(81) : error C2664: 'Open' : cannot convert parameter 1 from 'char [255]' to 'const unsigned short *'
    Types pointed to are unrelated; conversion requires reinterpret_cast, C-style cast or function-style cast
    C:\Program Files\Microsoft Visual Studio\MyProjects\database\XAccess\Chip\NewChip\AccessAutomation\Access.cpp(93) : error C2664: 'int __thiscall COleDispatchDriver::CreateDispatch(const struct _GUID &,class COleException *)' : cannot convert paramet
    er 1 from 'char [21]' to 'const struct _GUID &'
    Reason: cannot convert from 'char [21]' to 'const struct _GUID'
    No constructor could take the source type, or constructor overload resolution was ambiguous
    C:\Program Files\Microsoft Visual Studio\MyProjects\database\XAccess\Chip\NewChip\AccessAutomation\Access.cpp(94) : error C2664: 'AfxTrace' : cannot convert parameter 1 from 'char [37]' to 'const unsigned short *'
    Types pointed to are unrelated; conversion requires reinterpret_cast, C-style cast or function-style cast
    AccessAutomation.cpp
    msacc8.cpp
    ustring.cpp
    Generating Code...
    Error executing cl.exe.

    AccessAutomation.dll - 4 error(s), 0 warning(s)

    What am I doing wrong?

    Reply
  • Loading, Please Wait ...

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

Top White Papers and Webcasts

  • Live Event Date: November 20, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Are you wanting to target two or more platforms such as iOS, Android, and/or Windows? You are not alone. 90% of enterprises today are targeting two or more platforms. Attend this eSeminar to discover how mobile app developers can rely on one IDE to create applications across platforms and approaches (web, native, and/or hybrid), saving time, money, and effort and introducing apps to market faster. You'll learn the trade-offs for gaining long …

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds