HandsOn Using the Msg Box Function with Arguments

In the Visual Basic Editor window, choose Insert Module to add a new module. 2. In the Code window, enter the MsgYesNo subroutine as shown below. Dim question As String Dim myButtons As Integer question Do you want to open a new report myButtons vbYesNo + vbQuestion + vbDefaultButton2 MsgBox question, myButtons End Sub In the above subroutine, the question variable stores the text of your message. The settings for the buttons argument are placed in the myButtons variable. Instead of using the...

Connecting to a Microsoft Access Database via DSN

For the web server to access a database, you need to define an ODBC data source. A data source contains the information required to connect to a data provider, in this case, a Microsoft Access database. Earlier in this book you learned about three types of data sources User DSN, File DSN, and System DSN. File DSNs offer the most flexibility for web work. Because the information required to connect to the data source is stored in a text file, file DSNs can be readily shared with other users and...

Exporting XML Data

You can export tables, queries, forms, and or reports to XML files from an Access database (.mdb) file or an Access project (.adp) file. There is no XML support for macros and modules. When you export a form or report, you actually export the data from the form or report's underlying table or query. Access uses a special XML vocabulary known as ReportML for representing its objects as XML data. ReportML is an XML file that contains tags describing properties, methods, events, and attributes of...

HandsOn Writing the Form Error Event Procedure

Create a new form based on the Customers table. Add all the fields from the Customers table and save the new form as Customers Data Entry. 2. In the Design view of the Customers Data Entry form, choose Edit Select Form. 3. Choose View Properties to activate the Form properties sheet. 4. Click the Data tab and set the form's DataEntry property to Yes. 5. Click the Event tab, set the On Error property to Event Procedure , and press the Build button ( ). 6. Access will create the event procedure...

Introduction to Active Server Pages

With Active Server Pages (ASP), a technology developed by Microsoft, you can design and program powerful and dynamic web applications. The current version of ASP is 3.0, and it is available with Internet Information Services (IIS) 5.0. Active Server Pages are text files with the .asp extension. These files contain standard HTML formatting tags and embedded scripting statements. Because the default scripting language for ASP is VBScript, a subset of Visual Basic and Visual Basic for...

Copying Records to an Excel Spreadsheet

The procedure in Hands-On 14-5 uses automation to copy records from the Employees table to an Excel spreadsheet. Once the recordset is opened, the Excel part is handled by object variables that point to the Excel Application object (myExcel), Excel Workbook object (wkb), Excel Worksheet object (wks), and Excel Range object (StartRange). Before you can use any of these objects you must set a reference to the Microsoft Excel Object Library. The result of copying a recordset to a worksheet is...

Index

- (hyphen), 266 (exclamation point), 266 (Single) type declaration character, 35 (Double) type declaration character, 35 (number sign), 266 INCLUDE FILE directive, 570 (String) type declaration character, 35 (Integer) type declaration character, 35 (percent sign), 266, 395 & (concatenation), 33 & (Long) type declaration character, 35 (asterisk), 266, 655 (inXML), 619 WKGRP, 343 (question mark), 27, 266, 395, 571 (at sign), 450 (Currency) type declaration character, 35 * (in XML), 661, 665...

HandsOn Providing Easy Access to Data with Frames

This hands-on uses the following four HTML files Review.html, Logo.html, Examples.html, and Results.html. Creates a page containing two frames and breaks one frame into two rows (resulting in three areas visible on the screen). Specifies what should be displayed within each frame and tells browsers that do not support frames to ignore them. Places a company logo and a hyperlink to navigate to the company web site. Creates jumps to this chapter's hands-on examples. Used for dumping information...

HandsOn Copying Records to an Excel Spreadsheet

Switch to the Visual Basic Editor window and insert a new module. 2. Choose Tools References in the Visual Basic Editor window, scroll down to locate the Microsoft Excel Object Library, click the check box next to it, then click OK to exit. 3. In the Code window, enter the CopyToExcel procedure as shown below. Option Compare Database Option Explicit ' be sure to select Microsoft Excel Object Library ' in the References dialog box Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim wbk...

Custom Project Applying a Stylesheet to an XML Data File with the TransformXML Method

This custom project uses the copy of the Northwind sample database you created in Hands-On 29-8. Part 1 Creating a Custom Stylesheet for Transforming an XML Source File into Another XML Data File 1. Open Notepad and enter the statements as shown below. < xml version L0 > < xsl stylesheet version 1.0 < xsl output method xml indent yes > < xsl template match > < dataroot> < xsl for-each select Employees> < Extensions> < LastName> < xsl value-of select LastName >...

HandsOn From Access to Excel Loading an XML File into an Excel Workbook

In the Visual Basic Editor window, choose Insert Module to add a new standard module to the current VBA project. 2. In the module's Code window, enter the OpenAdoFile procedure as shown below. Dim rst As ADODB.Recordset Dim objExcel As Excel.Application Dim wkb As Excel.Workbook Dim wks As Excel.Worksheet Dim StartRange As Excel.Range Dim h as Integer rst.Open Provider MSPersist ' display the number of records MsgBox There are & rst.RecordCount & records & _ in this file. Set objExcel...

Opening a Microsoft Excel Spreadsheet

