Woody's Office Watch

Supplement to issue #4.10
Office 2000 Developers Special Issue

Below is additional information and tables to be read in conjunction with the special issue of Woody's Office Watch #4.10.

MOD Components

Name	Installed from	Available from	Usage
Code Librarian	Developer Tools option in MOD setup	Add-ins menu in VBE window; Microsoft Office 2000 Developer program group	Stores code segments to be pasted into procedures; comes pre-loaded with lots of useful code
VBA Code Commenter		Add-ins menu in VBE window	Inserts standard comment block into procedures
VBA Error Handler			Inserts standard error-trapping into procedures
VBA Multi-Code Import/Export			Imports and exports procedures as .bas, .cls or .frm files
VBA Package and Deployment Wizard			Prepares a set of setup disks for distributing applications
VBA String Editor			Constructs strings of text and variables for use in code
VBA WinAPI Viewer			Lets you select and paste API calls into procedures
VBA Source Code Control		Visual Source Safe	Lets you work with source code
Visual Source Safe	Developer application option on MOD setup (separate installation)	Visual Source Safe program group	Manages development projects with multiple programmers
HTML Help Workshop		HTML Help Workshop program group	Creates HTML Help files for applications
Answer Wizard Builder		Answer Wizard Builder program group	Creates Answer Wizard index entries for your custom Help topics
Microsoft Agent SDK		Microsoft Agent Character Editor shortcut on Programs menu of Start Menu	Lets you create your own custom Office Assistant characters
Microsoft Development Engine (MSDE)	Installed from separate file on Office 2000 CD #1: \Sql\x86\Setup\Setupsql.exe	MSDE program group	An alternate to the Jet Engine; provides local data storage compatible with SQL Server.
Data Binding Manager	Installed with Office 2000 Developer, not separately selectable	VBA code	Allows you to bind controls to data sources, even if they are not data-bound controls
Data Environment Designer		To activate designers, select File|Insert|Components in the VBE Window, and check the ones you want to use. To use, select File|Insert for VBA projects	An interface for creating connections to data sources
Data Report Designer			Designs grouped reports based on Office data
ADO Data Control		Access forms: More Controls tool on Controls Toolbox Outlook and Office UserForms: Right-click Control Toolbox background, select Additional Controls	Binds to data in Office applications; used as a data source for data-bound ActiveX controls
Data-Bound ActiveX Controls			Displays data from source bound to ADO Data Control
Other ActiveX Controls			Adds functionality to the form interface
COM Add-in Designer		File|New Project|Add-in Project from VBE window	Creates COM Add-ins for Office applications
Visual Studio Templates		MOD CD, ODETools\V9\Samples\Unsupprt folder	Used to create COM Add-ins in Visual Studio applications
Access Run Time		Selectable in Package & Deployment Wizard	Supports running Access applications on systems lacking the full version of Access
Replication Manager		Microsoft Office 2000 Developer program group	Manages replicated Access databases

MOD Acronymns List

Acronym	Stands for	Brief Definition
ADO	ActiveX Data Objects	An object model representing the structure and data in many different data sources
COM	Component Object Model	A cross-platform open architecture based on object-oriented technology
DAO	Data Access Objects	An object model representing the structure and data in Jet engine data sources (basically, Access data)
DLL	Dynamic Link Library	A collection of routines available from VBA procedures that are loaded and linked to your application at run time.
HTML	Hypertext Markup Language	A markup language used to display formatted text on the Web
I-ISAM	Installable Indexed Sequential Access Method	Drivers used by Access to update data in non-Access file formats
MSDE	Microsoft Data Engine	Provides local data storage compatible with SQL Server.
MSDN	Microsoft Developers Network	Online and CD source of documentation for Microsoft development products
ODBC	Open Database Connectivity	An older data access technology, used to access data in a number of sources
OLE	Object Linking and Embedding	A Microsoft technology for working with objects in other applications’ object models
OLE DB	Object Linking and Embedding Database	The system-level data access interface to data in a variety of sources
SDK	System Development Kit	A collection of programs and utilities that lets you create applications or components
SQL	Structured Query Language	A language used by relational databases to query, update and manage data.
UDA	Universal Data Access	A new data access strategy intended to replace the current plethora of acronyms
VB	Visual Basic	A separate Microsoft product for creating standalone applications.
VBA	Visual Basic for Applications	A dialect of VB used within Microsoft Office applications and 3rd party applications
VBS	Visual Basic Scripting Edition	A dialect of VB used for Outlook forms programming and Internet Explorer
VSS	Visual Source Safe	Manages source code for multi-developer applications

