| s-2
| About ANSI SQL query mode |
| s-3
| Some of the content in this topic may not be applicable to some languages. |
| s-4
| You can create SQL queries in one of two ANSI SQL query modes: |
| s-5
| ANSI-89 describes the traditional Jet SQL syntax. |
| s-6
| This mode conforms closely to the ANSI-89 Level 1 specification, but is not ANSI-89 Level 1 compliant. |
| s-7
| Certain ANSI-89 SQL features are not implemented and the wildcard characters conform to the Visual Basic for Applications (VBA) specification, not SQL. |
| s-8
| ANSI-92 provides new reserved words, syntax rules, and wildcard characters that enhance your ability to create queries, filters, and SQL statements. |
| s-9
| This mode conforms closely to the ANSI-92 Level 1 specification, but is not ANSI-92 Level 1 compliant. |
| s-10
| This query mode has more of the ANSI syntax, and the wildcard characters conform to the SQL specification. |
| s-11
| In Microsoft Access 2000 using ADOX, you could programmatically create queries that used ANSI-92 SQL syntax. |
| s-12
| However, any queries you created were not visible in the Database window because there was no option to set this mode in the user interface. |
| s-13
| Now in Access 2002, you can set the ANSI SQL query mode through the user interface for the current database and as the default setting for new databases. |
| s-14
| Why use ANSI-92 SQL? |
| s-15
| You may want to use ANSI-92 SQL for the following reasons: |
| s-16
| You anticipate upsizing your application in the future to an Access project and want to create queries that will run with minimal changes in a Microsoft SQL Server database. |
| s-17
| You want to take advantage of the new features not found in ANSI-89 SQL, such as: |
| s-18
| Changing security settings by using the GRANT and REVOKE SQL statements |
| s-19
| Using DISTINCT in an aggregate function reference, for example, SUM-DISTINCT-Price |
| s-20
| Using the LIMIT TO nn ROWS clause to limit the number of rows returned by a query |
| s-21
| Why you should avoid mixing queries created under different ANSI SQL query modes |
| s-22
| The ANSI SQL query mode default for new Access databases in 2002 and 2000 file formats |
| s-23
| ANSI-89 is the default setting for a new Access database in 2002 and 2000 file format. |
| s-24
| You can not set the SQL query mode new database default to ANSI-92 in 2000 file format because the option is disabled; |
| s-25
| ANSI-89 is the only query mode setting available for a database in Access 2000 file format. |
| s-26
| About using connection files |
| s-27
| A connection file stores information about a connection to a data source (such as an OLE DB data source) and the data associated with the connection. |
| s-28
| In Microsoft Access, you can use a connection file to bind one or more data access pages to a data source. |
| s-29
| When you open a page, Access reads the connection file that is linked to the page, and based on the contents of the connection file, connects the page to the appropriate data source. |
| s-30
| A connection file uses one of the following file formats: |
| s-31
| Office Data Connection (.odc) |
| s-32
| A file format that uses HTML and XML to store the connection information. |
| s-33
| You can view or edit the contents of the file in any text editor. |
| s-34
| Universal Data Link (.udl) |
| s-35
| A standard file format provided by Microsoft Data Links to create file-persistent OLE DB data source object definitions. |
| s-36
| Benefits of using a connection file |
| s-37
| Using a connection file simplifies the task of deploying related data access pages. |
| s-38
| Pages that connect to a common data source can share a single connection file. |
| s-39
| If you move or copy the data source, instead of updating the ConnectionString property of each dependent page, you only need to edit the connection information in the connection file to make the pages point to the right location or database. |
| s-40
| Linking a page to a connection file |
| s-41
| When creating a data access page, you can choose whether you want to link the page to a connection file or simply use a connection file without creating a link. |
| s-42
| You can also choose whether you want to use an existing connection file or create a new one. |
| s-43
| Linking to a connection file |
| s-44
| When you establish a link between the connection file and a page, the page's ConnectionFile property is set to the name of the file. |
| s-45
| Each time you open the page, Access will read the connection file, extract the connection information, and set the ConnectionString property of the page. |
| s-46
| Using a connection file without creating a link |
| s-47
| When creating a page, you can use the contents of a connection file to set the ConnectionString property of the page, but choose not to create a link between the page and the connection file. |
| s-48
| Changing the connection information of a page |
| s-49
| When a connection file is used |
| s-50
| Either change the ConnectionFile property of the page to point to a different connection file, or edit the connection file in a text editor. |
| s-51
| If you choose to edit the connection file, remember that all other pages that use the connection file will also be affected by the changes you make. |
| s-52
| If you edit the ConnectionString property of a page that is linked to a connection file, the link will be broken and the ConnectionFile property will be set to null. |
| s-53
| When a connection file is not used |
| s-54
| Either create a link between the page and a connection file by setting the ConnectionFile property, or edit the ConnectionString property. |
| s-55
| When you create a link, Access will automatically set the ConnectionString property based on the contents of the connection file. |
| s-56
| Convert a previous-version data access page |
| s-57
| A page created using Microsoft Access 2000 must be converted before it can be used with Access 2002. |
| s-58
| Open the page in Access 2002 Design view. |
| s-59
| Access will create a backup copy of the page (pagefilename.bak.htm) at the same location as the original data access page file. |
| s-60
| If you want to revert to the Access 2000 version of the page, delete the converted file, rename the backup copy, and then connect the page to the database. |
| s-61
| If the data access page includes any Microsoft Office Web Components (a PivotTable list, chart, or spreadsheet), only users with a valid Microsoft Office 2002 license will be able to use those components. |
| s-62
| About analyzing data |
| s-63
| Microsoft Access provides several techniques to help you analyze data. |
| s-64
| Create a crosstab query |
| s-65
| You can use a crosstab query to calculate and restructure data for easier analysis. |
| s-66
| Crosstab queries calculate a sum, average, count, or other type of total for data that is grouped by two types of information – one down the left side of the datasheet and another across the top. |
| s-67
| Comparison of a crosstab and a select query |
| s-68
| 1 A select query |
| s-69
| 2 A crosstab query based on the select query |
| s-70
| Add a PivotTable list to a data access page |
| s-71
| A PivotTable list is an interactive table that you can use to analyze data dynamically from within a Web browser. |
| s-72
| At run time, users can change the layout of the PivotTable list. |
| s-73
| They can move the fields that are displayed in the row, column, and data area of the PivotTable list, or add or remove fields from the list. |
| s-74
| Users can also filter, sort, and group data. |
| s-75
| The following illustration shows a PivotTable list on the Orders data access page. |
| s-76
| PivotTable list on a data access page |
| s-77
| View a datasheet or form in PivotTable view |
| s-78
| Access 2002 provides a PivotTable view for datasheets and forms. |
| s-79
| In this view, you can build an interactive report using the fields underlying the datasheet or form. |
| s-80
| The view uses a Microsoft Office PivotTable Component, so you can do all the things that you can do on a PivotTable list. |
| s-81
| You can add fields to the view, move or remove fields, and filter, sort, and group data. |
| s-82
| The following illustration shows the Employees table in PivotTable view. |
| s-83
| Datasheet in PivotTable view |
| s-84
| About customizing the layout of a PivotTable or PivotChart view |
| s-85
| Customizing the layout of a PivotTable view |
| s-86
| You can control how the data is presented in a PivotTable view by customizing the layout. |
| s-87
| A different layout lets you calculate and compare summarized values for different elements in your data, or display summaries for a subset of the data. |
| s-88
| Using the drop areas |
| s-89
| You change the layout by moving the fields to predefined drop areas within the PivotTable view workspace. |
| s-90
| Drop areas in PivotTable view |
| s-91
| 1 Drop areas |
| s-92
| 2 Detail area |
| s-93
| After you move fields to the drop areas so that the drop area captions are covered up, you can still drag additional fields to the areas. |
| s-94
| Moving fields to the row or column areas |
| s-95
| When you move a field to the row area, the unique items of data within the field are displayed down the rows of the PivotTable view. |
| s-96
| When you move a field to the column area, the unique items of data are displayed across the columns. |
| s-97
| Moving fields to the detail area |
| s-98
| To view total or detail data from the underlying record source, move fields to the detail area. |
| s-99
| Moving fields to the filter area |
| s-100
| Filter fields allow you to confine the view to a particular part of the available data. |
| s-101
| For example, when you move a Product field to the filter area, you can display data for one product at a time. |