You can open external data sources supported by the Microsoft Jet database engine by using ADO and the Microsoft Jet 4.0 OLE DB provider. Use the Extended Properties of the Connection object to pass the connection string. Hands-On 10-6 demonstrates how to open a Microsoft Excel spreadsheet named C Report.xls by using ADO. Hands-On 10-6 Opening an Excel Spreadsheet with ADO This hands-on uses the Report.xls spreadsheet file included in the book's downloadable files. You can modify the procedure...

Creating the User Interface

Employee Data Entry Vba Excel

Implementing our custom CEmployee object requires that you design a form to enter and manipulate employee data. Custom Project 8-1 Step 7 Designing a User Form 1. In the Database window, click the Forms object button and double-click Create form in Design View. 2. Save the form as frmEmployeeSalaries. 3. Use the toolbox to place controls on the form as shown in Figure 8-1. Introduction to Access 2003 VBA Programming Figure 8-1 This form demonstrates the use of the CEmployee custom object. 4....

Declaring and Raising Events

Standalone class modules automatically support two events Initialize and Terminate. Use the Initialize event to give the variables in your classes initial values. The Initialize event is called when you make a new instance of a class. The Terminate event is called when you set the instance to Nothing. In addition to these default events, you can define custom events for your class module. To create a custom event, use the Event statement in the declaration section of a class module. For...

HandsOn Creating a Shaped Recordset with Multiple Children

Note Because this hands-on retrieves data from the Northwind database, adjust the path found in the procedure code to point to the correct location of this file on your computer. 1. Insert a new module and, in the module's Code window, enter the ShapeMultiChildren procedure as shown below. Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim rstChapter1 As Variant Dim rstChapter2 As Variant Dim strConn As String Dim shpCmd As String Dim strParent As String Dim strChild1 As String Dim...

Using Breakpoints

Margin Indicator Bar Vba

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...

List Properties Methods

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

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...

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...

HandsOn Importing a Schema File XSD to an Access Database

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...

Events Recognized by Controls

School Data Entry Form

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...

Changing a User Password

User passwords are stored in the workgroup information file. To change a user's password from your VBA code, use the ADOX User object's ChangePassword method. This method takes as parameters the user's current password and the new password. If a user does not yet have a password, use an empty string for the user's current password. The procedure in Hands-On 17-15 demonstrates how to change a password for the Admin user. Recall that Admin is the default user account that has a blank password. In...

Custom Project Using Hierarchical Recordsets

Excel 2003 Vba Formparent

Part 1 Creating a Form with a TreeView Control 1. In the left pane of the Database window, click the Forms object button, and click the New button. 2. In the New Form window, choose Design View and click OK. The Form design window opens. 3. Choose Insert ActiveX Control. 4. In the ActiveX Control box, choose Microsoft TreeView Control 6.0 as shown in Figure 16-8, and click OK to place a TreeView control on the form. Figure 16-8 The Microsoft TreeView control provides an excellent way to display...

Understanding and Using Conditional Compilation

When you run a procedure for the first time, Visual Basic converts your VBA statements into the machine code understood by the computer. This process is called compiling. You can also perform the compilation of your entire VBA project before you run the procedure by choosing Debug Compile name of the current VBA project . You can tell Visual Basic to include or ignore certain blocks of code when compiling and running by using conditional compilation. Your procedure may behave differently...

Linking a Microsoft Excel Spreadsheet

You can link an Excel spreadsheet to a Microsoft Access database by using the TransferSpreadsheet method of the DoCmd object, as shown in Hands-On 11-9. Note, however, that neither the DoCmd object nor its Transfer-Spreadsheet method are members of the ADO Object Model. The DoCmd object is built into the Microsoft Access library. Hands-On 11-9 Linking an Excel Spreadsheet This hands-on uses the Regions.xls spreadsheet file provided in the book's downloadable files. You can revise the procedure...

Opening a dBASE File Using Msdasql Provider

MSDASQL is the Microsoft OLE DB provider for ODBC. This provider works with all ODBC data sources. You can use MSDASQL to access data located in an external data source such as a dBASE file. When you connect to a database via the ODBC, you must specify the connection information. You do this by creating the DSN Data Source Name via the ODBC Administrator. The ODBC icon is located in the Windows Control Panel. If you are using Windows 2000 or Windows XP, open the Administrative Tools in the...

Introduction to ADO Recordsets

Recordset Type

The Recordset object represents a set of records in a table, or a set of records returned by executing a stored query or an SQL statement. The Recordset object is one of the three most-used ADO objects the other two are Connection and Command . What you can do with a recordset depends entirely on the built-in capabilities of its OLE DB provider. You can open a recordset by using the recordset's Open method. The information needed to open a recordset can be provided by first setting properties,...

Exporting to XML Using the ExportXML Method

Use the Microsoft Office Access 11.0 Object Library ExportXML method of the Application object to export XML data, schemas XSD , and presentation information XSL from a Microsoft Access database .mdb , Microsoft SQL Server 2000 Desktop Engine MSDE 2000 , or Microsoft SQL Server 6.5 or later. The ExportXML method takes a number of arguments that are shown in Table 29-1. Table 29-1 Arguments of the ExportXML method in order of appearance Table 29-1 Arguments of the ExportXML method in order of...

Loading an XML Document in Excel

After saving an ADO recordset to an XML file on disk see Hands-On 29-13 earlier in this chapter , you can load it into a desired application and read it as if it were a database. To gain access to the records saved in the XML file, use the Open method of the Recordset object and specify the filename, including its path and the persisted recordset service provider as Provider MSPersist. The following hands-on exercise demonstrates how to open a persisted recordset and write its data to an Excel...