Data Binding Manager

Here are the steps to bind a standard text box control to a field in the Northwind Customers table.

1. Create a new UserForm in the VBE window.
2. Place an ADO Data Control on the form; name it ocxADO.
3. Double-click the "(Custom)" selection in the control’s properties sheet to open the control’s special properties sheet.
4. Select the "Use Connection String" option, and click the Build button next to it.
5. In the properties sheet’s Provider tab, select the Microsoft Jet 4.0 OLE DB Provider.
6. On the Connection tab, select the Northwind sample database from a standard File Open dialog.
7. On the RecordSource page, select the adCmdTable command type.
8. From the Table or Stored Procedure Name drop-down list, select the Customers table.
9. Back on the properties sheet’s General page, you will see the connection string for the new connection.
10. Click OK to close the dialog (the advanced options generally can be left at the default settings).
11. Resize the ADO control to a narrow strip (it will be used to navigate through the records).
12. Place a standard textbox control on the form; name it txtCompany.
13. Double-click the UserForm background to open its code module.
14. Open the Tools|References dialog and set a reference to the Microsoft Data Binding Collection.
15. Enter the following declaration in the General section of the module:

Private bmCustomers As New BindingCollection

16. Select the form’s Initialize event, and enter the code below (note that there are no quotes around the name of the text box control, although this argument is listed as a String in the Object Browser):

Private Sub UserForm_Initialize()

Set bmCustomers.DataSource = ocxADO

BmCustomers.Add txtCompany, "Text", "CompanyName"

End Sub

17. Run the form. You will see the first company name in the Customers table; to navigate through the records in the table, use the VCR buttons in the ADO control.

MOD Designers

To use this designer, follow the steps below:

1. Select File|New Project in the VBE window.
2. Click the Empty Project icon to create a new project.
3. With the new project highlighted, select Insert|Data Environment. You will see a new Connection object called Connection1; name it conNorthwind.
4. Right-click the connection and select Properties.
5. In the properties sheet’s Provider tab, select the Microsoft Jet 4.0 OLE DB Provider.
6. On the Connection tab, select a database (say, the Northwind sample database) from a standard File Open dialog.
7. Click OK to close the dialog (the advanced options generally can be left at the default settings).
8. With the connection highlighted, click the Add Command button on the toolbar.
9. Open the command’s properties sheet.
10. A new command is created, called Command1; rename it comProducts.
11. On the General tab, select Table as the database object.
12. Select the Products table from the Object Name drop-down list.
13. Click OK to close the dialog.

Once you have set up your connection string, you can drag it to a Data Report (see the next section). This designer is a great help in setting up connection strings, sparing the developer the pain of constructing them manually, using an extremely cryptic syntax.

The Data Report Designer produces a report laid out much like an Access report, with Header, Footer and Detail sections. To create a report, follow the steps below:

1. Select Insert|Data Report in a project (say the one created above).
2. A new report opens, with a DataReport control separated into sections like an Access report.
3. Drag the previously created comProducts command to the report’s Detail section.
4. Set the report’s Data Member property to the name of the command.
5. Help (by this time in a VB Help topic) says to set the report’s Data Source property to the data environment’s name. However, there is no such property for a report in Office 2000.
6. Help says that you can display the report by using its Show method, say from a command button on a User Form with the line

rptProducts.Show

