CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
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 %sn “, (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 hereif (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;
}
