Copyright: Copyright ® 1996 by Software AG


ADABAS D OfficePlus
Contents

Ordering the Manual

Introduction

Introduction

QueryPlus

QueryPlus

Starting QueryPlus

Start Parameters of QueryPlus

Working with QueryPlus

Starting QueryPlus from Windows Applications

DDE Support of QueryPlus

QueryPlus Options

ADABAS D AccessPlus

ADABAS D AccessPlus

Requirements for Working with AccessPlus

Installing AccessPlus

Starting AccessPlus

Logging in to the Database

The Attaching Wizard

The Upsizing Wizard

ODBC Driver

General Information


Ordering the Manual

Manual Order Number:  ESD611-043WOU

This online help is applicable to ADABAS D Version 6.1.1 PE and to all subsequent releases, unless otherwise indicated in new editions or technical newsletters.

Specifications contained herein are subject to change and these changes will be reported in subsequent revisions or editions.

Readers' comments are welcomed. Comments may be addressed to the Documentation Department at the address on the back cover.

©     Dezember 28, 1995, SOFTWARE AG, Germany & SOFTWARE AG of North America, Inc.

All rights reserved

Printed in the Federal Republic of Germany

The SOFTWARE AG documentation often refers to numerous hardware and software products by their trade names. In most, if not all cases, these designations are claimed as trademarks or registered trademarks by their respective companies.


Introduction - Introduction
Introduction

This online help describes the installation and handling of ADABAS OfficePlus.

ADABAS OfficePlus comprises a package of Windows applications and add-ons that offer easy access to ADABAS data directly from within standard Windows programs. The components of this package have been particularly adapted to the Microsoft Office Package, but can also be used with other Windows programs supporting DDE or the clipboard.


QueryPlus - QueryPlus
QueryPlus

QueryPlus is a program that can run under Microsoft Windows to give access to data stored on an ADABAS D server. QueryPlus is a Multi Document Interface (MDI) application, supports "drag-and-drop", allows data to be copied onto the clipboard, and supports Dynamic Data Exchange (DDE). QueryPlus completes other Microsoft Windows tools by adding data exchange with ADABAS. Data can be inserted into a mail merge document, a spreadsheet, or a presentation. Data exchange is facilitated by the possibility of linking Windows objects, such as Excel or Word documents, to saved queries or SQL statements. In this way, ADABAS data in existing documents can be updated at any time simply by clicking on a button.


Starting QueryPlus

To start QueryPlus, first select ADABAS D from Programs in the start bar, then Tool and then QueryPlus.


Start Parameters of QueryPlus

To start QueryPlus with a start parameter, select the Programs directory in the Start Menu directory in your Windows directories explorer. In the Programs directory, you find the ADABAS D programs, among them QueryPlus. Single-click on the QueryPlus icon and select Properties using the right mouse button. In the following dialog, select the cardfile card Shortcut. The desired parameters can then be specified in the Target dialog box.

The following start parameters can be specified:

/D	To display more detailed information in case problems arise while accessing data from an ADABAS server, QueryPlus has the special start parameter /D.

/Remote	To start QueryPlus for a DDE conversation, the parameter /Remote must be specified. A DDE conversation with an already started QueryPlus application does not require the parameter /Remote.

/DSN	To specify a standard data source when Connecting to the Database.

/USER	To specify a standard user when Connecting to the Database.

/PWD	To specify a standard password when Connecting to the Database.


Working with QueryPlus

QueryPlus provides you with several ways of communicating with the database. After connecting to the database, you can send SQL statements to the database, make queries, and create object links.

For this purpose, the following toolbar is available:

Before the connect:

After the connect:

The toolbar buttons have the following meanings:

Connecting to the Database

Cutting selected text while editing SQL statements (see Section Edit Menu).

Copying selected text into the clipboard while editing SQL statements (see Section Edit Menu).

Pasting text from the clipboard while editing SQL statements (see Section Edit Menu).

Undoing the last modification while editing SQL statements (see Section Edit Menu).

When viewing results or when defining a query in the Query description window, you can see and edit the individual fields in a separate window. For this purpose, you can move the mouse pointer to the desired field and press the right mouse button, then select Zoom (see Section Edit Menu)

Dialog for Direct SQL Statements

Direct SQL statements are used to formulate and subsequently execute SQL statements. You can execute SQL statements or queries directly and save the direct SQL statement for later use (see Section Direct SQL Dialog).

SQL Dialog

The SQL Dialog is used to execute already formulated and saved SQL statements or to formulate and save new SQL statements. In addition, you can modify or delete existing SQL statements (see Section SQL Dialog).

Query Dialog

The Query Dialog is used to execute already designed and saved queries, i.e., search requests, or to design and save new queries. In addition, you can modify or delete existing queries (see Section Query Dialog).

Creating MS-Word Links

In this context, linking means that the data desired for the document is stored in the database, whereas the particular position is stored in the document (see Section MS-Word Link).

Creating MS-Excel Links

In this context, linking means that the data desired for the table (i.e., the linked queries or SQL statements) is stored in the database, whereas the particular position is stored in the table (see Section MS-Excel Link).

Sharing Objects

The SQL statements, queries, MS-Word, and MS-Excel links you created are only accessible to you. You have the option of sharing these objects with other users (see Section Sharing Objects).

Stopping Sharing

This button can be used to display the objects you made accessible to other users and to stop the sharing procedure (see Section Stop Sharing).

Requesting Help

This button can be used to start the QueryPlus help. To request context-specific help, if available, use the [image] key.

See also

Connecting to the Database

Direct SQL Dialog

SQL Dialog

Query Dialog

MS-Word Link

MS-Excel Link

Linking to Other Windows Applications

Viewing and Modifying the Result of a Query

Sharing Objects

Edit Menu


Connecting to the Database

After starting, you must connect to your ADABAS server. This can be done in the following ways:

1)	Connect by Using the Database/Connect Menu

Connecting is done via the connect window. If the connect was successful, it is saved in the Database menu to be used for a direct connect.

2)	Connect Directly by Using the Database Menu

If entries for a direct connect are available in the Database menu, you can connect to the database by selecting the desired entry.

3)	Connect Automatically by Using the  [image]  Toolbar Button

If entries for direct connects were made to the Database menu, the  [image]   toolbar button can be used to repeat the last connect.

To close a current database connection, select the Database/Disconnect menu item. A disconnect can be required in the following case: you are only allowed to have one connection to the database and you want to connect to the database from another ADABAS component without leaving QueryPlus. No disconnect is needed for a reconnect. The Database/Disconnect menu appears after a connect.

In the following, these three ways will be explained in greater detail.

1)	Connect by Using the Database/Connect Menu

To connect to the database, select the Database/Connect menu. First select a data source. You can create the data source by using the ODBC Administrator.

The QueryPlus connect window then appears into which the required values are to be entered.

The username and password are automatically displayed in uppercase characters. If case-sensitivity is desired, the username and password must be enclosed in double quotation marks. The double quotation marks are also needed when the name contains special characters. If a double quotation mark occurs in the name, it must be specified twice.

For "Serverdb", the name of the database (SERVERDB) must be specified.

For "Servernode:", the network node name of the database server must be specified.

If a connect is successful, it is entered in the Database menu to enable the user to connect directly to another QueryPlus session. The specifications for User, Serverdb, and Servernode are saved. According to the selected option (see Section Save Password for a Direct Connect), the password is also saved or not. If it is saved, it is used with the entry associated with it for a direct connect. If it is not saved, the connect window is displayed with the connect data, except for the password which must still be entered.

Up to ten entries for a direct connect can be made to the Database menu. If there are already ten entries and another connect is to be added, the connect used least is deleted. Entries for a direct connect are ordered according to the frequency of use. The last connect is in first place.

If you want to remove some entries for a direct connect from the Database menu or if you want to change the order of the entries, you can do so when entering the options for a connect (see Section Save Password for a Direct Connect).

2)	Connect Directly by Using the Database Menu

The entries for a direct connect made to the Database menu indicate which user connected to which Serverdb on which Servernode. By selecting the corresponding entry, the desired user is connected to the desired database. The option set (see Section Save Password for a Direct Connect) determines whether the password is requested for the direct connect or not.

3)	Connect Automatically by Using the  [image]  Toolbar Button

If the Database menu contains entries for direct connects, QueryPlus displays in the status line the last entry from the Database menu used for a connect. If a new connect is to be performed with that entry, it is sufficient to select the  [image]  toolbar button.


