Database developers need to convert applications from Microsoft Access 1.1, 2.0, and even Access 95 to Visual Basic 4.0. There are a variety of reasons for migrating (also called porting) an Access database application to Visual Basic
4.0—reasons that are discussed in prior chapters of this book. The primary incentive for migrating Access applications to Visual Basic counterparts is reduction in system resource requirements and, with Access 95, execution speed. Whatever your motive
might be to migrate the application, you need to convert most Access macro actions to Visual Basic event-handling subprocedures and deal with reserved words or methods, such as SysCmd and DoCmd, that are recognized by Access's source code interpreter but
not by Visual Basic 4.0. Access also has a rich set of events triggered by update operations on bound controls, forms, and reports; many of these events are not supported by Visual Basic 4.0's Data control and controls bound to Data controls.
Access 1.x and 2.0 employ Access Basic, a precursor of VBA. Use of Access Basic code in versions 1.0 and 1.1 was restricted to modules; you assigned events (called event properties) to event-handling functions contained in modules. Access
2.0 introduced code behind forms (CBF), which provided conventional, Visual Basic–like Sub ControlName_EventName. . .End Sub event handlers stored in form and report containers. Access 95 replaced Access Basic with an Access
dialect of 32-bit VBA that's almost identical to the Visual Basic 4.0 VBA flavor. Converting to Visual Basic "developer-style" Access applications that use Access Basic became progressively easier as Microsoft released new versions of Access.
For better or worse, the most common method of responding to events in simple Access applications is to use one or more of Access's 40-plus macro instructions. Macros, rather than Access Basic or VBA procedures, are the default event-handlers of
Access. Microsoft added an application-specific macro language to Access at the same time that the firm's programmers were working hard to supplant specialized macro languages in Microsoft's other mainstream applications with dialects of VBA. The goal of
adding macros to Access was to create a desktop relational-database development environment for Windows that did not require writing code. Microsoft almost achieved its goal, at least if you don't consider complex macros with conditional execution and
looping statements to be "code." The missing element is the IsLoaded("FormName") function, which almost every Access application needs. An example of Visual Basic code that duplicates the Access Basic IsLoaded() function is
included in this chapter.
One of the primary difficulties with porting Access 1.x and 2.0 applications to Visual Basic 3.0 was the lack of security management features for Access .MDB files in Visual Basic 3.0's implementation of its connection to the Jet 1.x and
2.x Data Access Object. With Visual Basic 4.0, you still cannot create a Jet 1.x or 2.x SYSTEM.MDA system file, or a Jet 3.0 System.mdw workgroup file. However, if you have a 16-bit SYSTEM.MDA or 32-bit System.mdw file created with
Access, Visual Basic 4.0 now lets you add and delete groups and users, and assign permissions for the Access objects that Visual Basic supports using either the Jet 2.5 or 3.0 data access object.
This chapter begins with a discussion of how you convert Access applications that are based primarily on macros as event handlers. The majority of Access 1.x applications use macros to control the execution of Access applications in response to
user- or application-generated events. Use of Access Basic event handlers became more common with the release of Access 2.0. Later in the chapter, an example of converting a code-intensive Access 2.0 application to its counterpart in Visual Basic is
provided.
Before you begin to convert Access macros or Access Basic/VBA code to Visual Basic 4.0, you need to determine how to structure your application. Although this chapter is devoted primarily to importing Access Basic code into Visual Basic subprocedures
that serve as event-handlers and into .BAS modules, your migration strategy can affect how you go through the importation process. The sections that follow discuss the use of Visual Basic MDI forms to duplicate the windowing environment of Access and how
to duplicate Access menus that are generated by applying the AddMenu macro action.
[VB4_NEW]Converting 16-bit Access 1.x or 2.0 applications to 32-bit Access 95 is a one-way process. In a 100-percent 32-bit environment, upgrading to Access 95 isn't a problem; however, it's not likely that the majority of firms presently
running Windows 3.1+ will upgrade to 32-bit Windows on the client side until late 1996 or even 1997. If you're faced with supporting Access applications in a mixed 16-bit and 32-bit environment, you must deal with two separate code bases—one for
Access 2.0 and the other for Access 95. Visual Basic 4.0's conditional compilation feature lets you use the same source code for both 16-bit and 32-bit applications. Even if you don't presently need to create 32-bit database applications, the ability to
create simultaneously production 16-bit and preproduction 32-bit versions of your database applications smoothes the future transition to operation under Windows 95 or Windows NT.
Maintaining an identical Visual Basic 4.0 source code base for both 16-bit and 32-bit applications requires that you use in your application only controls and OLE DLLs that are available in both 16-bit and 32-bit versions. Make sure not to include unneeded references to 32-bit only controls, such as Msrdo32.dll and Msrdc32.ocx. (You must delete these two references from the autoload file, Auto32ld.vbp, if you're using the Enterprise Edition of Visual Basic 4.0 and you write your applications with the 32-bit version.)
You must continue to use the Access 2.x .MDB file format for objects that are shared by Access 2.0 and Access 95 or 32-bit Visual Basic applications using Jet 3.0. If you want to compare the performance of Jet 3.0 .mdb and 2.x files with
a common application, you must modify your Data controls to open the appropriate database version when the form loads. Delete the entry for the DatabaseName property of the Data control(s) in the properties sheet, then assign the appropriate value in the
Form_Load event handler, as in the following example from Chapter 13's OLTPGrid application:
Private Sub Form_Load() 'Set the Database name for dtcForm and dtcFind #If Win32 Then 'Use the Jet 3.0 version dtcForm.DatabaseName = "biblio95.mdb" dtcFind.DatabaseName = "biblio95.mdb" #Else 'Use the Jet 2.5 version dtcForm.DatabaseName = "biblio.mdb" dtcFind.DatabaseName = "biblio.mdb" #End If dtcForm.Refresh dtcFind.Refresh End Sub
Access database applications use MDI child windows to display all Access forms, as well as other visible objects (such as the Database window and datasheet views of Table objects) and the query-result sets returned by precompiled QueryDef or ad hoc
queries, as well as the design views of all Access database objects. Virtually all modern Windows productivity applications with any degree of complexity use an MDI parent window together with as many MDI child windows as are necessary to accomplish the
objectives of the application and the user. Microsoft encourages the use of the single-document interface (SDI) for Windows 95 applications, but all of the main members of 32-bit Microsoft Office 95 retain their original MDI design.
You don't need to use MDI forms for a simple data-entry application. MDI is overkill for single-purpose, transaction-processing applications, which might employ a few modal dialogs for input prompting or pick list selection. Unless you specifically
need to emulate Access's MDI approach to maintain consistency with the Access application you're porting, loading and unloading the required Visual Basic forms conserves scarce system resources. If your application needs more "snap" in the forms
department, you can Hide and Show the forms.
Whatever approach you take, you need a written inventory of forms and controls used in the Access application. You can use the Database Analyzer add-in library (supplied with Access 1.x), Access 2.0's Database Documentor, or Access 95's
Documenter add-in to create a report that lists the forms in your Access application, together with a list the control objects contained in each of the forms. You'll have to create your own Access reports or use Crystal Reports to print the lists if you
use Access 1.1's ANALYZER.MDA. A better approach is to use FMS Inc.'s Total Access documenting application to create detailed listings of all the database objects of your Access application. Using Total Access is described briefly in Chapter 25,
"Documenting Your Database Front-Ends."
Whether you choose to use Analyzer/Documentor, acquire Total Access, or use a yellow pad and pencil, you need to create a written inventory of the objects of the Access application, especially of those objects that are accessible with Visual Basic 4.0's control repertoire. Although you can open Access databases, tables, and QueryDef objects in Visual Basic, you cannot open Access forms, reports, macros, or modules.
Visual Basic does not support form names that contain spaces or other punctuation symbols, with the exception of the underscore. The documentation and sample databases that accompany Access 1.x, 2.0, and 95 encourage the use of spaces in form
and report names, as well as the names of other Access database objects. Visual Basic can handle spaces in the names of Table and QueryDef objects, as well as in the names of database fields, but this is not a recommended practice. Names with spaces or
illegal punctuation characters can be referenced either as literals enclosed with double-quotation marks ("Table Name") or as object references enclosed in square brackets ([Field Name]). You must change Access form names that
include spaces or special punctuation symbols to proper Visual Basic object names.
If you or the author of the application have complied with the Leszinsky Naming Conventions (LNC) for Access variable and object names, you don't need to worry about renaming your forms. The LNC for Access variable and object names corresponds, with few exceptions, to the original Microsoft object-naming conventions that appear in the Visual Basic 3.0 Programmer's Guide. The Microsoft developers who write the sample applications for Visual Basic don't consistently follow the firm's own naming conventions, often preferring to use a prefixed My tag instead of an appropriate control or data type tag when naming objects and variables. The expanded Leszinsky Naming Conventions for Visual Basic 4.0 appears as Appendix B of this book, "Naming Conventions for Visual Basic Objects and Variables."
Menu macros are the easiest of all Access macros to convert to Visual Basic 3.0. You can't execute the AddMenu action with the Access Basic DoCmd statement or the DoCmd object of Access VBA, so converting Access menu macros to Visual Basic menus
embedded in forms is discussed in this section of the chapter rather than in the next section, which deals directly with code conversion. To make the conversion, just duplicate the menu structure defined by the hierarchy of macros that employ the AddMenu
action with entries in Visual Basic's Menu Design window for each of the forms of your Access application that you have reconstructed as Visual Basic forms. Unlike Access, which requires a RunCode macro action to call a Access Basic or Access VBA function
from a menu macro, Visual Basic creates the MenuName_Click event-handler subprocedure stubs for you automatically.
One reason for using Visual Basic MDI forms is that the menus you add to MDI child forms behave exactly as the menus you create with Access menu macros that you activate with the OnMenu event of Access forms. If the Access application doesn't use its
own set of macros, you can add a minimal menu structure to the MDI parent form. A minimal menu structure consists of the following elements:
If a help file is available for your Access application and you duplicate the structure of the Access application with your Visual Basic derivative, you can use the same help file. Like Access forms, each Visual Basic form and control has its own HelpContextID property. Access enables you to assign different values to the HelpFile property of each form. In Visual Basic, HelpFile is a property of the application (and of the common dialogs). If your Access application uses multiple help files, you can call the WinHelp API function (WinHelpA for 32-bit applications) in your VBA code to determine the specific help file for a form. It's usually easier to call the WinHelp or WinHelpA function than to re-create a single help file from a collection. You may find that HelpContextID values are duplicated in the combined files.
The menus you add to MDI child forms replace the default menu you add to the MDI parent form while the child form is active. Therefore, you need to duplicate the basic menu of your application or the minimal menu structure described in the preceding
list in each MDI child window. The event-handling code for the submenu choices of basic or minimal menus is likely to be the same, no matter which child window is active. Thus, you call a subprocedure that processes a basic or minimal menu choice in a
Visual Basic module. The event handlers common to a particular menu choice that is used in all the MDI child forms call the same menu subprocedure.
Access applications that rely primarily on macros for event handling require more effort to convert to Visual Basic than applications that use Access Basic or VBA code for event handling. You must translate macros into Visual Basic event-handling code.
Most Access Basic and VBA functions import into Visual Basic modules with only a few alterations. The sections that follow provide a cross-reference between Access and Visual Basic events, and list the Visual Basic methods and properties that correspond to
Access macro actions.
Access 95 includes a macro to VBA code converter that aids in migrating Access applications to Visual Basic. You can use the Tools | Database Utilities | Convert Database command to create an Access 95 version of databases created with prior versions of Access; you can then open each form in design view and choose Tools | Macros | Convert Form's Macros to Visual Basic (for Applications). After you've made the conversion, you can copy all of the code behind an Access form to a Visual Basic 4.0 form module in a single Clipboard operation. This is one of the benefits of the Full Module View option of VBA.
Visual Basic 4.0 has a broader repertoire of general-purpose events than even Access 95, but Access offers finer granularity in events that are related to forms and controls bound to database objects. Granularity is a term used by programmers to
define the degree of precision of control (sometimes called resolution) that you can achieve with a language's code. (Granularity is similar to the term graininess that is used in photography to describe the size of the grains of silver
halide that form the image; the grain size determines the potential sharpness or acuity of an image.) Microsoft refers to Access events as properties in the documentation that accompanies Access, presumably because events are included in Access's
Properties window list for forms and controls, while events don't appear in Visual Basic's Properties window. This book uses the term event rather than property or event property.
Events that relate to movement of the record pointer of a Recordset object only are triggered by Data controls. If you want to use the Validate event to detect changes to the values in (or changes to the position of the record pointer of) an underlying Recordset object before the changes occur, you need to bind a data control to a persistent Table or QueryDef object, or to a Recordset object of the Dynaset type that you create with a Jet SQL statement. You can make the Data control invisible, and you need not bind any control objects to the Data control. Your code must reference the Recordset object of the Data control, not an instance of the Recordset object that you open or create with VBA code.
An example of the fine granularity of Access events triggered by bound controls is the sequence of OnEnter, BeforeUpdate, AfterUpdate, and OnExit events that occur when you update the value of a field of the current record. Table 17.1 lists Access
events and the equivalent Visual Basic event (where an equivalent exists). Table 17.1 shows that Visual Basic offers the GetFocus, Validate (with the vbDataActionUpate Action constant value), and LostFocus events, which correspond roughly to Access's
OnEnter, BeforeUpdate, and OnExit events, respectively. However, there is no Visual Basic event that directly corresponds to Access's AfterUpdate event, nor are Access events such as BeforeDel(ete)Confirm and AfterDel(ete)Confirm available in Visual Basic
The OnCurrent event also is not available in Visual Basic. You can use Visual Basic's Validate event to simulate AfterUpdate by adding a DoEvents statement to your event-handler code. There is no direct substitute or universal workaround for the OnCurrent
event.
| Event | Applies To | Occurrence | Visual Basic Event |
| OnOpen | Forms | On opening a form before the first record appears | GotFocus event of first control in tab order on form |
| OnCurrent | Forms | Before a record becomes the current record | No equivalent (use Validate with vbDataActionMove. . .) |
| OnInsert | Forms | When the first entry is made in a new record | Change event for any text box bound to the new record |
| OnEnter | Controls | When the control receives the focus | GotFocus |
| BeforeUpdate | Forms and controls | Before a change to a record is made permanent | vbDataActionUpdate |
| AfterUpdate | Forms and controls | After a change to a record is made permanent | No equivalent (use Validate with vbDataActionUpdate) |
| OnPush | Command buttons | When a command button is clicked | Click |
| OnExit | Controls | On losing the focus | LostFocus |
| OnDelete | Forms | Before deleting a record | Validate with vbDataActionDelete |
| OnClose | Forms | On closing a form | Unload, QueryUnload |
| but before the form disappears |
All the Access macro actions that manipulate forms and controls have corresponding Visual Basic methods or properties. Creating reports with the Crystal Reports OLE Control in Visual Basic applications differs greatly from the use of the integrated
report generator of Access, as discussed in Chapter 11, "Printing Reports with Code and Crystal Reports." Therefore, Access macro actions that pertain to reports do not have Visual Basic counterparts. (See Table 17.2.)
| Access Action | Purpose | VB 4.0 Substitute |
| Close (form or report) | Closes a form or report object | Unload or Hide methods for report custom control for reports |
| GoToControl | Sets the focus to a control on a form | ctrlName.SetFocus method |
| Maximize, Minimize, Restore | Sets the window style of a form | frmName.WindowState property |
| MoveSize | Determines the size and position of a form | Left, Top, Width, and Height properties of forms |
| OpenForm | Opens and displays a form | frmName.Show method |
| OpenReport | Opens a report for print preview or printing | Report control |
| Prints the activeobject (forms only) | [frmName].PrintForm method for forms (except MDI forms) | |
| RepaintObject | Redraws the selected object | objName.Refresh method |
| Requery | Updates a specified control | dtcName.Requery method (Data control only) |
| SetValue | Sets the value of a (property macros only) | Sets the value of the property with code |
The Access macro actions listed in Table 17.3 manipulate Access's equivalent of the Visual Basic Data Access Object. It is important to bear in mind the distinctions between the Jet Table and QueryDef objects that underlie Access forms and reports and
the same database objects opened in Visual Basic. Using Access, you can only manipulate the record pointer of a Table or QueryDef object to which an Access form or report is bound with Access macro actions. Visual Basic enables you to manipulate the record
pointer of a Data control directly with the Move. . . and Find. . . methods. Visual Basic 4.0 lets you imitate the GoToRecord macro action that specifies a record number with the AbsolutePosition property of a Recordset object.
| VB 4.0 Access Action | Purpose | Substitute |
| Close (Table or QueryDef object) | Closes a database object | Close method |
| FindNext | Finds the next record meeting specified criteria | FindNext method |
| FindRecord | Finds the first record meeting specified criteria | FindFirst method |
| GoToRecord | Goes to the record specified by an argument value (previous, next, first, last, record | Move. . . methods (except record number) and AddNew method (new record only) |
| number, new record) | ||
| OpenQuery | Opens a QueryDef object in datasheet, design or print preview views, or executes an action query | dbName.OpenQueryDef (strQueryName) method and additional code |
| OpenTable | Opens a specified Table object in datasheet, design, or print preview views | dbName.OpenTable(strTable Name) method and additional code |
Many of Access's general-purpose macro actions have exact counterparts in Visual Basic properties, methods, and instructions. Table 17.4 lists Access macro actions that are duplicated in Visual Basic.
| VB 4.0 Access Action | Purpose | Substitute |
| Hourglass | Turns the mouse pointer to the hourglass shape | Screen.MousePointer property |
| MsgBox | Displays a message box with an optional title | MsgBox instruction or MsgBox() function |
| Quit | Exits the Access application | End statement |
| RunApp | Runs another Windows | Shell() function |
| application | ||
| SendKeys | Sends keystrokes to the application with the focus | SendKeys instruction |
| Beep | Sounds the standard Windows message beep | Beep instruction |
Table 17.5 lists the macro actions of Access that have no direct equivalents in Visual Basic. Some of the actions listed in Table 17.5, such as RunMacro, StopMacro, and StopAllMacros, are not applicable to Visual Basic, because you convert all Access
macros to VBA subprocedures. Menus you create in Visual Basic's Menu Design window substitute for Access menus created by the AddMenu action. There is no need to use the Echo False and Echo True actions in Visual Basic applications, because you don't need
to inhibit screen repainting.
In cases where a macro action listed in Table 17.5 is applicable in Visual Basic programming methodology, a workaround usually exists or can be created with VBA code. Some workarounds are simpler than others. You can use flags to selectively execute
event-handling code to duplicate the effect of the CancelEvent action. On the other hand, a substantial amount of code and programming expertise is required to write a workaround for Visual Basic 4.0's lack of Access's TransferSpreadsheet action. However,
you can use OLE Automation to export data to an Excel worksheet object and then save the worksheet.
| VB 4.0 Access Action | Purpose | Workaround |
| AddMenu | Adds a menu or submenu choice to a form and specifies the macro or function to execute. | Use the Menu Design window (see the section "Converting Menu Macros"). |
| ApplyFilter | Applies a WHERE clause filter to a Table or QueryDef object. | Add the SQL WHERE clause and execute a new query. |
| CancelEvent | Cancels the event that initiated the macro execution. | Visual Basic event handlers that include a Cancel argument, such as Validate; otherwise no counterpart. |
| DoMenuItem | Executes a menu choice. | Execute the event-handling subprocedure for the menu choice. |
| Echo | Halts repainting of most visible objects. | Not applicable. Ignore Echo actions. |
| GoToPage | Sets the focus to the first control of the specified page of a multipage form. | Not applicable. Multipage forms are not supported. |
| Print (except forms) | Prints the specified datasheet. | Printer.Print method with code to draw grid objects and create appropriate text strings. |
| RunCode | Runs a specified Access Basic function from a macro. | Not applicable. The closest equivalent is the Call statement. |
| RunMacro | Runs a designated macro. | Not applicable. Macros are not supported. |
| SelectObject, CopyObject, Rename | Selects, copies, or renames database objects. | Not applicable to VB database objects. |
| SetWarnings | Enables or disables Access warning messages, primarily related to manipulating database objects. | Not applicable. Access warning messages do not appear. |
| ShowAllRecords | Removes filters applied to a Recordset or QueryDef object. | Requery without an SQL WHERE clause. Filters are not supported. |
| StopMacro, StopAllMacros | Stops execution of the current macro or all macros. | Not applicable. Macros are not supported. |
| Transfer Database, Transfer Spreadsheet, TransferText | Imports data from or exports data to database tables, worksheets, or text files. | Must be accomplished with code. Use VB's low-level file I/O instructions or OLE Automation. |
Access 1.x's Access Basic, originally called Embedded Basic, is a direct descendant of Visual Basic 2.0. Visual Basic 3.0 inherited most of its data access functions from Access 1.0, along with the improved Jet database engine of Access 1.1. The
TableDefs object collection and TableDef objects of Visual Basic 3.0 were present in Access 1.x, but they were not exposed to Access Basic until the advent of Access 2.0. The close relationship of Access Basic to VBA makes importing Access Basic
1.x and 2.0 code into Visual Basic modules a relatively simple process, and importing Access VBA is even easier. The sections that follow describe the alterations to Access Basic and VBA code that are necessary when you import Access functions and
subprocedures into Visual Basic 4.0 modules.
User-defined functions written in Access Basic and VBA that simply return calculated values or modified strings usually import into Visual Basic 4.0 without the need to alter any code. All the arithmetic and comparison operators, as well as the string
and data-type conversion functions, of Access Basic, Access VBA, and Visual Basic 4.0's flavor of VBA are identical. The widely used Access Basic IsLoaded function, however, requires translation to Visual Basic. The IsLoaded function returns True or
False, depending on whether a form named in the function's argument is loaded. (The IsLoaded function primarily is called by Access macros.) Listing 17.1 shows the Access Basic version of IsLoaded included in the Utility Functions module of Access
2.0's version of NWIND.MDB. The IsLoaded function of the Access 95's Northwind.mdb uses Access VBA's SysCmd function, which isn't supported by Visual Basic VBA.
Listing 17.1. The Access Basic version of the IsLoaded() function.
Function IsLoaded (MyFormName)
' Accepts: a form name
' Purpose: determines if a form is loaded
' Returns: True if specified the form is loaded;
' False if the specified form is not loaded.
' From: Chapter 3
Dim i
IsLoaded = False
For i = 0 To Forms.Count - 1
If Forms(i).FormName = MyFormName Then
IsLoaded = True
Exit Function ' Quit function once form has been found.
End If
Next
End Function
If you copy the preceding code for the IsLoaded() function to a Visual Basic module and then attempt to compile the project that contains the module, you receive an error message that reads Property or control "FormName" not found. Figure
17.1 shows the error message and the outlined code containing the error. Simply change the FormName property to Name, and the function executes properly.
Emulating NWIND.MDB's IsLoaded function in Visual Basic 3.0 involved a major code-writing exercise because the Forms collection of Visual Basic 3.0 didn't expose the Name property of each Form object in the collection. The Forms collection of Visual Basic 4.0 closely resembles that of Access 2.0 and 95, with the exception of a few Form property names.
Access Basic and VBA event-handling functions, and CBF subprocedures can be called by one of the three following methods:
Whether an Access Basic or VBA function is called directly or from a macro has no effect on the alterations you need to make to conform the code to Visual Basic 4.0 standards.
If the Access Basic or VBA function is called by a complex macro, you need to make a decision: Should any additional code that is required to duplicate other actions in the calling macro be incorporated within the function? "Not initially" is usually the best answer. Preserving the original structure of the Access application until you have the Visual Basic 4.0 version running makes debugging easier, because both applications have parallel execution paths.
The sections that follow provide specific advice on the major issues that face developers porting Access Basic applications that include substantial amounts of Access Basic code to Visual Basic 4.0 applications.
You can import Access Basic code to Visual Basic modules by using one of the following three methods:
Using text files to import Access Basic module code en masse from Access 1.x and 2.0 to Visual Basic applications is the better approach when more than about 100 lines of code are involved in the conversion process.
The Access Basic DoCmd statement is used to execute macro actions in Access Basic code. Only macro actions can manipulate Access database objects such as forms, reports, and the Recordset objects to which forms, reports, and controls are bound.
Therefore, you are likely to encounter a substantial number of DoCmd statements in Access Basic and the DoCmd object in Access VBA code that you need to convert to Visual Basic. In most cases, the code you substitute for DoCmd{ |.}ActionName
Arguments. . . statements is identical to the code you write to substitute for the ActionName macro action. (Access 1.x and 2.0 use a space to separate DoCmd from ActionName; Access 95 uses a period separator because
ActionName now is defined as a method of the DoCmd object.) The tables of Visual Basic code counterparts for Access macro actions given earlier in this chapter apply equally to actions executed by DoCmd statements in all versions of Access.
DoCmd statements that employ Access global symbolic constants to represent the values of arguments are quicker and easier to convert than those statements that use integer arguments. Using symbolic constants instead of integer values for arguments
makes the objective of the DoCmd statement readily understandable. If the DoCmd statements use integers to represent argument values, you need to refer to the Access Language Reference to make the integer values meaningful.
An alternative method of translating argument values to meaningful terms is to open a new macrosheet in Access and choose the macro action you are replacing from the drop-down combo box list in the Action column. Macro action arguments in the Action
Arguments pane of the macro design window that require integer values have drop-down combo boxes, as illustrated by Figure 17.2. Open the drop-down combo box and count list items from the first entry (0) to the value assigned to the argument. The list
entry provides the required translation. As an example, if you encounter a DoCmd DoMenuItem 1, 2, 0 statement in the Access Basic code or DoCmd.DoMenuItem 1, 2, 0 in Access VBA, add a DoMenuItem macro action to the new macrosheet. Open the list box for the
Menu Bar and count down to the second item to identify the object to which the menu is applicable, the Database window. Then open the Menu Name and count down to the third item to identify the View menu. Finally, open the Command list box to identify the
first menu choice, Tables.
Figure 17.2. Using Access 95's macro design window to translate integer action arguments.
Each version of Access has its own menu layout and hierarchy. For backward compatibility, Microsoft provides a set of intrinsic constants acMenuVer1x (the default), acMenuVer20, and acMenuVer70, to specify the version of Access menus for the DoMenuItem action or method. If you use the new macrosheet method for determining integer values of DoMenuItem arguments, make sure you use the version of Access that corresponds to the value of the acMenuVer?? constant. If the acMenuVer?? constant is not present, you need to use Access 1.1's macro design window to obtain the correct values.
There are a few Access Basic reserved words and keywords that are not duplicated exactly in Visual Basic 4.0. Domain aggregate functions are examples of Access Basic keywords that Visual Basic 4.0 lacks. Domain aggregate functions return values that
represent the count, average, and a number of other characteristics of a specified domain (a set of records). To duplicate a domain aggregate function in Visual Basic, you need to create a Recordset object of the Dynaset or Snapshot type that corresponds
to the domain, and then perform a record-by-record arithmetic operation or search on the Recordset object. If your Recordset contains less than 100 or 200 rows, using the Snapshot-type Recordset object usually is faster than the Dynaset type, and you can
speed things up even more by specifying a forward-only Snapshot. The exception to the described procedure is the DCount() function that you can replace with the RecordCount property of the Recordset object. (Make sure to apply the MoveLast method before
returning the RecordCount property to obtain a valid result.)
The SysCmd function of Access is not supported by Visual Basic. SysCmd primarily is used to return status information for Access objects, such as whether forms and reports are open in design or run mode, and the Access application itself, such as
whether the application is operating under the retail or runtime version of Access. (The runtime and retail versions of Access 95 use the same executable file, Msaccess.exe; an entry in the Registry determines if Msaccess.exe operates in runtime mode.) The
preceding uses of SysCmd are not applicable to Visual Basic applications, so in most cases you can delete the SysCmd statements with no ill effect. SysCmd also is used to create a programmable progress indicator in the status bar; you can emulate the
progress indicator with a 32-bit (only) Visual Basic ProgressBar control, which is one of the Win32 common controls. For 16-bit compatibility, use the Gauge OLE Control.
Access Basic DDE. . . instructions and functions differ greatly from the Link. . . methods you apply in Visual Basic. Unfortunately, Microsoft didn't include in Visual Basic 4.0 DDEInitiate, DDEExcecute, and other DDE. . . instructions and functions
that are employed by the other dialects of VBA. All the DDE methods of Visual Basic are bound to form or control objects, while DDE methods in other VBA dialects obtain values from or return values to conventional variables.
Flexibility in creating reports has been one of Access's strongest selling points since Microsoft introduced Access 1.0 in 1992. Version 1.x's inability to alter in run mode many of the properties of Access reports was rectified in Access 2.0,
making Access reports even more powerful. The version of Crystal Reports supplied with the Professional version of Visual Basic 3.0 was a stand-alone database reporting product that was added to, rather than integrated with, Visual Basic. The new Crystal
Reports OLE Controls, Crystl16.ocx and Crystl32.ocx, described in Chapter 11 can be bound to a Data control, which is a significant improvement. You must recreate all of your Access reports in Crystal Reports' design mode; this is not a trivial process if
the Access application you're porting contains a large number of complex reports.
The alternative to using Crystal Reports is to use OLE Automation to print reports from a runtime Access 95 application or to use DDE with Access 2.0. Simultaneously running a sizable Visual Basic 4.0 application and Access (especially Access 95) is a very resource-intensive process. Unless the PCs on which you plan to run such a hybrid application have at least 16MB of RAM (Windows 95) or 24MB of RAM (Windows NT 3.51+), the report printing process is likely to be very slow.
Access provides controls, such as multicolumn bound combo and list boxes, that don't have exact counterparts in Visual Basic 4.0. Figure 17.3 shows a Access 95 demonstration application that uses two bound list boxes to display customer names and
recently placed orders. Several third-party publishers provide OLE Controls that emulate Access's bound controls. If you want to use only those controls supplied with Visual Basic 4.0, the appearance and behavior of the controls you substitute differ from
the Access originals.
Figure 17.3. An Access 95 form with two bound multicolumn list boxes.
The following list describes your options for creating bound or pseudo-bound lists with Visual Basic 4.0:
Figure 17.4. Emulating an Access multicolumn list box with a Visual Basic 4.0 DBGrid control.
Visual Basic 4.0 identifies most of the changes you need to make to your Access Basic or VBA code for you when you attempt to compile the code. One method of identifying nonconforming statements is to choose Run | Start with Full Compile
repeatedly and correct the errors as the interpreter finds them. The following list of conversion steps provides a more organized alternative approach:
Similarities between Access Basic, Access VBA, and Visual Basic 4.0's dialect of VBA make it appear that converting Access applications to Visual Basic 4.0 database applications is a simple, straightforward process. Many Access applications, however,
use macro actions rather than Access Basic code to respond to events. Replacing macros with Visual Basic code requires ingenuity on the part of the Visual Basic programmer. The chapter began by providing an outline of the approach to use and the basic
steps involved in converting a macro-based Access application to Visual Basic 4.0. Suggestions for translating Access macro actions to code that sets the value of Visual Basic object properties or applies Visual Basic methods to objects were provided in
tabular form.
This chapter completes Section IV, the advanced programming section of Database Developer's Guide with Visual Basic 4. The next section of this book deals with creating multiuser Visual Basic database applications designed for use in a
workgroup, department, or enterprise-wide network environment.
