Programming Data Access Pages with VBA


You can program solutions with data access pages at several different levels. This section explores solutions based on VBA, the AllDataAccessPages collection, the DataAccessPage object, and selected methods of the DoCmd object. Programmatic manipulation of data access pages by using VBA can serve multiple administrative needs.

Enumerating Data Access Pages and Their Properties

The AllDataAccessPages collection works like the AllForms and AllReports collections. Its members are not database objects, but rather AccessObject objects. These objects are available whether or not a data access page is open . The AllDataAccessPages collection lets you track the full set of all data access pages associated with a database. The following short VBA procedure inventories all the data access pages associated with the CurrentProject object of an Access database file or an Access project, and the procedure notes whether the pages are open.

The FullName property of an AccessObject object in the AllDataAccessPages collection has a special meaning. Recall that pages are not stored as objects in the database file; they are separate HTML files. The location of the pages can be anywhere on a LAN. The FullName property indicates the path and filename for a data access page. The next procedure lists all the pages in a project and itemizes them by their Name and FullName properties. The Name property value is the shortcut for the data access page that appears in the database window.

 SubListMyPages() Dimobj1AsAccessObject     'IteratethroughthemembersoftheAllDataPagescollection. 'Printlinkname,whetheritspageisloaded,and 'theaddressatwhichthelinkpoints. ForEachobj1InCurrentProject.AllDataAccessPages Debug.Print"Linkname:"&obj1.Name Debug.PrintString(5,"")&"Isloaded:"&obj1.IsLoaded Debug.PrintString(5,"")&"Linkaddress:"&obj1.FullName Debug.Print Nextobj1     EndSub 

Because the AllDataAccessPages collection has a limited number of properties, you are likely to need the DataAccessPages collection to get more information about the data access pages associated with a database. While the AllDataAccessPages collection belongs to the CurrentProject object shown in the preceding sample, the DataAccessPages collection belongs to the Application object. In addition to this distinction, data access pages belong to the DataAccessPages collection only when they are open. Therefore, you must open a data access page before you can reference it as a member of the DataAccessPages collection. Since proper applications restore settings to the way they were before their operation, you should close any data access pages that you opened to make them members of the DataAccessPages collection.

The following procedure prints the links, the path and filename for the page links, and the data source specification for selected data access pages. The first two items are available from the AllDataAccessPages collection. However, the last item requires an excerpt from the connection string for a member of the DataAccessPages collection. The data source specification within the ConnectionString property for a DataAccessPages member starts with the string "Data Source" and ends with the first semicolon after "Data Source" . If you create a new copy of a database with the most recent data, it's possible that some of your data access pages will reference an incorrect, older version of that database. You can put the called procedure in the following sample within a loop and go through the data access pages in the CurrentProject object. The previous sample illustrates this type of design. When you have just a few objects to report on, you can speed up your application by referencing just a subset of items with indexes, as the following code illustrates.

Note  

If you change the target files in the first procedure, save it to avoid a file-locking error. The sample needs an exclusive lock on the current database, and it cannot make the lock if uncommitted changes exist.

 'SavemoduleafterupdatingdapNamesettings SubCallPrintDAPConnectionString()  DimdapNameAsString     'Passthenameoftwodataaccesspagelinks 'forlookupoftargetproperties dapName="dapMenuPage" PrintDAPConnectionStringdapName dapName="dapProducts" PrintDAPConnectionStringdapName     EndSub     SubPrintDAPConnectionString(dapNameAsString)  Dimbol1AsBoolean Dimstr1AsString Dimint1AsInteger Dimint2AsInteger     bol1=True     'Iffileisnotloaded,setbol1toFalse IfCurrentProject.AllDataAccessPages(dapName).IsLoaded=FalseThen DoCmd.OpenDataAccessPagedapName,acDataAccessPageBrowse bol1=False EndIf     'Printtargetforpagelinkanddatasourceforpageat 'whichthelinkpoints str1=DataAccessPages(dapName).ConnectionString int1=InStr(str1,"DataSource") int2=InStr(int1,str1,";") Debug.PrintCurrentProject.AllDataAccessPages(dapName).Name Debug.PrintString(5,"")&"Linkpointsat"&_ CurrentProject.AllDataAccessPages(dapName).FullName Debug.PrintString(5,"")&"Datafileforpagepointsat"&_ Mid(str1,int1,int2-int1)     'Iffilewasinitiallyclosed,closeitagain Ifbol1=FalseThen DoCmd.CloseacDataAccessPage,dapName,acSaveNo EndIf     EndSub 