Direct SQL Dialog

If you are familiar with SQL queries or if you want to use other options of the ADABAS server in addition to the database queries, or if you simply want to make an ad-hoc query, you can select the Direct SQL Dialog with the ADABAS server. To do so, first connect to the desired database (see Section Connecting to the Database). Then you can select the Direct item in the Database menu or the  [image]  toolbar button.

In the following window, you can formulate your SQL statement. To execute it, select Execute. If your SQL query produces a result, this is displayed in a new window. If you want to reuse your SQL statement at a later point in time, save it with Save (see Section SQL Dialog).

See also

Specifying Several SQL Statements


QueryPlus - Direct SQL Dialog - Specifying Several SQL Statements
Specifying Several SQL Statements

When you describe your SQL statement, you can specify several subsequent SQL statements which will be executed one after the other. To separate these statements from each other, insert a blank line between each statement. To mark the blank line as a separating line, place a '/' in front of it. The separating line can also be used to insert a comment, because all characters in this line are ignored. When saving, all separating lines and SQL statements are saved.


SQL Dialog

The SQL item in the Database menu or the  [image]  toolbar button opens a window where all SQL statements are displayed that are accessible to you. In this window, you can design and save new SQL statements, and execute, modify, or delete existing ones.

Initially, this window will be empty and you will only be able to design new SQL statements. To do so, select New. In the following window, you can then enter the SQL statement. If an SQL query is involved, you may view its result before saving it. Select Execute and a window with the result appears. Of course, you can use this result at once (see Section Viewing and Modifying the Result of a Query). If it is the desired result, you can save the SQL statement with Save. Its name can have a maximum length of 150 characters.

If there are already saved SQL statements or if you share SQL statements with other users (see Section New Sharing of Stored Objects), these SQL statements are displayed, ordered by owner. Click on the title bar to order the SQL statements by their names. In the title bar, you can see whether the SQL statements are ordered by owner or by name; the sort column is not enclosed in brackets.

If other users allow you to share their SQL statements, you can execute these statements. The owner of an SQL statement to be shared can determine whether the sharing user may only execute the statement or also read it. If you share an SQL statement for execution only, then you cannot change it. If you share it for reading, you can modify and save it as your own SQL statement.

See also

Specifying Several SQL Statements

Parameterizing SQL Statements

Executing Saved SQL Statements

Modifying and Deleting Saved SQL Statements


QueryPlus - SQL Dialog - Specifying Several SQL Statements
Specifying Several SQL Statements

When you enter your SQL statement, you can specify several subsequent SQL statements which will be executed one after the other. To separate these statements from each other, insert a blank line between each statement. To mark the blank line as a separating line, place a '/' in front of it. The separating line can also be used to insert a comment, because all characters in this line are ignored. When saving, all separating lines and SQL statements are saved.


Parameterizing SQL Statements

To parameterize an SQL statement, insert brackets wherever a variable is required. Between the brackets, you may formulate a prompt which appears in the dialog box where the text for the variable part of the SQL statement is to be entered. One SQL statement can have multiple parameters. They are retained when saving the SQL statement. If you want the columns Product ID and Price Per Unit from the table Products to remain variable, you can set the following parameters for the selection criteria:

 SELECT	"Product ID", "Price Per Unit"

 FROM	"Products"

 WHERE	"Product ID" >= [First ID:]

 AND	"Product ID" <= [Last ID:]

 And	"Price Per Unit" <= [Price Limit:]


Executing Saved SQL Statements

After you save it, your SQL statement appears in the SQL window (see Section SQL Dialog). For execution, select the desired SQL statement and Execute, or double-click on the statement. In the status line of QueryPlus at the bottom of the window, a message tells you whether the SQL statement was executed successfully or not. If an SQL query is involved, you may view its result in the following window (see Section Viewing and Modifying the Result of a Query).


Modifying and Deleting Saved SQL Statements

To modify a saved SQL statement, select the desired statement and Design. A window appears containing the saved SQL statement. You can modify the statement, view a result with Execute, and save it with Save using either the same name or a new name. If you enter a new name, the old SQL statement will be kept without any changes. Otherwise, the old SQL statement is overwritten with the modified one.

If you modify an SQL statement that another user shares with you, this modified SQL statement is saved as your property.

The modifications are not made to the original statement. If you want the owner of the original SQL statement to be able to use your modified SQL statement, you must share the modified SQL statement with him/her.

For deletion, select the corresponding saved SQL statement and Delete.


Query Dialog

If you are not familiar with SQL queries, you should select the Query Dialog. To do so, first connect to the desired database (see chapter Connecting to the Database). The Query item in the Database menu or the  [image]  toolbar button of the Query Dialog then opens a window where all the queries are displayed that are accessible to you.

Initially, this window will be empty and you will only be able to design new queries. Select New and describe your query in the following window (see Section Selecting Tables).

Before you can see the result, you must save the query with Save. Its name can have a maximum length of 150 characters.

If there are already saved queries or if you share queries with other users (see Section New Sharing of Stored Objects), these queries are displayed, ordered by owner. Click on the title bar to order the queries by name. In the title bar, you can see whether the queries are ordered by owner or by name; the sort column is not enclosed in brackets.

If other users allow you to share their queries, you can execute these queries. The owner of a query to be shared can determine whether the sharing user may only execute the query or also read it. If you share a query for execution only, then you cannot change it. If you share it for reading, you can modify and save it as your own query.

See also

Selecting Tables

Selecting Columns from Tables

Synonyms for Column Names

Showing Columns in the Result

Defining the Sort Order

Selection Criteria for a Column

Parameterizing Queries

The AND Operation

The OR Operation

The Individual Predicates

The JOIN Predicate

Viewing the Pertinent SQL Statement

Executing a Saved Query

Modifying a Saved Query

Deleting a Saved Query


Selecting Tables

To design a query, you must first specify the database tables you need for the formulation of the query. For this purpose, a selection window with tables is displayed. If you create a new query, this window appears automatically. If you modify an existing query, this window appears after selecting Tables.

Initially, you will only see your own tables, if any, in the Add Tables window. If you do not have your own tables, or if you want to add tables of other database users to your query, you can use the Owners of Tables combo box to specify the database user whose tables you want to see. You can also specify that you want to see all tables that are accessible to you. It depends on the Show Tables setting which tables are displayed (see Section Show Tables). It is possible to select and add several tables at the same time. To select a continuous block, use the [image] key; to select several independent tables, use the [image] key.

In the query design window, the selected tables are tiled on output. Afterwards, they can be moved freely. If all the tables cannot be displayed simultaneously, the scroll bars below and on the right of the displayed tables can be used.

If one of the available tables is to be deleted from your query, simply select the table to be deleted and press the [image] key on your keyboard.


Selecting Columns from Tables

The query design window consists of two parts. The top half contains the tables selected for the request. Here you can select the columns desired for the result and join tables to each other (see Section The JOIN Predicate). The bottom half contains a more detailed description of the result in the form of a table. Here you can define the position, and, if needed, a synonym, the sort order, and other criteria for the individual columns (see Sections Synonyms for Column Names, The JOIN Predicate).

To select a column for the result, click on the desired column of the particular table in the top half of the query design window and drag the mouse pointer to the desired column of the table for designing the result.

During this drag-and-drop procedure, the mouse pointer changes its form. The selected column cannot be dropped in the database table from which it was selected or in the areas of the screen where the mouse pointer takes the shape of a circle with a diagonal bar. You can drop the column when the mouse pointer appears as a rectangle containing the word Column and is positioned in another database table or the table for designing the result. If another table was selected from the top half of the query design window, a join (see Section The JOIN Predicate) is established between the columns of the two tables, as illustrated by the join line that is then displayed. If the table concerned is the table for designing the result, the particular columns are passed to the result.

You can also select or modify a column of the table for designing the result by selecting the row Field of a column in the table for designing the result. In a combo box, the selected cell contains all columns of all tables you inserted in the top half of the Query design window. From this combo box, you can select the columns to be displayed in the result column.

Unless otherwise specified, the column is displayed in the result table. A column can, however, be available in the result table without being displayed. This can be especially useful, when you want to realize an AND operation with that particular column. To set a selected column to invisible, remove the selection from the row Show: of the table for designing the result.

To delete a column from the table for designing the result, select the name of the column and press the [image] key on your keyboard.


Synonyms for Column Names

Since the column names in the table for designing the result are composed of the owner, the particular table name, and the column itself, without reflecting the actual meaning of the column, you can specify a synonym under the column name. This synonym is then used for the column in the result.


