Microsoft KB Archive/266387

= How to find and to list Access VBA procedures by using MFC =

Article ID: 266387

Article Last Modified on 5/28/2007

-

APPLIES TO


 * Microsoft Office Access 2007
 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition
 * Microsoft Access 2000 Standard Edition
 * Microsoft Access 97 Standard Edition
 * Microsoft Visual C++ 6.0 Professional Edition
 * Microsoft Foundation Class Library 4.2

-



This article was previously published under Q266387



SUMMARY
The utility program described in this article provides the developer with a tool to list the Visual Basic for Automation (VBA) routines stored in a Microsoft Access database (*.mdb or *.accdb). This tool provides a means for auditing databases and for locating VBA code that can be copied to other databases.

This utility uses Automation to Microsoft Access to open the database. Therefore, it does not prevent auto macros from running. The utility lists the names of Sub or Function procedures in VBA modules; it does not list macros.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

The following steps demonstrate how to build a sample program that lists the VBA procedures in the Northwind.mdb or Northwind.accdb Access database:  Start Microsoft Visual C++, and then start a new MFC AppWizard .exe project named NameProcedures. In step 1 of the wizard, select the Dialog based application type, and click Finish to accept all other defaults. The new dialog box should be displayed in the edit area of the project. If it is not, click the ResourceView tab of the workspace, expand the Dialog folder, and then double-click IDD_NAMEPROCEDURES_DIALOG to open the dialog box If the Controls tool box is not visible, right-click on a blank area of the Visual Studio menu bar, and choose Controls from the drop-down menu. Click the List Box control on the Controls tool box and then drag the list box to the desired shape (approximately 1.5&quot; x 2.5&quot;) and location on your dialog box. Right-click the List box control and choose Properties. Examine the ID to be sure it is IDC_LIST1. Right-click the OK button on the dialog box, and choose Properties from the context menu. Change the button's ID to IDRUN and its caption to List VBA Procedures. Double-click the List VBA Procedures button. When you are prompted to add a new member function for the BN_CLICKED message, click OK and an empty CNameProceduresDlg::OnRun function is added to NameProceduresDlg.cpp. On the View menu, click ClassWizard (or press the CTRL+W keyboard combination).</li> Select the Member Variables tab of the ClassWizard. Verify that the selected project is NameProcedure and that the selected class is CNameProceduresDlg. In the list of control IDs, select IDC_LIST1, and then click Add Variable. When you are prompted, specify m_iList for the variable name, Control for the category, and CListBox for the variable type. Click OK to add the new member variable.</li> Select the Automation tab of the ClassWizard. Click Add Class and then choose From a type library. Browse to locate the Microsoft Access object library (Msacc8.olb for Microsoft Access 97, Msacc9.olb for Microsoft Access 2000, or Msacc.olb for Microsoft Access 2002, for Microsoft Office Access 2003, or for Microsoft Office Access 2007). Select all of the interfaces shown in the list and click OK.

Note The default folder for the Access .olb file is:

C:\Program Files\Microsoft Office\Office for Access 2000.

C:\Program Files\Microsoft Office\Office10 for Access 2002.

C:\Program Files\Microsoft Office\Office11 for Access 2003.

C:\Program Files\Microsoft Office\Office12 for Access 2007.

</li> Repeat the procedure for adding Automation classes for DAO.

Note Use the version of DAO that works with your version of Access (DAO 3.5 for Access 97 or DAO 3.6 for Access 2000, Access 2002, Access 2003, or Access 2007). The default folder for DAO is:

C:\Program Files\Common Files\Microsoft Shared\DAO

</li> Click OK to close the ClassWizard.</li> For each of the interfaces of the two object libraries, the ClassWizard creates COleDispatchDriver wrapper classes, which you can see displayed on the Class View page of the Visual Studio workspace. The Access and DAO type libraries each have an interface called Properties. To avoid a conflict between these two interfaces, use the namespace keyword for the Access wrapper classes: <ul>  Open the header file msacc9.h (msacc.h, or msacc8.h) and add the following code at the top of the file: namespace Access{ At the end of the file, add a closing curly brace ( } ).

