AfterLayout
The AfterLayout event occurs after the chart in the PivotChart view has been laid out but before the chart elements have been drawn on screen. Use this event to draw custom lines, text, and various drawings on the chart. The drawObject parameter in the AfterLayout event represents a chChartDraw object. The DrawEllipse, DrawLine, DrawPolyLine, and DrawPolygon methods of the chChartDraw object can be used to add drawing objects to a chart. The DrawText method can be used to add text to a chart....
Retrieving Replica Properties
The procedure in Hands-On 18-8 examines Microsoft Office Access database files in the specified path and lists their replication properties to the Immediate window. Table 18-3 List of replica properties checked in Hands-On 18-8 Table 18-3 List of replica properties checked in Hands-On 18-8 Specifies the type of replica Full, Partial, Design Master, or database is not replicable. Use the following enumerators jrReplicaTypeFull jrReplicaTypePartial jrRepTypeDesignMaster jrRepTypeNotReplicable...
Creating a Design Master
A replica is a copy of a database. There are three types of replicas Design Master, full replica, and partial replica. To work with replication, start by creating a backup copy of your database. You will need it in case there is a problem with the replicated database. Next, create a replica of your database by using the Replica object's MakeReplicable method. This method requires that you provide a full path to your database and indicate whether you want column-level tracking. With column-level...
Capture Errors with Case Else
Although using Case Else in the Select Case statement isn't required, it's always a good idea to include one just in case the variable you are testing has an unexpected value. The Case Else is a good place to put an error message. Sometimes a decision is made based on whether the test expression uses the greater than, less than, equal to, or some other relational operator see Table 5-1 . The Is keyword lets you use a conditional expression in a Case clause. The syntax for the Select Case clause...
Using Breakpoints
If you know more or less where there may be a problem in your procedure code, you should suspend code execution at that location on a given line . Set a breakpoint by pressing F9 when the cursor is on the desired line of code. When VBA gets to that line while running your procedure, it will display the Code window immediately. At this point you can step through the procedure code line by line by pressing F8 or choosing Debug Step Into. To see how this works, let's look at the following...
Listing Tables and Fields
Earlier in this chapter you learned how to enumerate tables in the Northwind database by accessing the Tables collection of the ADOX Catalog object. The procedures in Hands-On 11-12 and 11-13 demonstrate how to use the OpenSchema method of the ADO Connection object to obtain more information about a database table and its fields. Hands-On 11-12 Using the OpenSchema Method to List Database Tables 1. In the Visual Basic Editor window, choose Insert Module. 2. In the module's Code window, type the...
Custom Project Importing XML Data to an Access Database and Applying a Transform
Part 1 Create a Custom Transformation File to be Used After the XML Data Import 1. Open Notepad and enter the statements as shown below. lt xml version L0 encoding UTF-8 gt lt xsl stylesheet version 1.0 lt xsl output method html version 4.0 indent yes gt lt xsl template match dataroot gt lt html gt lt body gt lt xsl apply-templates select Customers gt lt table gt lt table gt lt xsl apply-templates lt table gt lt body gt lt html gt lt xsl template gt lt xsl template match Customers gt lt xsl...
Understanding the Code Window
The Code window is used for Visual Basic programming as well as for viewing and modifying the code of existing Visual Basic procedures. Each VBA module can be opened in a separate Code window. There are several ways of activating the Code window From the Project Explorer window, choose the appropriate module and then click the View Code button From the VBE menu, choose View Code From the keyboard, press F7 At the top of the Code window there are two drop-down list boxes that allow you to move...
HandsOn Writing a Function Procedure with Arguments
This chapter's hands-on exercises are provided in the Acc2003_Chap04.mdb file included in the book's downloadable files. 1. Open Acc2003_Chap04.mdb from the downloadable files or, if you'd like to start from scratch, create a new Access 2003 database. 2. In the Database window, click the Module button to create a new module. 3. Enter the following function procedure JoinText in the Module1 Code window. JoinText k o End Function Note that there is a space character in quotation marks...
Retrieving Table Properties
The Properties collection of an ADOX Table object allows you to set or retrieve table properties. The Properties collection exposes standard ADO properties as well as properties specific to the data provider. The following procedure accesses the table called tblFilters and lists its properties and their values in the Immediate window. Hands-On 11-6 Listing Table Properties 1. In the Visual Basic Editor window, choose Insert Module. 2. In the module's Code window, type the List_TableProperties...
Custom Project Filling a Combo Box with a Disconnected Recordset
This custom project requires that you complete Hands-On 16-2. 1. Create a form as shown in Figure 16-1. Place a combo box control in the form. Change the Name property of this control to cboCompany. Set the Caption property of the label control to Company . ' ' ' ' 1 Figure 16-1 This custom form is used to demonstrate how you can fill the combo box control with a disconnected recordset. Figure 16-1 This custom form is used to demonstrate how you can fill the combo box control with a...
Connecting to an SQL Server
The ADO provides a number of ways of connecting to an SQL Server database. To access data residing on Microsoft SQL Server 6.5, 7.0, or 2000, you can use SQLOLEDB, which is the native Microsoft OLE DB provider for SQL. Hands-On 10-12 Connecting to an SQL Server Database 1. In the Visual Basic Editor window, choose Insert Module. 2. In the module's Code window, type the ConnectToSQL_SQLOLEDB procedure shown below. 3. Choose Run Run Sub UserForm to execute the procedure. Sub ConnectToSQL_SQLOLEDB...
List PropertiesMethods
Each object can contain one or more properties and methods. When you enter the name of the object in the Code window followed by a period that separates the name of the object from its property or method, a pop-up menu may appear. This menu lists the properties and methods available for the object that precedes the period. To turn on this automated feature, choose Tools Options. In the Options window, click the Editor tab, and make sure the Auto List Members check box is selected. While...
HandsOn Writing an Event Procedure
1. Open the Acc2003_Chap01.mdb database file from the book's downloadable file. This file contains a copy of the Customers table and the Customers form from the Northwind database that comes with Microsoft Office Access. 2. Open the Customers form in Design view. 3. Right-click the ContactName text box control on the form, and choose Properties from the shortcut menu. 4. Click the Event tab of the Text Box ContactName property sheet. The list of event procedures available for the text box...
Synchronizing Replicas
Synchronization allows reconciliation of all changes between replicas. This means that any changes, additions, or deletions can be exchanged between replicas, thus keeping all the copies current and up to date. In JRO, there are three types of synchronization, as shown in Table 18-1. Table 18-1 Replica synchronization types Table 18-1 Replica synchronization types Updates are mutual they flow from the target replica to the source replica and from the source replica to the target replica....
Custom Project Securing a Microsoft Access Database
You must complete this custom project in order to work with the hands-ons in this chapter. 1. Create a new folder on your computer and name it BookProject. 2. Start Microsoft Office Access and create a new blank database called SpecialDb.mdb. Save this database in the BookProject folder you created in step 1. Keep this database open and proceed to Part 2 below. Part 2 Establishing User-Level Security We will use the built-in User-Level Security Wizard to secure the blank Access database we...
Working with Collections
A set of similar objects is known as a collection. For example, a Microsoft Access database has a collection of tables, and each table has a collection of fields and indexes. In Microsoft Excel, all open workbooks belong to the collection of workbooks, and all the sheets in a particular workbook are the members of the worksheets collection. In Microsoft Word, all open documents belong to the collection of documents, and each paragraph in a document is a member of the paragraphs collection....
Creating a PassThrough Query
SELECT Customers.4- E oM Customers WHEKE CLEtDmers.Cim1r 'France Figure 15-4 A pass-through query used to access external data sources is a special type of query and can only be created via the SQL Pass-Through Query window or in a VBA procedure. SELECT Customers.4- E oM Customers WHEKE CLEtDmers.Cim1r 'France Figure 15-4 A pass-through query used to access external data sources is a special type of query and can only be created via the SQL Pass-Through Query window or in a VBA procedure....
Understanding the Project Explorer Window
The Project Explorer window provides access to modules behind forms and reports tt Microsoft Office Access Class Objects m Form_Customer Orders Subforml Ell Form_Customers IfU Form_Employees EH Form_Orders I El Form_Orders Subform Eil Form_Sales Analysis I El Form_5ales by Year Dialog ill Report_Employee Sales by Country ill Report_5ales by Year via the Microsoft Office Access Class Objects folder see Figure 2-1 . The Modules folder lists only standard modules that are not behind a form or...
Creating a Primary Key
A primary key uniquely identifies a row in a table. A primary key is an index with its Unique and PrimaryKey properties set to True. There can be only one primary key per table. To create new keys, use the Key object from the ADOX library. To determine whether the key is primary, foreign, or unique, use the Key object's Type property. For example, to create a primary key, set the Key object's Type property to adKeyPrimary. The procedure in Hands-On 12-1 demonstrates how to add a primary key to...
Returning a Recordset as a String
Instead of using a loop to read the values of fields in all rows of the open recordset, you can use the Recordset object's GetString method to get the desired data in one step. The GetString method returns a recordset as a string-valued Variant. This method has the following syntax Variant Recordset.GetString StringFormat, NumRows, _ ColumnDelimiter, RowDelimiter, NullExpr The first argument StringFormat determines the format for representing the recordset as a string. Use the adAddClipString...
Setting User Permissions for an Object
With ADOX, you set permissions on an object by using the SetPermissions method. User-level security can be easier to manage if you set permissions only for groups, and then assign users to the appropriate groups. Recall that permissions set for the group are automatically inherited by all users in that group. The SetPermissions method, which can be used for setting both user and group permissions, has the following syntax GroupOrUser.SetPermissions Name, ObjectType, Action, Rights , Inherit...
Retrieving Field Properties
The procedure in Hands-On 11-7 retrieves the field properties of the field named Id located in tblFilters in the current database. Hands-On 11-7 Listing Field Properties 1. In the Visual Basic Editor window, choose Insert Module. 2. In the module's Code window, type the List_FieldProperties procedure shown below. 3. Choose Run Run Sub UserForm to execute the procedure. Sub List_FieldProperties Dim cat As ADOX.Catalog Dim col As ADOX.Column Dim pr As ADOX.Property Set cat.ActiveConnection...
Advanced XML Export Options
When you exported the Shippers table to XML format, you may have noticed the More Options button in the Export XML dialog box see Figure 29-2 at the beginning of this chapter . Pressing this button opens a window with three tabs as shown in Figure 29-8. Each tab groups options for the types of XML objects that you can export. The Data tab contains options for the XML document, the Schema tab list options for the XSD document, and the Presentation tab provides options for generating the XSL...
The LBound and UBound Functions
The LBound and UBound functions return whole numbers that indicate the lower bound and upper bound indices of an array. Hands-On 7-10 Finding the Lower and Upper Bounds of an Array 1. In a new module, enter the code of the FunCities2 procedure shown below. 2. Run the FunCities2 procedure. ' assign the values to array elements cities 1 Las Vegas cities 2 Orlando cities 3 Atlantic City cities 4 New York cities 5 San Francisco MsgBox cities 1 amp Chr 13 amp cities 2 amp Chr 13 _ amp cities 3 amp...
Setting the Database Password
Database security can be handled at share level or user level. Share-level security is the easiest to implement, as it only requires that you set a password on the database. You can use the ALTER DATABASE statement to set a new database password or change an existing password. The syntax looks like this ALTER DATABASE PASSWORD newPassword oldPassword When setting the password for the first time, use Null for the old password see the example procedure in Hands-On 22-1 . The Access database must...
Trapping Errors
No one writes bug-free programs the first time. For this reason, when you create VBA procedures you have to determine how your program will respond to errors. Many unexpected errors happen at run time. For example, your procedure may try to give a new file the same name as an open file. Run-time errors are often discovered not by a programmer, but the user who attempts to do something that the programmer has not anticipated. If an error occurs when the procedure is running, Visual Basic...
ADO Object Model
ActiveX Data Objects ADO is OLE DB's main object model. ADO consists of three object models, each providing a different area of functionality see Table 10-1 . Because of this, only the objects necessary for a specific task need to be loaded at any given time. Note If you're interested in programming Microsoft Office Access databases using the older Data Access Objects DAO and this may be a valid requirement if you happen to be involved in a DAO to ADO conversion project , AppendixA.pdf in the...
HandsOn Writing the FormOnConnect Event Procedure
1. In the Database window, select the Sales Analysis Subform 1 and click the Design button. This is one of the forms included with the Northwind sample database. 3. Choose View Properties to activate the Form properties sheet. 4. Click the Event tab, set the On Connect property to Event Procedure , 5. Access will create the event procedure stub. Enter the following Form_OnConnect event procedure MsgBox You have successfully connected to the data source. amp vbCrLf _ amp The following SELECT...
HandsOn Writing the FormBeforeQuery and FormQuery Event Procedures
1. In the Visual Basic Editor window, double-click Form_Sales Analysis Subform 1. 2. The Code window shows the Form_OnConnect event procedure you prepared in Hands-On 24-21. Enter the following Form_BeforeQuery and Form_Query event procedures below the last procedure code. Private Sub Form_BeforeQuery Dim strColor As String strColor InputBox Type 1 for Red, 2 for Yellow, amp _ or 3 for Green, Specify Background Color for amp _ Detail Rows strColor Yellow Case 3 strColor Green Case Else strColor...
The Erase Function
When you want to remove the data from an array, you should use the Erase function. This function deletes all the data held by static or dynamic arrays. In addition, the Erase function reallocates all of the memory assigned to a dynamic array. If a procedure has to use the dynamic array again, you must use the ReDim statement to specify the size of the array. The next hands-on demonstrates how to erase the data from the array cities. Hands-On 7-9 Removing Data from an Array 1. In a new module,...
HandsOn Importing a Schema File XSD to an Access Database
1. Create a new Access database named Acc2003_Chap29.mdb. 2. In the Database window, choose File Get External Data Import. 3. In the Import dialog box, select the Shippers.xsd file in the Learn_XML folder and click the Import button. Access displays the Import XML dialog box as shown in Figure 29-14. Notice that you cannot indicate which columns you would like to import. Access always imports the entire XSD file. The Options button in the Import XML dialog box is always disabled during the...
CommandExecute
The CommandExecute event occurs after a user selects a command from the menu bar or toolbar. You can use this event when you want to execute a set of commands after a particular command is executed. This event takes the Command argument that returns a PivotCommandId constant, which allows you to determine which command the user executed. You can use the Object Browser to view the list of commands for this constant. The event procedure in Hands-On 24-34 checks whether the user used the Copy...
HandsOn Using Intrinsic Constants in a VBA Procedure
1. Open any form in Design view. You can use the Customers form in the Acc2003_Chap01.mdb file that you worked with in Chapter 1. 2. In the Design view of the chosen form, choose Edit Select Form. 4. Click the Event tab in the Form properties sheet. 5. Click the Build button to the right of the On Filter property, and select Code Builder. 6. In the Code window, enter the Form_Filter event procedure code as shown below. 7. Switch back to Design view in the Customers form and choose View Form...
HandsOn Working with Object Variables
1. Load another instance of Microsoft Office Access 2003 and open the Acc2003_Chap01.mdb file that you worked with in Chapter 1. Both instances need to be open for this example. 2. Press Alt F11 to open Visual Basic Editor window. 3. Choose Insert Module to add a new module. 4. Enter the code of the HideControl procedure shown below in the new module's Code window. 5. To run the procedure, click any line between the Sub and End Sub keywords and press F5 or choose Run Run Sub UserForm. ' this...
HandsOn Writing the FormCommandEnabled Event Procedure
1. In the Visual Basic Editor window, double-click Form_Sales Analysis Subform 1. 2. The Code window shows several event procedures you prepared earlier in this chapter. Enter the following Form_CommandEnabled event procedure below the last procedure code. Private Sub Form_CommandEnabled ByVal Command As Variant, _ If Command OWC11.plCommandCopy Then If Enabled.Value True Then Debug.Print Copy command enabled. Debug.Print Copy command disabled. End If End If End Sub 3. To test the above event...
ApplyFilter
The ApplyFilter event occurs when you apply the filter to restrict the records. This event takes place when you select the Apply Filter Sort, Filter by Selection, or Remove Filter Sort options. Use this event to change the form display before the filter is applied or undo any changes made when the Filter event occurred. The ApplyType argument can be one of the predefined constants shown in Table 24-1 ApplyType argument constants Table 24-1 ApplyType argument constants The event procedure in...
Transaction Processing
To improve your application's performance and to ensure that database activities can be recovered in case an unexpected hardware or software error occurs, consider grouping sets of database activities into a transaction. A transaction is a set of operations that are performed together as as a single unit. If you use an automatic teller machine ATM , you are already familiar with transaction processing. When you go to the bank to get cash, your account must be debited. In other words, the cash...
Database Errors
In VBA, you can trap errors in your procedures by using the special On Error On Error Resume Next On Error GoTo 0 To determine the cause of the error, you need to check the value of the Number property of the VBA Err object. The Description property of the Err object contains the message for the encountered error number. When using ADO to access data, in addition to the VBA Err object, you can get information about the errors from the ActiveX Data Objects ADO Error object. When an error occurs...
Removing Objects from a Custom Collection
Removing an item from a custom collection is as easy as adding an item. To remove an element, use the Remove method in the following format The object is the name of the custom collection that contains the item you want to remove. The item is the item you want to remove from the collection. To demonstrate the process of removing an item from a collection, let's work with the following hands-on that modifies the NewEmployees procedure that you prepared in Hands-On 8-1. Hands-On 8-2 Removing...
Events Recognized by Controls
In addition to the events for forms and reports introduced in Chapters 24 and 25, you can control a great many events that occur for labels, text boxes, combo and list boxes, option buttons, check boxes, and other controls installed by default with an Access application. These events make it possible to manage what happens on a field level. The best way to learn about events that a form, report, and or control can respond to is to develop an application that addresses a specific problem. The...
HandsOn Writing the FormSelectionChange Event Procedure
1. In the Visual Basic Editor window, double-click Form_Sales Analysis Subform 1. 2. The Code window shows several event procedures you prepared earlier in this chapter. Enter the following Form_SelectionChange event procedure below the last procedure code. Debug.Print Selection Type amp Me.PivotTable.SelectionType End Sub 3. To test the above event procedure, open Sales Analysis Subform 1 in Form view. Because you've already created several event procedures for this form, various events will...
HandsOn Writing the FormBeforeDelConfirm Event Procedure
This hands-on uses the Product Categories form created in Hands-On 24-6. 1. In the Visual Basic Editor window, double-click Form_Product Categories, which was created in Hands-On 24-6 and modified in Hands-On 24-7. 2. In the Code window, two event procedures are shown that were prepared in Hands-On 24-6 and 24-7. Enter the following Form_BeforeDelConfirm event procedure below the last procedure code. Private Sub Form_BeforeDelConfirm Cancel _ As Integer, Response As Integer Response...
Creating and Joining Workgroup Information Files
When you open a database, Microsoft Access reads the workgroup information file to find out who is allowed to access the database. If security was put into place, you will be prompted for the user ID and password. The first hands-on in this chapter will walk you through the steps required to create and join a new workgroup information file. Once you join the workgroup, you will create a new Access database and set up a password for the Admin user. This information will be saved in the workgroup...
Importing to XML Using the ImportXML Method
Use the ImportXML method to programmatically import an XML data file and or schema file. The ImportXML method takes two arguments, as shown in Table 29-4. Table 29-4 Arguments of the ImportXML method in order of appearance Table 29-4 Arguments of the ImportXML method in order of appearance Specifies the full path of the XML file to import. acAppendData 2 acStructureAndData 1 acStructureOnly 0 Specifies whether to import structure only 0 , import structure and data 1 default , or append data 2 ....
Transforming AttributeCentric XML Data into an HTML Table
As you've seen in earlier examples, after creating an XML file from an ADO recordset, the generated output contains attribute-centric XML. To import this type of output to Access you had to create a special stylesheet and apply the transformation to convert the attribute-centric XML to the element-centric XML that Access supports. But what if you simply want to display the XML file created from an ADO recordset in a web browser You can create a generic XSL stylesheet that draws a simple HTML...
HandsOn Deleting a Stored Query
1. In the Visual Basic Editor window, choose Insert Module. 2. In the module's Code window, type the Delete_Query procedure shown below. 3. Choose Run Run Sub UserForm to execute the procedure. Dim cat As New ADOX.Catalog Dim strPath As String strPath CurrentProject.Path amp Northwind.mdb cat.ActiveConnection amp _ Data Source amp strPath cat.Views.Delete London Employees MsgBox Query does not exist. Else MsgBox Err.Number amp amp Err.Description End If
Checking Permissions for Objects
You can retrieve the permissions for a particular user or group on a particular object with the ADOX GetPermissions method. Because this method returns a numeric permission value for the specified object, if you want to display the names of constants representing permissions, you must write more code to decipher the returned value. The procedure in Hands-On 17-13 demonstrates how to retrieve the permissions set for the PowerUser on the Customers table in a sample database. Hands-On 17-13...
Sinking Events in Standalone Class Modules
Instead of writing your event procedures in the form and report class modules, you can make the maintenance of your Microsoft Access applications much simpler by writing the event code in standalone class modules. Recall that a standalone class module is a special type of a class module that is not associated with any particular form or a report. This class module can be inserted in the Visual Basic Editor window by choosing Insert Class Module. In addition to creating custom objects see...
HandsOn Counting the Number of Returned Records
1. In the Visual Basic Editor window, choose Insert Module. 2. In the module's Code window, type the CountRecords procedure shown below. 3. Choose Run Run Sub UserForm to execute the procedure. Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim myarray As Variant Dim returnedRows As Integer Dim r As Integer 'record counter Dim f As Integer 'field counter Set conn CurrentProject.Connection Set rst New ADODB.Recordset rst.Open SELECT FROM Employees, _ conn, adOpenForwardOnly,...