Showing Columns in the Result

After selecting a column from one of the tables and dragging it into the table for designing the result, the column is shown in the result by default. If the column is not to be displayed in the result, click on the row Show: in the corresponding column.


Defining the Sort Order

Unless otherwise specified, a selected column is not sorted. The sort order can be changed for the particular column by selecting the cell Sort:. To do so, select one of the sort orders in the combo box. You can select the sort orders Ascending, Descending, or (not sorted). If no sort order or (not sorted) was selected for a column, then the cell for the sort order of the corresponding column remains empty.


Selection Criteria for a Column

To formulate selection criteria for a column, use the cells starting with the row Criteria. Here you can specify, for example, that only particular rows of a table appear in your result. A selection criterion can have a maximum length of 255 characters.

For the formulation of a selection criterion, the comparison values for the corresponding column must be inserted according to the data type of the column. This means, if the data type for the particular column is, e.g., a character string, the comparison value must be enclosed in single quotation marks. To link several selection criteria, AND or OR operations can be used (see Sections The AND Operation and The OR Operation). AND operations must always be formulated in one criterion line; OR operations in more criterion lines.


Parameterizing Queries

To formulate selection criteria, queries can be parameterized. To do so, insert brackets wherever the selection criterion of a query needs to be variable. Between the brackets, you may formulate a prompt which appears in the dialog box where the text for the variable part of the query is to be entered. A single query can have multiple parameters which are kept when saving the query. If you have, for example, selected the column Order ID. from the table Orders, you can set the following parameters for the selection criteria:


The AND Operation

The criteria for all columns formulated on one line are interpreted as AND operations. A column can be used for an AND operation without being visible. To simplify the formulation of AND operations, you can use the same column a number of times in the query (see Section Showing Columns in the Result).


The OR Operation

Criteria located on different lines are interpreted as OR operations. A column can be used for an OR operation without being visible. To simplify the formulation of OR operations, you can use the same column a number of times in the query.


The Individual Predicates

For the formulation of a selection criterion, all ADABAS predicates are supported, with the exception of those for which a subquery (see Reference online help under Predicates) had to be formulated or a value has to be compared to one or two columns. If you use predicates to formulate selection criteria, review the general conditions valid for this purpose (see Section Selection Criteria for a Column).


The JOIN Predicate

The join predicate serves to join tables. To join two columns from two tables, select the first column from the first table, hold down the mouse button and select the second column from the second table. A connecting line between the columns indicates that two tables are joined via two columns.

To specify the created join, select Joins or double-click on the connecting line that represents the join. Here you can see all existing joins, and enter the type and comparison operator for a join. To delete a join, select Joins or double-click on the connecting line that represents the join and Delete, or press the [image] key.


Viewing the Pertinent SQL Statement

To view the SQL statement pertaining to a query, select Show SQL. A window with the corresponding SQL statement appears.


Executing a Saved Query

To view the result of a query, the query must have been previously saved (see Section Query Dialog). Then it appears in the list of saved queries. If you want to see the result of a previously saved query, select the corresponding query and Execute or double-click on the query. The result is displayed in a separate window (see Section Viewing and Modifying the Result of a Query). It can be processed immediately.


Modifying a Saved Query

For modification, select the corresponding saved query and Design. A window for designing a query appears containing the search request saved within the query. You can modify and save the query with Save using either the same name or a new name. If you enter a new name, the old query is kept without any changes. Otherwise, the old query is overwritten with the modified one.


Deleting a Saved Query

For deletion, select the corresponding saved query and Delete.


MS-Word Link

The MS-Word Link menu item in the Database menu or the  [image]  toolbar button opens a window showing all the MS-Word links that are accessible to you (see Sections Linking Microsoft Word Documents with Data and Microsoft Word Mail Merge). In this window, you can create and save new MS-Word links, modify, or delete existing ones.

Initially, the window is empty and you will only be able to create new MS-Word links. Select New and describe your MS-Word link in the following window.

Enter the name of the Microsoft Word document to which data is to be linked. Then specify the link position. The description of the position consists of two captions in your Microsoft Word document. The first caption describes the start of link data, the second the end. If no position is specified for a link or if QueryPlus does not find the specified captions, Microsoft Word asks whether or not the link data is to be inserted at the insertion point (see Section Linking Microsoft Word to ADABAS via QueryPlus.).

After describing the position for a link, you can link a saved SQL statement or query to your document. You can also link several SQL statements or queries to an MS-Word document. The linked SQL statements or queries are shown in tabular format with their positions within the document.

You can also insert SQL statements and queries that other users share with you into your MS-Word link.

If you want to view the result of an SQL statement or query, select the corresponding SQL statement or query in the table and Execute. If you want to remove an SQL statement or query as a partial link, select Delete. If you have created all the links desired for the document, you can save the document with Save. QueryPlus uses the name of the link object for saving; in this case, the name of the document. This name can have up to 150 characters.

If there are already saved MS-Word links or if you share MS-Word links with other users (see Section New Sharing of Stored Objects), these links are displayed, ordered by owner. Click on the title bar to order the MS-Word links by name. In the title bar, you can see whether the MS-Word links are ordered by owner or by name; he sort column is not enclosed in brackets.

If other users allow you to share their MS-Word links, you can execute these links. The owner of an MS-Word link to be shared can specify whether the sharing user may only execute the MS-Word link or also read it. If you share an MS-Word link for execution only, then you cannot change it. If you share it for reading, then you can modify and save it as your own MS-Word link.


MS-Excel Link

The MS-Excel Link menu item in the Database menu or the  [image]  toolbar button opens a window showing all the MS-Excel links that are accessible to you (see Section Linking Microsoft Excel Tables with Data). In this window, you can create and save new MS-Excel links, modify, or delete existing ones.

Initially, the window is empty and you will only be able to create new MS-Excel links. To do so, select New and describe your MS-Excel link in the following window.

First enter the name of the Microsoft Excel object (for example, a Microsoft Excel table or a macro template) to which data is to be linked. Then specify the link position. The description of the position consists of the row and column description of the top left and bottom right corner of your destination area. If the destination area is smaller than the data of a query, only as much data as fits in the destination area is transferred. If only the top left corner is specified for the description of the position or if it has not been specified at all, the data of a query will be inserted either from the top left corner or from the active cell after a warning has been displayed and confirmed.

After describing the position for a link, you can link a saved SQL statement or query to your Microsoft Excel object. You can link several SQL statements or queries to an MS-Excel object, e.g., a table. The linked SQL statements or queries are shown in tabular format with their positions within the Microsoft Excel object.

You can also insert SQL statements and queries that other users share into your MS-Excel link.

If you want to view the result of an SQL statement or query, select the corresponding SQL statement or query in the table and Execute. If you want to remove an SQL statement or a query, select Delete. If you have created all the links desired for the Microsoft Excel object, you can save it with Save. QueryPlus uses the name of the link object for saving; in this case, the name of the Microsoft Excel table. This name can have up to 150 characters.

If there are already saved MS-Excel links or if you share MS-Excel links with other users (see Section New Sharing of Stored Objects), these links are displayed, ordered by owner. Click on the title bar to order the MS-Excel links by name. In the title bar, you can see whether the MS-Excel links are ordered by owner or by name; the sort column is not enclosed in brackets.

If other users allow you to share their MS-Excel links, you can execute these links. The owner of an MS-Excel link to be shared can determine whether the sharing user may only execute the MS-Excel link or also read it. If you share an MS-Excel link for execution only, then you cannot change it. If you share it for reading, then you can modify and save it as your own MS-Excel link.


Linking to Other Windows Applications

The functions for designing and saving MS-Word or MS-Excel links can also be used to create links with objects of other Windows applications. You must only ensure that the particular application can interpret the position description for the specified link. For Microsoft Word and Microsoft Excel, macros for data exchange are available. You can adapt the examples to your application or according to your requirements (see Sections Starting QueryPlus from Windows Applications, ADABAS Link to Other Windows Applications, and DDE Support of QueryPlus.).


Viewing and Modifying the Result of a Query

The result of a query (for example, from an SQL or Query dialog) is displayed in a separate window. If the result is very large, only the first rows are loaded (see Section Maximum Rows to Read). If not the whole result has been loaded; you can load the missing rows by scrolling forward. Click on the scrollbar button used for jumping to the end of the result. If this procedure takes too much time, you can interrupt it with the [image] key. The rows loaded so far are kept. You can repeat this procedure at any time.