Creating and Deleting Data Access Pages

If you programmed data access pages much with Access 2000, you might have noticed the CreateDataAccessPage method. This method belonged to the Application object, and it enabled the creation of new, blank data access pages as well as new links to existing data access pages. If you look for this method with the Object Browser in Access 2003, you will not see it. The method still exists, but it generates a run-time error. Happily, it's relatively easy to program around the run-time error. Access 2003 retained the acDataAccessPage argument for the DeleteObject method. This method is handy for discarding page links that you no longer need as well as relinking existing pages with new data access pages.

The following pair of VBA procedures shows the new technique for using the CreateDataAccessPage method for making a new data access page file and linking to it in the current project. Recall that a data access page for an Access project has three important characteristics. First, it is a standalone Web page. Second, it must have a data source control that specifies a relationship to a data source. Third, it's the target of a link inside an Access database file or Access project that points at the standalone Web page.

The CreateDataAccessPage method takes two arguments. The first of these is for the path and filename of the standalone Web page. Your specification for this first argument doesn't require an extension. Therefore, you should not end it with .htm. The method automatically appends the correct extension to the file for the data access page. The second argument takes a Boolean variable assignment. An assignment value of True causes the method to create a new data access page file. A value of False is appropriate when you're creating a new link for an existing standalone page. When you invoke the method, it opens a blank data access page in memory. You must provide a memory variable for the return value from the method. Failing to do so generates a compile-time error. You can give the variable an AccessObject data type.

After invoking the CreateDataAccessPage method as described, you will generate a run-time error. In my tests of the method, the most common result was error 13, with a description of Type Mismatch. If I ran the procedure successively without erasing the previously created data access page, I also generated error 2023. This error can occur when your application tries to create a data access page file that already exists.

The sample application that follows includes traps and fixes for both of the errors that I encountered . Other errors are logged to the Immediate window before a normal exit. The sample application sets the filename and path for the data access page in the first procedure. By default, the connection string for the page points at the current project's connection. In addition, the connection string uses letters to denote drives . The second procedure combines the arguments for the filename and the path for the CreateDataAccessPage method and invokes the method. This procedure also includes error traps. One of these traps is for the case in which the application tries to write over an existing data access page. When the application detects this error, it invokes a third procedure to delete the old file and then retries the CreateDataAccessPage method. Notice that the called procedure removes both the page link with the DeleteObject method and the file to which it links with the Kill statement. The other error trap is for the type mismatch error. This is the normal result of trying to create a new data access page with the method. Although the method generates a run-time error, it successfully creates a new page with a data source control set to the database for the current project. The page resides in memory. Therefore, when the second procedure traps this error, it saves the page in memory and closes the page. This completes the task.

After the second (and optionally the third) procedure performs its tasks , control returns again to the first procedure. At this point, the new data access page exists, but it has a connection string specified with driver letters, which may not work well for a community of users that can use the same drive letter to point at different physical drives. The main procedure therefore calls the setUNCSting procedure. This procedure reopens the page and assigns it a connection string with a UNC syntax before resaving and closing the page.

 SubCallNewDataAccessPageForNewPage()  DimdapNameAsString DimdapPathAsString     'Setparametersforpageandlink dapName="dapfoo3" dapPath="c:\inetpub\wwwroot\pma11\"     'Createpagewithdefaultconnectionstring NewDataAccessPagedapName,dapPath     'Callanyintermediateprocessingifdesired     'SetUNCconnection SetUNCStringdapName     EndSub     SubNewDataAccessPage(dapNameAsString,dapPathAsString)  OnErrorGoToNewPageTrap DimdapPathFile Dimdap1AsAccessObject     'ConcatenatepathandfileforCreateDataAccessPagemethod dapPathFile=dapPath+dapName     'Createnewdataaccesspageandwipeoldcopy 'ifitexists Setdap1=CreateDataAccessPage(dapPathFile,True) NewPageExit: 'Thisnormalexitpathisnotstrictlynecessarybecause 'proceduredoesnotendnormally ExitSub     NewPageTrap: IfErr.Number=2023Then 'Deleteoldversionofpage,ifitalreadyexists DeleteDapdapName,dapPath Resume ElseIfErr.Number=13Then 'Savepageandclosepage,tofinishmethod DoCmd.RunCommandacCmdSave 'debug.Printcurrentproject.dataaccesspages(dapName). Else 'Printouterrornumberanddescriptionotherwise Debug.PrintErr.Number,Err.Description EndIf EndSub     SubDeleteDap(dapNameAsString,dapPathAsString)  DimdapPathFile DimdapAsAccessObject     'Specifyfiletodeletewithextension dapPathFile=dapPath+dapName+".htm"     'FirstdeletelinkinDatabasewindow DoCmd.DeleteObjectacDataAccessPage,dapName     'Nextkillfilefordataaccesspage KilldapPathFile     EndSub     SubSetUNCString(dapName)     'Opentargetpage DoCmd.OpenDataAccessPagedapName,acDataAccessPageBrowse     'Re-writeconnectionstringwithUNCsyntax 'topointatChapter14.mdbintheChapter14path 'onthecabSony1computer str1=DataAccessPages(dapName).ConnectionString int1=InStr(str1,"DataSource") int2=InStr(int1,str1,";") DataAccessPages(dapName).ConnectionString=_ Left(str1,int1-1)&_ "DataSource=\cabSony1\Chapter14\Chapter14.mdb"&_ Right(str1,Len(str1)-int2+1)     'Saveandclosepage DoCmd.RunCommandacCmdSave DoCmd.Close     EndSub 