Actually, when I tried to show a report, I just got a "Data source invalid" message. This may be because a different method is required to display a report’s data in Office 2000. I am awaiting a response on this issue.

ActiveX controls

The ActiveX controls provided with MOD are listed in the table below, with the data-bound ones indicated by an asterisk.

Control Name	Usage
*ADO Data Control	Binds to data in a table or other data source; used to supply data to other controls
Animation	Displays silent Audio Video Interleaved (AVI) clips
Common Dialog	Displays one of several Windows dialogs, such as the File Open and Colors dialogs
Chart Control	Displays data in a chart format, similar to an Excel chart
CoolBar	Creates user-configurable Internet Explorer-style toolbars
*Data Report	Used with the Data Report Designer to place fields of data on a report
*DataCombo	A data-bound combo box
*DataGrid	A data-bound datasheet
*DataList	A data-bound listbox
DateTimePicker	Displays a pop-up calendar and saves the selected date to a field
FlatScrollbar	Works like the Windows scrollbar, with several style choices
*Hierarchical Flexgrid	Displays data from linked tables, similar to the new subdatasheet feature in Access.
ImageCombo	Similar to a combo box, except that you can include images with the list items
ImageList	Stores a list of images that can be displayed in other controls, such as the TreeView control
Internet Transfer	Lets you connect to Web sites and FTP sites
ListView	Displays data as ListItem objects, which may have an associated icon. Can be used to display an expanded view of a TreeView control node.
MonthView	Displays a fixed-size calendar showing one or more months at a time; users can select a single date or a range of dates
ProgressBar	Graphically represents the progress of a transaction
Rich Textbox	Displays Rich Text, with color, emphasis and different fonts
Slider	A bar with a "thumb" control (similar to a Windows scroll bar); the user moves the thumb to adjust a value
StatusBar	Displays information about the status of various application components
SysInfo	Detects system events such as desktop resizing or resolution changes; also can display operating system or version information.
Tabbed Dialog	Presents several pages of information with tabs for selecting a page.
TabStrip	A strip of tabs used to define multiple pages for a window or dialog.
ToolBar	Contains a collection of buttons used to create a toolbar for an application.
TreeView	Displays data in a hierarchical tree, like the Windows Explorer.
UpDown	Increments or decrements a number (this control is used in many of the built-in Windows and Office dialogs)
Winsock	Lets you connect to a remote computer using either the UDP or TCP protocol.

DataGrid Control

The steps below walk you through creating a data-bound DataGrid control on a UserForm which could be used in any Office application.

Placing a DataGrid Control on an Office UserForm

1. Create a new UserForm in the VBE window.
2. Place an ADO Data Control on the form; name it ocxADO.
3. Double-click the "(Custom)" selection in the control’s properties sheet to open the control’s special properties sheet.
4. Select the "Use Connection String" option, and click the Build button next to it.
5. In the properties sheet’s Provider tab, select the Microsoft Jet 4.0 OLE DB Provider.
6. On the Connection tab, select the Northwind sample database from a standard File Open dialog.
7. Back on the properties sheet’s General page, you will see the connection string for the new connection.
8. On the RecordSource page, select the adCmdTable command type.
9. From the Table or Stored Procedure Name drop-down list, select the table you want to use as a record source.
10. Click OK to close the dialog (the advanced options generally can be left at the default settings).
11. Set the control’s Visible property to False (the VCR buttons are not functional in the interface for this control).
12. Place a DataGrid control on the form.
13. Double-click the form to open its code module, and select its Initialize event.
14. Enter the following line of code for the Initialize event (where "ocxFlexGrid" is the name of the DataGrid control, and "ocxADO" is the name of the ADO Data Control):

Private Sub UserForm_Initialize()

Set ocxFlexGrid.DataSource = ocxADO

End Sub

15. Click the Run button to run the form; the DataGrid displays data from the selected Northwind table.

 

VBA PROJECT

Download NORTHWIND.VBA here