The column names or their synonyms are displayed on the top of the table, the numbers of the rows are displayed on the left of the table. The column names and row numbers and the left upper edge are buttons. These can be used to select columns, rows, or the whole table. You can specify that the section selected is always the whole table (see Section Selecting the Whole Result Table).

If a section was selected, it can be copied onto the clipboard using Clipboard. The columns are then separated by tabs.

To save the selected section into a file, select Save. The data can be saved either as an ASCII file where the columns are separated by tabs, or as a Microsoft Excel file. If QueryPlus is used within a DDE dialog, you can transfer the selected section into your starting program, (e.g., Microsoft Excel or Microsoft Word for Windows: see Section Starting QueryPlus from Windows Applications.) by selecting Get Selection.


Sharing Objects

The SQL statements, queries, MS-Word, and MS-Excel links you created are only accessible to you. But you can share these objects with other users. The other users can then execute your objects or use them as a model. If they use your objects as a model, they can modify and save them as their property.

See also

New Sharing of Stored Objects

Stop Sharing


New Sharing of Stored Objects

To share your objects with other users, first connect to the desired database (see Section Connecting to the Database). Use the New Share menu item in the Sharing menu or the  [image]  toolbar button to open a window where you can make your objects accessible to other users.

Initially, all your SQL statements are provided for sharing. If you want to share another object, select the object type in the Object Type combo box.

Thereafter, either use the  [image]  button to select the objects marked for sharing in the left-hand list or use the  [image]  button to select all objects of the selected object type. If you want to cancel a selection, select the objects not to be shared in the right-hand list and use the  [image]  or  [image]  button.

If you want to share additional objects of another object type, select the desired object type and proceed as described above. After selecting all the objects to be shared, determine whether you want other users to be able to read your object. Finally, select the users to which the selected objects are to be made accessible in the Share with User combo box. If you want the selected objects to be accessible to all database users, select PUBLIC as user.

Use the  [image]  button to make the selected objects accessible to the user(s) selected.

You can do this repeatedly in succession. To terminate the object sharing, select the  [image]  button.


Stop Sharing

To terminate sharing, first connect to the desired database (see Section Connecting to the Database). The Stop Sharing menu item in the Sharing menu or the  [image]  toolbar button opens a window where you can view your shared objects.

The list of shared objects is ordered by users. If you want to change the sort order, click on the desired column in the title bar of the list. The column used to order the objects in the list is not enclosed in brackets in the title bar.

To terminate object sharing, select the objects and press Stop Sharing.


Edit Menu

When you design your SQL statement in one of the two SQL dialogs, you can either use the Edit menu or the five  [image] [image]  toolbar buttons. The Edit menu is used to copy text into the clipboard or to paste text from the clipboard in your SQL statement. The menu consists of the Undo, Cut, Copy, Paste, and Delete menu items.

There is also the possibility, when viewing results or defining a query in the Query description window, to see and edit the individual fields in a separate window. For this purpose, you can move the mouse pointer to the desired field and press the right mouse key, then select Zoom

The items are only available when they are usable. This means, e.g., pasting from the clipboard is only provided when something is in the clipboard. This menu item is not offered beforehand.

The Undo menu item can be used to cancel the last editing step in your SQL statement. Cut can be used to remove a previously marked section from the input screen and to copy it into the clipboard. Copy can be used to copy the marked section into the clipboard. Paste can be used to insert the content of the clipboard in your SQL statement. The object is pasted at the position where the insertion point is placed. A marked section is replaced by pasting. The Delete menu item removes the marked section without copying it into the clipboard.

You can use the following toolbar buttons instead of the individual menu items:

Undo:

Cut:

Copy:

Paste:

Zoom:


Starting QueryPlus from Windows Applications

QueryPlus supports Microsoft Windows programs that can run with DDE. For this purpose, QueryPlus provides a number of DDE commands and query options. You can use DDE to activate the individual QueryPlus dialogs, to set QueryPlus options, and to query the results.

The distributed document templates ADABAS6.DOT and ADABAS7.DOT provide an easy link between ADABAS and Microsoft Word for Windows in its versions 6.0 and 7.0. The macro templates ADABAS5.XLA and ADABAS7.XLA serve to link Microsoft Excel to ADABAS. On the basis of the distributed example files ADABAS6X.DOT and ADABAS5X.XLM, you can develop your own concrete links for all programs that can run with DDE.

For a DDE dialog with QueryPlus, a special DDE cancel button  [image]  was added in the toolbar. You can use this button to cancel any DDE dialog in any phase. In DDE dialog, the buttons for terminating a dialog, such as Save or Close, are labeled Save DDE or Close DDE. These buttons terminate both the current SQL or Query dialog and the DDE dialog.

In DDE dialog, each window containing a query result is provided with a button that allows you to transfer the result to the starting program. Thus results can be kept to be passed to a program in a later DDE dialog, if necessary.

See also

Linking Microsoft Word to ADABAS via QueryPlus

Linking Microsoft Excel to ADABAS via QueryPlus

ADABAS Link to Other Windows Applications

Inserting Data from ADABAS

Linking Microsoft Word Documents with Data

Microsoft Word Mail Merge

Linking Microsoft Excel Tables with Data


Linking Microsoft Word to ADABAS via QueryPlus

The menu or toolbar required and the macros are contained in the document template ADABAS6.DOT or ADABAS7.DOT, respectively. To use these link elements, the particular document templates must be installed into the corresponding Microsoft Word versions.

See also

Installing the Macro Templates for Microsoft Word


Installing the Macro Templates for Microsoft Word

The macro templates for Microsoft Word are in the directory <DBROOT>\QUERYPL\MACROS.

The document template ADABAS6.DOT or ADABAS7.DOT can be used directly in Microsoft Word. Copy the document template either to the Microsoft Word template directory or to the Microsoft Word startup directory. Document templates in the template directory can be used for new documents like normal document templates, while document templates in the startup directory are automatically attached to each document.

If you want the link elements to be made available for each document, copy the document template to the startup directory. If you want to use the link elements with particular document templates, copy the document template to the template directory and link the desired macros, menus, or the toolbar to the desired document template.

For more information about the management of macros, menus, and toolbars or about attaching document templates from the Microsoft Word startup directory, refer to the Microsoft Word documentation.

After the installation, the ADABAS menu, the macros needed for linking, and the following toolbar are available:

The ? menu and the  [image]  toolbar button can be used to start the online Help for QueryPlus.


Linking Microsoft Excel to ADABAS via QueryPlus

The menu or toolbar required and the macros are contained in the additional macro ADABAS5.XLA or ADABAS7.XLA. To be able to use these link elements, this macro must be installed in Microsoft Excel. After the installation, a menu, the macros needed for linking, and the following toolbar are available:

See also

Installing the Macro Template for Microsoft Excel


Installing the Macro Template for Microsoft Excel

To install the macro template for the link to ADABAS, use the Microsoft Excel Add-In Manager. Click on Tools/Add-Ins to start the Add-In Manager in Microsoft Excel. Use Browseto open the additional macro ADABAS5.XLA or ADABAS7.XLA in the directory <DBROOT>\QUERYPL\MACROS to activate the link elements. For more information about this subject, refer to the Microsoft Excel documentation.


ADABAS Link to Other Windows Applications

From any Windows application that allows, for example, an extension by macro programming and supporting DDE, you can establish a link to the database using QueryPlus. To do so, you must extend the application by programming the DDE link to the database.

You can use the example templates ADABAS6X.DOT (Microsoft Word 6.0) and ADABAS5X.XLM (Microsoft Excel 5.0) provided in the directory <DBROOT>\QUERYPL\MACROS\SAMPLES and adapt them to your application or according to your requirements (see Section DDE Support of QueryPlus.).


Inserting Data from ADABAS

After installing the Microsoft Word or Microsoft Excel macros, the following three toolbar buttons are provided for inserting data from ADABAS:

Microsoft Word or Microsoft Excel

The toolbar buttons have the following meanings, from left to right:

1.	Dialog for Direct SQL Statements

Direct SQL statements are used to formulate and subsequently execute SQL statements. You can execute SQL statements or queries directly and save the direct SQL statement for later use (see Section Direct SQL Dialog).

2.	SQL Dialog

The SQL Dialog is used to execute already formulated and saved SQL statements or to formulate and save new SQL statements. In addition, you can modify or delete existing SQL statements (see Section SQL Dialog).

3.	Query Dialog