Basing Data Access Pages on Access Forms and Reports

Data access pages were a popular new feature back when I was explaining the benefits of Access 2000 in the seminars I gave throughout the United States. The attendees told me they liked data access pages because they had the look and feel of Access forms and reports. Data access pages were viewed as relatively familiar and easy to develop. Perhaps the most common question asked by seminar registrants was, "Is there any automatic way to base a data access page on an existing Access form or report?" At the time, when only Access 2000 was available, the answer was no ”however, it changed to yes when Access 2002 was introduced, and Access 2003 offers the same benefit. Although the conversion process is not perfect in every case, it's always automatic.

The process for automatically creating a data access page works for both Access forms and reports. In addition, you can do so both manually and programmatically. To perform the process manually, select either an Access form or report in the Database window and choose File, Save As. Access responds by opening a Save As dialog box. In the drop-down list control on the dialog box, select Data Access Page. In the New Data Access Page dialog box, designate a folder and name for the new data access page based on the form or report. Then click OK to save the data access page in memory to disk.

You can achieve results comparable to the File, Save As command by using the DoCmd object's OutputTo method. Using the programmatic solution to base data access pages on forms and reports has a couple of advantages over the manual technique. First, the programmatic approach makes it easy to process sets of forms and reports to data access pages in one step. Second, by learning to use the programmatic solution, you can offer this capability to users of your custom solutions.

When outputting a data access page based on a form or a report, you will typically specify as many as four arguments for the OutputTo method. The first argument is an intrinsic constant for either a form ( acForm ) or a report ( acReport ), depending on the type of Access object on which you base your data access page. The second argument is a string denoting the name of the Access form or report that serves as the basis for the new data access page. The third argument is another intrinsic constant ( acFormatDAP ) to specify that the method will generate a data access page. This is necessary because the OutputTo method can generate many different types of output, including text files, Excel workbooks, ASP, XML files, and more. The fourth argument represents the name of the path and file that will store the new data access page.

When you create a data access page with the OutputTo method, you're responsible for archiving any existing files that have the same name as your new data access page. If you specify a name for your new page that's identical to one that already exists in the same path, Access silently copies over the old file. You're also responsible for creating a link in the project that points at the file for the new data access page. This sample points a link at a new page. If you already have a link pointing at the new data access page based on the page's filename, Access creates another link with the same name, except it appends an underscore and a sequential number to the link's name. You can use manual or programmatic techniques to ensure that the new link is correct. For example, you can delete the old link if it exists.

To demonstrate automating the creation of a data access page based on an Access form, I used the AutoForm Wizard to generate an Access form named frmOrderDetails for the Order Details table. This form is available in the Access database file for this chapter (Chapter14.mdb). Next, I ran the following procedure to create a data access page based on the form and a link to the new page. The procedure has three parts . The first part creates the new page with the OutputTo method. The second part creates a link for the page in the Database window for the Access database file. The third part uses the link to open the page based on the form and changes its connection string to a UNC style before saving and closing the page.

 SubdapfrmOrderDetails() DimdapAsAccessObject DimdapPathAsString     'CreateanewdataaccesspageindapPathbasedon 'thefrmOrderDetailsform dapPath=_ "c:\inetpub\wwwroot\pma11\dapfrmOrderDetails.htm" DoCmd.OutputToacForm,_ "frmOrderDetails",_ acFormatDAP,_ dapPath     'Thiscodecreatesalinkinthecurrentprojecttothe 'dataaccesspageatdapPath OnErrorResumeNext Setdap=_ CreateDataAccessPage(dapPath,False) IfErr.Number=13Then DoCmd.RunCommandacCmdSave DoCmd.Close EndIf     'SetUNCconnection SetUNCString"dapfrmOrderDetails"     EndSub 

