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

Create Custom Input Forms Powershell

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

Events Event Properties and Event Procedures

In order to customize your database applications or to deliver products that fit your users' specific needs, you'll be doing quite a bit of event-driven programming. Access 2003 is an event-driven application. This means that whatever happens in an Access application is the result of an event that Access has detected. Events are things that happen to objects and can be triggered by the user or by the system, such as clicking a mouse button, pressing a key, selecting an item from a list, or...

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

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

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

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

Custom Project Using Hierarchical Recordsets

Treeview Control Vba

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

Retrieving the First Matching Node

If all you want to do is retrieve the first node that meets the specified criterion, use the SelectSingleNode method of the DOMDocument object. As the argument of this method, specify the string representing the node you'd like to find. For example, the following procedure finds the first node that matches the criterion CompanyName in the Shippers.xml file Dim xmldoc As MSXML2.DOMDocument50 Dim xmlSingleNode As MSXML2.IXMLDOMNode Set xmldoc New MSXML2.DOMDocument50 xmldoc.async False...

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

HandsOn Writing the Detail Format Event Procedure

Use the rptCustomers report you created in Hands-On 25-1. 2. In the Visual Basic Editor Code window for the rptCustomers report, type the following Detail_Format event procedure. Do not type the Option Compare Database and Option Explicit statements if they are already at the top of the Code window. Option Compare Database Option Explicit Private Sub Detail_Format Cancel As Integer, _ FormatCount As Integer If shaded Then Me.Detail.BackColor vbWhite End If Notice that at the top of the...

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

Static and Dynamic Arrays

The arrays introduced thus far are static. A static array is an array of a specific size. You use a static array when you know in advance how big the array should be. The size of the static array is specified in the array's declaration statement. For example, the statement Dim Fruits 10 As String, declares a static array called Fruits that is made up of 10 elements. But what if you're not sure how many elements your array will contain If your procedure depends on user input, the number of...

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

HandsOn Creating a Shaped Recordset

Insert a new module and, in the module's Code window, enter the ShapeDemo procedure as shown below. Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim rstChapter As Variant Dim strConn As String Dim shpCmd As String ' define database connection string ' using the OLE DB provider ' and Northwind database as Data Source strConn Data Provider Microsoft.Jet.OLEDB.4.0 strConn strConn amp Data Source C Program Files amp _ Microsoft ' specify Data Shaping provider ' and open connection to...

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