The Query Dialog is used to execute already designed and saved queries, i.e., search requests, or to design and save new queries. In addition, you can modify or delete existing queries (see Section Query Dialog).


Linking Microsoft Word Documents with Data

In this context, linking means that the data desired for the document (i.e., the linked queries or SQL statements) is stored in the database, whereas the particular position is stored in the document. Once a link of ADABAS data has been added to your document, you can actualize the data contents in your document by clicking on a button.

The following three toolbar buttons are provided for linking ADABAS data with a document:

Microsoft Word

The toolbar buttons have the following meanings, from left to right:

1.	Adding Links

When adding links, QueryPlus is automatically informed about the name of the document as well as the position within the document at which the link is to be added. Therefore the document must be named, if this has not been done yet. If the document is already named, an automatic change is made to the "Design New Link Dialog" in QueryPlus (see Section MS-Word Link or MS-Excel Link). There you can link saved SQL statements or saved queries with your document.

When adding links, captions for the particular position of the link data are added to your document. These captions are called SQLEntryBeginX or SQLEntryEndX, respectively, where X will be replaced by the number of the link for this document. The numbering starts with 0. In addition to these captions, brackets are inserted into your document. These brackets indicate that a link is available but has not yet been actualized.

If the captions are moved while you are editing your document, the link data at the moved positions is actualized as well. If captions for the data link are deleted, the links are preserved in spite of the deletion, because information for a link was stored in the database. If captions for a link are no longer available, the data is inserted at the insertion point after confirmation.

2.	Actualizing Links

Actualizing links of a document initially serves to insert link data and then to actualize data existing in the document with data from ADABAS. If captions for a link are no longer available, the data is inserted at the insertion point after confirmation.

3.	Modifying Links

You can modify links to change the data attached to a link or to release individual links from the document, or simply to show the links that are available to a particular document.


Microsoft Word Mail Merge

For mail merge under Microsoft Word, you need a data source containing data to be merged with the main document. It would be especially useful if this data could be taken from the database. There are two ways of creating data sources with data from the database: the needed data can either be inserted into (see Section Inserting Data from ADABAS) or linked with the data source (see Section Linking Microsoft Word Documents with Data).

Linking data from the database with the data source has the advantage that you can easily actualize it at any time without having to keep the search request in the memory. In this way, it is also possible to define and "distribute" links that other users only need to actualize.

To create a data source, open a new document. You can either create links for data from the database (see Sections Linking Microsoft Word Documents with Data and MS-Word Link) or insert data directly into the document (see Section Inserting Data from ADABAS). Later on, this data source will provide the data for the main document. If you have created links, actualize the links in the data source (see Section Linking Microsoft Word Documents with Data). If the data meets your requirements, save the data source.

To create the actual mail merge document, select Tools Mail Merge under Microsoft Word (see Microsoft Word documentation). Then attach the previously created data source to the main document. The full Microsoft Word mail merge functionality is then available to you.

The ADABAS data linked to the data source can be actualized by a mouse click. Three mail merge buttons allow you to use the functionality of QueryPlus links without changing to the data source every time:

Microsoft Word

The buttons have the following meanings, from left to right:

1.	Add Mail Merge Link

2.	Actualize Mail Merge Link

3.	Modify Mail Merge Link


Linking Microsoft Excel Tables with Data

In this context, linking means that the data desired for the table (i.e., the linked queries or SQL statements) is stored in the database, whereas the particular position is stored in the table. Once a link of ADABAS data has been added to your table, you can actualize the data contents in your table by clicking on a button.

The following three toolbar buttons are provided for linking ADABAS data with a table:

The toolbar buttons have the following meanings, from left to right:

1.	Adding Links

When adding links, QueryPlus is automatically informed about the name of the table as well as the position within the table at which the link is to be added. Therefore the table must be named, if this has not been done yet. If the table is already named, an automatic change is made to the "Design New Link Dialog" in QueryPlus (see Sections MS-Word Link or MS-Excel Link). There you can link saved SQL statements or saved queries with your table.

2.	Actualizing Links

Actualizing links of a table initially serves to insert link data and then to actualize the data existing in the table with data from ADABAS.

3.	Modifying Links

You can modify links to change the data attached to a link or to release individual links from the document, or simply to show the links that are available to a particular document.


DDE Support of QueryPlus

If QueryPlus is to be started for a DDE conversation, QueryPlus must be started with the option /Remote. A DDE conversation can be established with an already started QueryPlus application which has not been started with the /Remote option. For a DDE conversation with QueryPlus, the data transmitted and received is always a character string:

Application name	:	QUERYPL

Topic	:	QueryPlus DDE Server

The following DDE query options are provided:

SysItems	:	produces a list of DDE queries and commands.

SQLStatus	:	produces the status of a DDE communication.

SQLRetCode	:	produces the return code of the last database request.

SQLResult	:	produces general results.

SQLCols	:	produces the number of columns of a result.

SQLRows	:	produces the number of rows of a result.

The following DDE query options can also be set by a program that can run with DDE:

SQLWithHeader	:	specifies whether the result contains column names ("1") or not ("0").

SQLSelectAll	:	specifies whether the result of a search request is to be selected automatically completely for transfer ("1")

or not ("0").

QueryPlus can be addressed by the following DDE commands:

SQLExec	:	executes an SQL statement directly.

SQLCommand	:	starts the SQL Dialog in QueryPlus.

SQLQuery	:	starts the Query Dialog in QueryPlus.

SQLModifyLink	:	creates or modifies links.

SQLActualizeLink	:	actualizes links.

SQLFetch	:	transfers results.

SQLFetchLink	:	transfers results of single links.

See also

The Individual DDE Commands and Their Usage


The Individual DDE Commands and Their Usage

The individual DDE commands are described in the following with their syntax and function.

General Syntax Rules

1.	The functions SQLExec, SQLCommand, SQLQuery, SQLModifyLink, and SQLActualizeLink can be called without the parameters <DSN>, <User>, and <Password>. If QueryPlus is started and connected to a database, the particular function will be executed on this database. If QueryPlus is neither started nor connected to a database, either the first direct connect is performed, if possible (see chapter Connecting to the Database), or the connect window appears where you must enter all the specifications that are required for a connect to the database before the function is executed.

If the parameters <DSN>, <User>, and <Password> have been specified, they are used for a new connect, regardless of whether QueryPlus is already started or connected to a database. After this connect, the old database connection will no longer be available.

2.	If the parameter for the name of the corresponding object is specified for the functions SQLCommand, SQLQuery, SQLModifyLink, SQLActualizeLink, then the owner of the object must be specified as well. Otherwise, QueryPlus assumes that the object is your property. To specify the owner, enter the owner as object namefollowed by a tabulator character (ASCII 9) and the actual object name. Owner name and object name are case-sensitive.

General Rules

1.	Parameters are always passed as character strings.

2.	If more parameters are to be passed, they must be separated from each other by a comma.

3.	Except for the actual parameters, blanks must not occur in the parameter list, i.e., between the opening and closing parentheses.

4.	The commands SQLExec, SQLCommand, SQLQuery, and SQLActualizeLink only provide data. To transfer this data from QueryPlus to your application, use the commands SQLFetch or SQLFetchLink.

See also

SQLExec

SQLCommand

SQLQuery

SQLModifyLink

SQLActualizeLink

SQLFetch

SQLFetchLink

Troubleshooting

Examples with DDE Commands


SQLExec

Function

This function executes an SQL statement or changes to the direct SQL Dialog in QueryPlus (see Section Direct SQL Dialog).

Format

 	SQLExec

 |	SQLExec (<DSN>,<User>,<Password>)

 |	SQLExec (<SQL Statement>)

 |	SQLExec (<SQL Statement>,<DSN>,<User>,<Password>)

Syntax Rules

1.	This function can be called without the parameter <SQL Statement>. In this case, QueryPlus and a window for entering an SQL statement is opened.

General Rules

None


SQLCommand

Function

This function executes a saved SQL statement or changes to the SQL Dialog in QueryPlus (see Section SQL Dialog).

Format

SQLCommand

 |	SQLCommand (<DSN>,<User>,<Password>)

 |	SQLCommand (<SQL Name>)

 |	SQLCommand (<SQL Name>,<DSN>,<User>,<Password>)

Syntax Rules

1.	This function can be called without the parameter <SQL Name> . In this case, QueryPlus and a window containing a list of saved SQL statements is opened.

General Rules

None


SQLQuery

Function

This function executes a saved query or changes to the Query Dialog in QueryPlus (see Section Query Dialog).