</li>  Open the source file msacc9.cpp (msacc.cpp, or msacc8.cpp) and add the following code after the last #include: using namespace Access; </li></ul> </li>  Locate CNameProceduresApp::InitInstance in NameProcedures.cpp and add the following code to the beginning of that function: if(!AfxOleInit) {       AfxMessageBox(&quot;Unable to initialize COM&quot;); return FALSE; }                   </li>  In NameProceduresDlg.cpp, add the following code after the last include.

For Access 2002, for Access 2003, or for Access 2007, add: For Access 2000, add: For Access 97, add: </li>  Locate the empty CNameProceduresDlg::OnRun function in NameProceduresDlg.cpp. Replace that entire procedure with the following code.
 * 1) include &quot;msacc.h&quot;
 * 2) include &quot;dao360.h&quot;
 * 1) include &quot;msacc9.h&quot;
 * 2) include &quot;dao360.h&quot;
 * 1) include &quot;msacc8.h&quot;
 * 2) include &quot;dao350.h&quot;

Note To obtain Northwind 2007, visit the following Microsoft Web site:

http://office.microsoft.com/en-us/templates/TC012289971033.aspx

HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...) { // Begin variable-argument list... va_list marker; va_start(marker, cArgs); if(!pDisp) { MessageBox(NULL, &quot;NULL IDispatch passed to AutoWrap&quot;, &quot;Error&quot;, 0x10010); _exit(0); }  // Variables used.

DISPPARAMS dp = { NULL, NULL, 0, 0 }; DISPID dispidNamed = DISPID_PROPERTYPUT; DISPID dispID; HRESULT hr; char buf[200]; char szName[200]; // Convert down to ANSI. WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL); // Get DISPID for name passed. hr = pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID); if(FAILED(hr)) { sprintf(buf, &quot;IDispatch::GetIDsOfNames(\&quot;%s\&quot;) failed w/err 0x%08lx&quot;, szName, hr); MessageBox(NULL, buf, &quot;AutoWrap&quot;, 0x10010); _exit(0); return hr; }  // Allocate memory for arguments. VARIANT *pArgs = new VARIANT[cArgs+1]; // Extract arguments... for(int i=0; i<cArgs; i++) { pArgs[i] = va_arg(marker, VARIANT); }  // Build DISPPARAMS. dp.cArgs = cArgs; dp.rgvarg = pArgs; // Handle special-case for property-puts. if(autoType & DISPATCH_PROPERTYPUT) { dp.cNamedArgs = 1; dp.rgdispidNamedArgs = &dispidNamed; }  // Make the call. hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, NULL, NULL); if(FAILED(hr)) { sprintf(buf, &quot;IDispatch::Invoke(\&quot;%s\&quot;=%08lx) failed w/err 0x%08lx&quot;, szName, dispID, hr); MessageBox(NULL, buf, &quot;AutoWrap&quot;, 0x10010); _exit(0); return hr; }  // End variable-argument section. va_end(marker); delete [] pArgs; return hr; }

void CNameProceduresDlg::OnRun {

using namespace Access;

//The path to the Northwind database. Be sure to modify this to point to  //the northwind.mdb or northwind.accdb file (or nwind.mdb file) on your system char* pszDatabasePath = &quot;C:\\Program Files\\Microsoft Office\\Office12\\Samples\\northwind 2007.accdb&quot;;

//These variables use the Class-Wizard generated wrapper classes to automate //Access and DAO. _Application app; Database db; Containers containers; Container container;

Documents documents; Document document; long documentcount; long i;  Modules modules; Module module; CString documentname;

//General purpose buffer char buf[512];

//Not all arguments are required for automation methods. This COleVariant //is useful for filling in optional parameters. COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); // Start Access and get Application object... if(!app.CreateDispatch(&quot;Access.Application&quot;)) {

AfxMessageBox(&quot;Couldn't start Access.&quot;); return; }

//Open a database.

//app.OpenCurrentDatabase(pszDatabasePath, true); app.OpenCurrentDatabase(pszDatabasePath, true, &quot;&quot;); // Access 2002 and Office Access 2003 // requires // password argument

//Obtain a reference to the containers collection of the open database. db = app.CurrentDb; containers = db.GetContainers; //Code to get the LPDISPATCH for the &quot;Modules&quot; item assigned to an Access //Container object variable {     VARIANT rVal = {0}; VARIANT name = {0}; name.vt = VT_BSTR; name.bstrVal = ::SysAllocString(L&quot;Modules&quot;); //Occassionally, a method cannot be accessed through the wrapper classes that are //generated by the Class-Wizard. In these cases, we can use the COM //IDispatch interface to access the method or property. AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &rVal,         containers.m_lpDispatch, L&quot;item&quot;, 1, name); container.AttachDispatch(rVal.pdispVal); VariantClear(&name); }  //Obtain the count of the &quot;documents&quot; or pages of code in the database. documents = container.GetDocuments; documentcount = documents.GetCount;

if( 0 != documentcount) {     //Loop through the documents. for( i = 0; i < documentcount; i++) {        {            // Code to assign the loop-counter to the &quot;Item&quot; property of the //Documents collection VARIANT rVal = {0}; VARIANT name = {0}; name.vt = VT_I4; name.lVal = i;           AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &rVal,                documents.m_lpDispatch, L&quot;item&quot;, 1, name); document.AttachDispatch(rVal.pdispVal); }        //Obtain the name of the document. documentname = document.GetName; // The module must be open before we can see its code. DoCmd docmd = app.GetDoCmd; //Access2000 has the DoCmd interface //IDoCmd docmd = app.GetDoCmd; //Access97 has the IDoCmd interface docmd.OpenModule(COleVariant(documentname), covOptional);

//Obtain the number of lines in the module we want. modules = app.GetModules; module = modules.GetItem(COleVariant(documentname)); long linecount = module.GetCountOfLines;

//STRATEGY: There is no list of procedures in a module, but we can //find out which procedure a given line belongs to, and we can find //out how many lines a given procedure has, so we can work through a        //file to find all the procedure names.

// Set the counter for the next line if not a sub or a function. for ( long linenumber = 1; linenumber <= linecount; linenumber++) {           long nProcType = 0;  //0 = vbext_pk_Proc, Sub or Function

//assign address of long containing type to the pointer CString procname = module.GetProcOfLine(linenumber, &nProcType);

//If IsEmpty is false, this is a real procedure. if (false == procname.IsEmpty) {              //Write the name of the procedure into a buffer, along with the //module that contains it. sprintf(buf,&quot;%s.%s&quot;, documentname, procname);

//Add the procedure name to the list box

m_iList.AddString(buf);

//Move the line number to the end of the procedure. linenumber = --linenumber + module.GetProcCountLines(procname, nProcType); } // end if        }  // end for } // end for ( e = 0; i < documentcount;...  }  // end if(0!= documentcount...   //Shut down the instance of Access that we launched. app.Quit(0); } // end OnRun function Note Modify the code to reflect the correct path for Northwind.mdb or Northwind.accdb for your Office installation. The code is written for compatibility with Access 2000, Access 2002,Access 2003, and Access 2007. If you are using Access 97, see the remarks in the code for using IDoCmd instead of DoCmd. </li> Build the project and run it. Click List VBA Procedures and note that the sample code launches Access, quickly enumerates through its modules, and then closes. Also note that there is a list of VBA macros in the list box in your application.</li></ol>

<div class="references_section">