Figure 14-18 shows the original Access form and its data access page representation inside an Access window. The original form in the upper-left portion of the screen translates perfectly into a data access page. There are even some extra touches in the lower right corner. For example, both the original form and its representation on a data access page have a record selector with the same kind of functionality. You can also view the total number of records in both forms and move among them with navigator controls. The data access page offers enhanced functionality, such as filtering and sorting. However, you can programmatically or manually restrict the availability of these capabilities to the form on the data access page.

click to expand
Figure 14.18: Automatically generated data access pages can have the same look and feel as Access forms.

Although this approach is promising , it has a couple of limitations. First, a subform in a main/subform combination doesn't get translated; the OutputTo method generates just the main form. Second, bound graphic images do not translate from Access forms to their data access page counterparts. You can use the technique discussed in the "Managing Images on a Data Access Page" section earlier in this chapter to display images tied to data values for forms generated automatically by the OutputTo method.

As mentioned in Chapter 6, you can represent parent-child data relationships with nested sections in Access reports. This lets you display the same kind of information found in a main/subform. Figure 14-19 shows an excerpt from the report rptProductsWithinCategories . As you can see, the report lists products within categories. For each category, the report shows the UnitsInStock and ReorderLevel field values.

The following VBA procedure shows how to convert the report shown in Figure 14-19 into an interactive report on a data access page. As you can see, the changes are minor in relation to the earlier sample for converting a form to a data access page. The code still must perform three main functions. Access automatically recognizes the source object as a form and the nesting in the original report. With the same basic syntax, Access adjusts to the input changes and creates a new output in the style of an interactive report.

click to expand
Figure 14.19: An Access report with a nested relationship for showing how the OutputTo method can generate a data access page with an interactive report style.
 SubdaprptProductsWithinCategories() DimdapAsAccessObject DimdapPathAsString     'CreateanewdataaccesspageindapPathbasedon 'therptProductsWithinCategoriesreport dapPath=_ "c:\inetpub\wwwroot\pma11\"&_ "daprptProductsWithinCategories.htm" DoCmd.OutputToacReport,_ "rptProductsWithinCategories",_ acFormatDAP,_ dapPath     'Thiscodecreatesalinkinthecurrentprojecttothe 'dataaccesspageatdapPath OnErrorResumeNext Setdap=_ CreateDataAccessPage(dapPath,False) IfErr.Number=13Then DoCmd.RunCommandacCmdSave DoCmd.Close EndIf     'SetUNCconnection SetUNCString"daprptProductsWithinCategories"     EndSub 

Figure 14-20 shows a slightly edited version of the automatically created interactive report based on the report in Figure 14-19. Notice that the report uses a caption area above the data values to label the values for products within a category. This caption area is a special kind of header section, just like those discussed in the "Getting Started with Forms on Data Access Pages" section and elsewhere throughout this chapter. As attractive and efficient as this report is, it could benefit from some of the techniques demonstrated for computed fields earlier. I moved the category name legend in Design view so that it does not overlap with the Expand control (the plus sign [+]) on the interactive report. I also added meaningful labels to the two navigator bars. You can do this by selecting the descriptor in each bar and then updating its RecordsetLabel property on the Data tab. It takes at least two clicks to select the label for a navigator bar. First, you select the bar. Then you select the label on the bar. Revise the RecordsetLabel property on the Data tab of the label's Properties dialog box. After making these and any other changes you want, you can save the interactive report just like any other data access page.

click to expand
Figure 14.20: A slightly edited interactive report based on the Access report in Figure 14-19.



Programming Microsoft Office Access 2003
Programming MicrosoftВ® Office Access 2003 (Core Reference) (Pro-Developer)
ISBN: 0735619425
EAN: 2147483647
Year: 2006
Pages: 144
Authors: Rick Dobson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net