Format

SQLQuery

 |	SQLQuery (<DSN>,<User>,<Password>)

 |	SQLQuery (<Query Name>)

 |	SQLQuery (<Query Name>,<DSN>,<User>,<Password>)

Syntax Rules

1.	This function can be called without the parameter <Query Name>. In this case, QueryPlus and a window containing a list of saved queries is opened.

General Rules

None


SQLModifyLink

Function

This function creates a new link or modifies an existing one (see Sections MS-Word Link, MS-Excel Link, ADABAS Link to Other Windows Applications).

Format

SQLModifyLink (<Object Type>,

               <Object Name>,

               <Destination Area>)

 |	SQLModifyLink (<Object Type>,

               <Object Name>,

               <Destination Area>,

               <DSN>,<User>,<Password>)

Syntax Rules

1.	The first parameter describes the type of the object to which data will be linked.  This parameter allows you to distinguish between Microsoft Word and Microsoft Excel objects. If you specify an empty string for this parameter, QueryPlus assumes a Microsoft Word object. Only the combination of <Object Type> and <Object Name> ensures the uniqueness of the link.

2.	The second parameter indicates the name of the object. If this parameter is specified as an empty string, QueryPlus asks you for the name of the object when you describe the link in greater detail.

3.	The third parameter describes the destination area in the object to be linked into which the link data will be inserted when actualizing.

General Rules

1.	The first three parameters must be specified, but they can be empty strings.


SQLActualizeLink

Function

This function actualizes link data. It provides data for the first of various links, if any.

Format

SQLActualizeLink (<Object Type>,

                  <Object Name>)

 |	SQLActualizeLink (<Object Type>,

                  <Object Name>,

                  <DSN>,<User>,<Password>)

Syntax Rules

1.	The first parameter describes the type of the object. This parameter allows you to distinguish between Microsoft Word and Microsoft Excel objects. If you specify an empty string for this parameter, QueryPlus assumes a Microsoft Word object. Only the combination of <Object Type> and <Object Name> ensures the uniqueness of the link.

2.	The second parameter indicates the name of the object.

General Rules

1.	The first two parameters must always be specified.


SQLFetch

Function

This function copies the selected data into the clipboard from where you can copy it into your application using simple commands.

Format

SQLFetch

Syntax Rules

None

General Rules

1.	Ensure that SQLFetch always refers to the last query and data transfer function (see Sections SQLExec, SQLCommand, and SQLQuery).


SQLFetchLink

Function

This function transfers data belonging to a link object. First it copies the selected data into the clipboard from where you can copy it into your application using simple commands. This function must be repeated for any existing link of an object. It produces the data sequentially from the first link to the last.

The description of a destination area belonging to a link is produced in the DDE query object SQLResult. Before this function can be called, the data must have been provided via SQLActualizeLink.

Format

SQLFetchLink

Syntax Rules

None

General Rules

1.	Ensure that SQLFetchLink always refers to the last SQLActualizeLink.


Troubleshooting

For troubleshooting, the DDE query objects SQLStatus and SQLRetCode are available. They will be reset after every DDE communication and should be checked after every DDE communication. SQLStatus produces general warnings and errors, whereas SQLRetCode returns the error code of a failed database communication. If no error occurs during a DDE conversation, both DDE query objects return an empty string. If an error occurs, the corresponding error code is contained as a string in the particular DDE query object.


Examples with DDE Commands

1.	The following example showing how to request DDE query objects and DDE commands is taken from the sample document template ADABAS6X.DOT:

 'initiating DDE communication

 ch = DDEInitiate("querypl", "QueryPlus DDE Server")

 If ch <> 0 Then

 	'requesting the DDE query object "SysItems"

 	result$ = DDERequest$(channel, "SysItems")

 	'outputting the result

 	MsgBox result$, "QueryPlus DDE Server"

 EndIf

2.	The following example of the DDE command SQLExec for a direct SQL query is also taken from the sample document template ADABAS6X.DOT:

 'initiating DDE communication

 ch = DDEInitiate("querypl", "QueryPlus DDE Server")

 If DDERequest$(ch, "SqldbStatus") <> "" Then

 	Goto MyEnd

 EndIf

 'executing a DDE command

 DDEExecute(channel, "SQLExec('Select * from Products')")

 If DDERequest$(ch, "SQLStatus") <> "" Then

 	Goto MyEnd

 EndIf

 'transferring data

 DDEExecute(channel, "SQLFetch")

 If DDERequest$(ch, "SQLStatus") <> ""  Then

Goto MyEnd

 EndIf

 'focusing the own application

 Activate WindowName$()

 'copying data from the clipboard into the application

 EditPaste

3.	The following example in Microsoft Word Basic shows how to transfer link data:

 '-23003 is the error code for more existing links,

 'copying the application

 MyError = -23003

 While MyError = -23003

'transferring the data of the link

DDEExecute(channel, "SQLFetchLink")

MyError$ = DDERequest$(ch, "SQLStatus")

If MyError$ <> "" AND MyError$ <> "-23003" Then

Goto MyEnd

EndIf

'determining the destination area

ActPos$ = DDERequest$(channel, "SQLResult")

MyError$ = DDERequest$(ch, "SQLStatus")

If MyError$ <> "" AND MyError$ <> "-23003" Then

Goto MyEnd

EndIf

...

'selecting the destination area

...

'focusing the own application

EditPaste

 Wend

4.	The following example describes how to set QueryPlus options from within Microsoft Excel. It was taken from the sample macro template ADABAS5X.XLM:

 DDEChannel=INITIATE("querypl", "QueryPlus DDE Server")

 1

 =POKE(DDEChannel,"SQLWithHeader",R[-1]C)

 =POKE(DDEChannel,"SQLSelectAll",R[-2]C)

 =TERMINATE(DDEChannel)

 =RETURN()


QueryPlus Options

To facilitate some operations, QueryPlus provides options that can be set via Options while QueryPlus is running. These options will be saved for the next session at once with Save or automatically when QueryPlus is left.

The options are distributed to two groups: on the one hand, there are the common options for QueryPlus to be set using the Options/QueryPlus menu; on the other hand, there are the options for a direct connect to the database using the Options/Direct Connections menu.

The common options are distributed to six so-called "cardfile cards". These cardfile cards comprise the options Common, Select, Scrolling, Show Tables, Confirmations and Auto Start. You can reach the individual cardfile cards by directly selecting the corresponding card.

The options for a direct connect can be used to remove entries for direct connections from the Database menu. To do so, select the individual entries in the list and press Delete. If you want to change the order of the entries, select the entry to be moved, hold down the mouse button, and drag the entry to the target position.

See also

Comprehensive Information

Save Password for a Direct Connect

Data Transfer with Headers

Selecting the Whole Result Table

Show Fetched Rows

Maximum Rows to Read

Show Tables

Confirmations

Auto Start

International Options


Comprehensive Information

If you select the option Comprehensive Information, you receive more detailed information about the corresponding operation when you click on the toolbar buttons or query status information. This option is contained in the cardfile card Common.


Save Password for a Direct Connect

You can specify whether QueryPlus either saves the password entered when connecting to a database (see sction Connecting to the Database) or not. When you connect for the first time, the password is automatically saved. If you do not want the password to be saved, remove the check mark from Save Password for a Direct Connect. If you want your password to be saved at a later time as well, select Save Password for a Direct Connect.

These options are contained in the cardfile card Common.


Data Transfer with Headers

To ensure that the column names are also passed when transferring data from QueryPlus to your application, enable the option Get With Header. This option is contained in the cardfile card Select.


Selecting the Whole Result Table

Only data selected in the result table is passed to your application. With Select All, you can specify that the whole result table be selected from the very beginning. When you have done so and you want to pass the complete result table to your application, you only need to select Get Selection. This option is contained in the cardfile card Select.


Show Fetched Rows

Not all the rows of a query result are loaded from the database for a large result (see Section Maximum Rows to Read, Viewing and Modifying the Result of a Query). It can therefore happen that rows are subsequently loaded while scrolling forwards. To display the row currently being loaded, enable the option Show Fetched Rows. This option is contained in the cardfile card Select.


Maximum Rows to Read

In the case of large query results, it is not necessary to immediately load all rows of the result. Depending on your computer or network, you can determine the number of rows to be loaded for the first page and for all subsequent pages. The values predefined by QueryPlus should be an optimal setting for most computers and networks. You can reset these values at any time using Defaults. These options are contained in the cardfile card Scrolling.


Show Tables

In Query Dialog (see Section Query Dialog, Selecting Tables), you select the tables required for the formulation of your query by using Tables. The settings on the cardfile card Show Tables determine which tables are displayed. On the cardfile card Show Tables, you can specify whether you want to see tables, views, synonyms, result tables, snapshots, and your SYS tables, if any.


Confirmations

The deletion of a QueryPlus object, whether it is an SQL statement, a query, a link, a share, or a table or column for a query design, is irrevocable. For security reasons, the user is always asked to confirm the deletion of such an object. The deletion of joins from a query design and linked SQL statements or queries from Word or Excel links is not so critical, because it can be easily corrected. In these cases, no default request is made. On the cardfile card Confirmations, you can specify whether you want to confirm a deletion or not. Here you can also define that QueryPlus requests a confirmation if modified queries and SQL statements are to be saved.


Auto Start

The Auto Start options can be used to determine the QueryPlus windows to be opened automatically when connecting to the database. You can specify, in addition, whether the open windows are to be opened with the next connect.


International Options

The information required in QueryPlus for language, date and time specifications is taken from the MS-Windows options. For example, if the English language is set in MS-Windows, QueryPlus also starts in English. If a specified language is unknown to QueryPlus, it automatically starts in English.


ADABAS D AccessPlus - ADABAS D AccessPlus
ADABAS D AccessPlus

ADABAS D AccessPlus is an add-in library for Microsoft Access 2.0 that supports the development of client/server applications with Microsoft Access and ADABAS D. AccessPlus contains two wizards, the ADABAS D Attaching Wizard and the ADABAS D Upsizing Wizard, which make it considerably easier to attach ADABAS tables to Access databases and to upsize (port) existing Access tables to ADABAS.

For step-by-step explanations of how to work with the Attaching Wizard and the Upsizing Wizard, see Sections The Attaching Wizard and The Upsizing Wizard. The following sections describe how to install and start AccessPlus and how to log in to a database. This procedure is identical for both wizards.


Requirements for Working with AccessPlus

AccessPlus places the following requirements on the server and client:

Server

ADABAS Database Version 6.1

Client

ADABAS OfficePlus

ODBC data source

Microsoft Access Version 2.0

To find out how to install ADABAS OfficePlus and create an ODBC data source, see the manual entitled "Installation under Windows".


Installing AccessPlus

Before you can work with AccessPlus, you must install it in Microsoft Access as an add-in. This can be done using the Microsoft Access Add-in Manager.

Installation Using the Add-in Manager

1.	To start the Microsoft Access Add-in Manager, choose "Add-ins" from the File menu, followed by "Add-in Manager". The Add-in Manager displays a list of all the add-ins (libraries) in your Microsoft Access directory.

2.	To install AccessPlus as an add-in, click the Add New button. Microsoft Access displays the "Add New Library" dialog box. Change to the ADABAS installation directory, and select the "ACCESSPL.MDA" file. Click the Ok button.

Microsoft Access then copies the add-in library to your Microsoft Access directory and inserts a new item in the list of available add-ins.

3.	Once AccessPlus is installed, click the Close button.

A message appears, telling you that you must restart Microsoft Access before the AccessPlus option will appear in the add-in menu.

Also see: "Using the Add-in Manager to install add-in" in Chapter 15 of the Microsoft Access manual entitled "Building Applications".

If the Add-in Manager is not made available to you, you must make the necessary changes to the MSACC20.INI file manually.

Installation Without the Add-in Manager

1.	Copy the ACCESSPL.MDA file from the ADABAS installation directory to your Microsoft Access 2.0 directory.

2.	Load the MSACC20.INI file in an editor. This file is located in the Windows directory.

3.	Enter the following line in the [Menu Add-Ins] section:

ADABAS D AccessPlus==ADABAS_D_AccessPlus()

4.	Enter the following line in the [Libraries] section:

accesspl.mda=rw

5.	Save the MSACC20.INI file, and exit the editor.

The next time you call Microsoft Access, "ADABAS D Access Plus" should appear in the "Add-ins" menu.


Starting AccessPlus

To start AccessPlus, proceed as follows:

1.	Open the Microsoft Access database to which you wish to attach the ADABAS tables or from which you wish to port Microsoft Access tables to ADABAS.

If you wish to use the Upsizing Wizard, you should activate the Exclusive option when you open the database to ensure that your database cannot be changed by other users during the porting process.

2.	Start AccessPlus by choosing "ADABAS D AccessPlus" from the "Add-ins" menu.

You can call either the Attaching Wizard or the Upsizing Wizard by clicking the corresponding button.

You can move from one dialog box to another by means of the Next> and <Back buttons. Once you have made a selection in a dialog box, click the Next> button to branch to the next dialog box. The <Back button takes you back to the previous dialog box.

The Cancel button cancels all processing and terminates ADABAS D AccessPlus.

The Help button calls help information for the relevant dialog box.

Once you have completed all the dialog boxes and have reached the last box, the Attach or Upsize button becomes available, which you can click to start the process.


Logging in to the Database

When you start one of the ADABAS D wizards, a list of the available ADABAS data sources appears.

If there is no data source for the desired ADABAS database, use the ODBC Administrator to create one. Then restart AccessPlus.

Select the appropriate data source, and then click the Next button. The ADABAS login dialog box appears, in which you must enter the login ID and password of the ADABAS D database user. Once you have confirmed your entries by clicking the Ok button, a connection is set up to the database.

Once the connection to the database has been set up successfully, the next dialog box appears, in which you can set various options for the Attaching Wizard or Upsizing Wizard.


The Attaching Wizard

The Attaching Wizard provides you with a simple and convenient means of selecting tables from an ADABAS D database and attaching them to a Microsoft Access database. You can reduce the length of the list of ADABAS tables by specifying a search pattern, and you can view the definition of an individual table before attaching it.

See also

Setting the Options

Selecting the Tables to be Attached

Attaching the Tables


ADABAS D AccessPlus - The Attaching Wizard - Setting the Options
Setting the Options

Select Tables

In the "Select Tables" box, you can specify a search pattern for "Owner" and "Table Name" to limit the selection of ADABAS tables that appear in the list. The default for the "Owner" box is the login ID of the current database user, and the default for "Table Name" is *. This means that all tables belonging to the user are displayed.

When you click the Next button to change to the next dialog box, a list of ADABAS D tables that match the specification is displayed.

Save login ID and password locally

Select "Save login ID and password locally" if you do not wish to be asked for the user parameters when you first open the table in a new Microsoft Access session. In this way, Microsoft Access saves the login information for the attached table and automatically uses it to set up the connection to the ADABAS D database.


Selecting the Tables to be Attached

On the left-hand side of the dialog box you will find the list of ADABAS tables to which you currently have access. Enter all the ADABAS tables that you wish to attach to your Microsoft Access application on the right-hand side. You must select at least one ADABAS table in order to start the process.

Use the arrow buttons to move individual tables or all tables from the list on the left to the list on the right or vice versa.

The Info button lets you view the definition of a selected ADABAS table. This is useful if you wish to know what fields are contained in a table before linking it to your Microsoft Access application.


Attaching the Tables

Once you have selected all the ABABAS tables that you wish to attach to your Microsoft Access application, click the Attach button to start attaching them.

The selected ADABAS tables are then attached to the Microsoft Access database one after the other. A status window indicates which ADABAS table is currently being attached.

Once the tables have been attached, the number of tables successfully linked to Microsoft Access is displayed.

Click the Close button to exit AccessPlus.


The Upsizing Wizard

The Upsizing Wizard allows you to port Microsoft Access tables to ABABAS. Various attributes of the Microsoft Access tables can also be transferred to ADABAS.

You can also modify your Microsoft Access database in such a way that your queries, forms and reports are based on the exported ADABAS tables instead of the local Microsoft Access tables.

See also

Preparations

Setting the Options

Selecting the Tables

Porting the Tables

The Upsizing Report

Subsequent Steps


Preparations

Before you can port a Microsoft Access database, you need an ADABAS database user and an ODBC data source. You should make a backup copy of the Microsoft Access database.

Creating a User

To port tables from a Microsoft Access database, the corresponding ADABAS database must contain a user to whom the tables exported from Microsoft Access can be assigned. In other words, this user will be the owner of the ported tables.

The user must have the access rights of a DBA and must also be authorized to open several database sessions simultaneously (NOT EXCLUSIVE option). The maximum disk space available to this user, which is defined by means of the PERMLIMIT value, must be enough to accommodate the data.

An appropriate name for this user would, for example, be the name of the Microsoft Access database.

If there is as yet no such database user, you can create one using ADABAS Domain. However, you can also issue a CREATE USER statement from Microsoft Access by means of an "SQL Pass-Through Query".

Example:

CREATE USER nwind PASSWORD secret DBA NOT EXCLUSIVE

For more information, see "Authorization, <create user statement>" in the ADABAS Reference online help.

Creating an ODBC Data Source

Make sure that you have created an ODBC data source for the ADABAS database to which you wish to port Microsoft Access tables.

To create an ODBC data source, use the ODBC Administrator. You will find more information on creating a data source for an ADABAS database in the ADABAS manual entitled "Installation under Windows".

Selecting a Microsoft Access Database

Only Microsoft Access tables can be ported using the Upsizing Wizard. Some Microsoft Access applications use two databases; the tables are contained in one database (the back end) and queries, forms, reports, macros and modules in the other (the front end). The tables from the back end are attached to the front end. Since the Upsizing Wizard ignores attached tables, you must use the back-end database.

Backing up the Microsoft Access database

Before porting your Microsoft Access database, you should make a backup copy. The Upsizing Wizard does not delete any data or objects from your Microsoft Access database, but tables are renamed if necessary.


ADABAS D AccessPlus - The Upsizing Wizard - Setting the Options
Setting the Options

Export Options

If you wish to export only the table structure without the data, select "Structure only, no data".

To export table attributes, select one or more of the following options:

- Primary Keys	- Default Values

- Indexes	- Validation Rules

- Relationships	- Required Options

After the Microsoft Access tables have been exported, an attempt is made to reproduce these attributes in the newly created ADABAS tables.

If you wish to export the table relationships ("Relationships" option), you must export all the tables involved in a relationship.

Some of the Microsoft Access attributes have different names in ADABAS. The following list indicates the names of the Microsoft Access attributes in ADABAS:

Microsoft Access	ADABAS

Primary Key	Primary Key

Index 	Index

Relationship	Referential Constraint or Foreign Key

Default Value 	Default Value

Validation Rule	Constraint

Required Option	NOT NULL Attribute

The ADABAS attributes are created using the ADABAS data definition language (DDL). The SQL statements used to create the corresponding attributes are listed below.

Primary Key

ALTER TABLE <table name>

ADD PRIMARY KEY (<column name>, ...)

Index

CREATE [UNIQUE] INDEX <index name>

ON <table name> (<column name>, ... )

Relation

ALTER TABLE <table name>

FOREIGN KEY (<referencing column>, ...)

REFERENCE <referenced table>

[ON DELETE CASCADE]

Default Value

ALTER TABLE <table name>

ADD DEFAULT <default value>

Validation Rule

ALTER TABLE <table name>

ADD CONSTRAINT <constraint name>

CHECK <search condition>

Required Option

ALTER TABLE <table name>

COLUMN <column name>

NOT NULL

Also see "Data Definition" in the ADABAS Reference online help.

If an SQL statement cannot be executed during the porting process because, for example, it could not be created correctly, it is displayed in a dialog box.

You can then either correct the SQL statement and execute it again (using the Execute button), or you can skip it (using the Skip button).

Attach Options

If you do not wish to attach the exported tables to your Microsoft Access database, clear the "Attach exported tables" check box.

Select "Save login ID and password locally" if you wish to save the login ID and password for the attached tables.


Selecting the Tables

The selection of the Microsoft Access tables that you wish to export to ADABAS has a decisive influence on the performance of your application.

A good Microsoft Access client/server application consists of a mixture of local tables and tables in the server database. As a general rule, tables that are seldom modified should be kept in the local database and tables that are often modified or are accessed by a large number of users should be exported.

Note: Tables previously ported and renamed by the Upsizing Wizard do not appear in the list of available tables. You can alter this by giving the tables the suffix "_local" before starting the Upsizing Wizard.

On the left-hand side of the dialog box you will find the list of Microsoft Access tables. Enter all the Microsoft Access tables that you wish to export to ADABAS on the right-hand side. You must select at least one Microsoft Access table to start the porting process.

Use the arrow buttons to move individual tables or all tables from the list on the left to the list on the right or vice versa.

The Info button lets you view the definition of a selected Microsoft Access table. This is useful if you wish to know what fields are contained in a table before you export it to ADABAS D.


Porting the Tables

Once you have selected the Microsoft Access tables that you wish to port, click the Upsize button to start the porting process.

The following steps are then executed:

1.	The selected Microsoft Access tables are exported to ADABAS one after the other. If a table already exists in ADABAS, you are asked whether or not you wish to overwrite this table.

2.	The selected attributes of the Microsoft Access tables are created in ADABAS by means of DDL.

If an error occurs, you can correct the corresponding SQL statement (see the section entitled "Setting the Options").

3.	If the "Attach exported tables" option was not selected, the upsizing process terminates here. Otherwise, the tables ported to ADABAS are attached to the Microsoft Access database.

All the exported Microsoft Access tables are first renamed. The new name of a table is obtained by adding the suffix "_local" to the previous name. The names of exported tables or individual field names may have to be modified when they are created in ADABAS because, for example, they contain invalid special characters. In this case, the name of the attached table receives the suffix "_remote" and an "aliasing query" is automatically generated with the original table name and field name.

The ADABAS tables are attached to the Microsoft Access database one after the other. A status window indicates which ADABAS table is currently being attached.

On completion of the upsizing process, the number of exported ADABAS tables and the number of exported tables that were attached to the Microsoft Access database are displayed.

The result of the porting process is given in an upsizing report. Click the Report button to view or print out the upsizing report, or the Close button to exit ADABAS AccessPlus.


The Upsizing Report

The upsizing report contains information on the porting process. It indicates the ADABAS database in which the tables were accepted, the options that were set and the Microsoft Access tables that were exported.

For each table, it indicates the table's new name (if any), whether an "aliasing query" was generated, and which attributes were exported.

In addition, the converted column names and data types and the attributes that could be created for individual columns are also displayed for each table.


Subsequent Steps

By porting a Microsoft Access application using the Upsizing Wizard, you have taken an important step toward creating a Microsoft Access client/server application with ADABAS.

However, in order to create a balanced client/server application, additional steps may be necessary. See the information and tips on designing client/server applications and on Microsoft Access JET Database Engine operation in the Microsoft Access manuals and other publications on this subject.

To ensure that the porting process is successful, you should always:

-	Ensure that Microsoft Access permits modification of the ported tables.

Defining Unique Indexes

A table attached to Microsoft Access cannot be modified unless it has a unique index. The Upsizing Wizard will port an existing unique index but does not create one if a unique index has not been defined in the exported table.

You can defined unique indexes using, for example, ADABAS Domain. However, you can also issue a CREATE UNIQUE INDEX statement from Microsoft Access by means of an "SQL Pass-Through Query".

For more information, see "Data Definition, <create index statement>" in the ADABAS Reference online help.

-	Set the appropriate privileges for the exported tables so that they can be accessed by other users.

Setting Privileges

The Upsizing Wizard does not export users, groups and privileges defined in your Microsoft Access application. After porting, the ADABAS user that you specified when logging in to the database is the owner of the exported tables and thus possesses all access rights.

Under this user, you can pass on privileges for specific tables to other users.

For more information, see "Authorization, <grant statement>" in the ADABAS-reference online help.

-	Check whether your Microsoft Access application is running correctly.

Checking the Application

Since tables attached by means of ODBC behave differently in Microsoft Access than local Microsoft Access tables, it is possible that your Microsoft Access application will not run correctly. If this happens, adapt your application to meet the new requirements of the attached tables.

See the information and tips on designing client/server applications and on Microsoft Access JET Database Engine in the Microsoft Access manuals and other publications on this subject.


General Information
The ADABAS ODBC driver allows you to access ADABAS using the standardized Microsoft ODBC (Open DataBase Connectivity) database interface.

The end user is thus offered easy access to the ADABAS data from Windows applications supporting ODBC (e.g., Microsoft Access).

Using development tools such as Visual Basic or Powerbuilder, programmers can create Windows applications using the ODBC functions for database access.

General Information

Special Features

Information about known restrictions and special features of Windows applications or development tools that have been tested with the ADABAS ODBC driver is outlined in the current version information (<DBROOT>\README.ODB). This information will help you to achieve an optimum link of the respective ODBC application to ADABAS.