In Client/Server programming, performance is usually a key issue. Microsoft Access has many ways to communicate with a database server including import, export, and linked table features. (For an overview of using these Microsoft Access features in an AS/400 environment, see my article “Links, Imports, Export: Using ODBC to Share OS/400 Data with Microsoft Access,” AS/400 Network Expert, September/October 1999.) However, these features usually deal with entire tables or views and do not provide any resource for, say, running a program or creating an index on the server. A solution to this problem would be for Access to provide a mechanism whereby an SQL statement could be sent directly to the database server without any preprocessing or intervention by the client.
Fortunately, Access offers several methods for passing SQL statements directly to a database server for processing. These methods include pass-through queries and Visual Basic for Applications (VBA) coding. The coding techniques include ODBCDirect, ADO and the ODBC API. The ODBC API can be used in any version of Access and it provides the best performance, but it is the most difficult method to code. ADO can be used with Access 95 and above. ODBCDirect is available in Access 97 and 2000. I’ve reviewed most of these techniques in recent issues of AS/400 Network Expert so, for the purpose of this article, I’ll demonstrate how to use the pass-through query function (available starting with Microsoft Access 2.0) to send SQL statements directly to your AS/400.
Before You Begin
Just a note before getting started: All of the examples here make use of the default *SQL naming convention (library.object) where the library name and object are separated by a dot
(.) rather than by a slash (/). If you configure your ODBC data source to use the *SYS naming convention (library/object), you must remember to change all of the examples accordingly. I’m assuming that you are familiar with the basics of creating an ODBC data source using Client Access for Windows 95/NT or AS/400 Client Access Express for Windows. I am also assuming that you possess a general knowledge of SQL, Microsoft Access, and are familiar with the concept of linked tables. VBA (Visual Basic for Applications) skills are required for the code example.
What Is a Pass-through Query?
A pass-through query is used to pass an SQL request directly to a server. In contrast to normal Access queries, the pass-through query is not syntax-checked by Access. The SQL
statement in the pass-through query must conform to the server’s SQL syntax. None of Access’s unique SQL structures are allowed. In this case, the SQL statements must conform to the rules of SQL/400.
To create a pass-through query in Access, you need to execute the following steps:
1. Create or open a database
2. Click on the Queries tab in the database window
3. Click the New button (The following steps vary depending on which version of Access you are using and the number of wizards installed.)
4. If prompted for a query type, choose Design View
5. If prompted with a Show Tables window, click close
6. On the Select Query window that appears, choose the Query option from the Microsoft Access menu bar and then select the SQL-Specific and Pass-Through options from the Query dropdown menu. This will bring you to the SQL Pass-Through Query screen shown in Figure 1.
On the SQL Pass-Through Query screen, note that only the SQL design mode is available—the Access Query-by-example (QBE) grid is not allowed. Enter an SQL statement in the query box. For example, enter SELECT * FROM QSYS2.
SYSINDEXES to return a list of SQL index files on the AS/400. Run the query by choosing Query/Run from the Access menu bar. When you run the query, you may be prompted to select an ODBC data source and to enter a user name and password.
Also, the ODBC connection information can be entered manually by setting the query’s ODBC connection string property under the View/Properties menu bar item from the SQL Pass-Through Query panel. In the Query Properties panel that appears (Figure 2), place your cursor in the ODBC Connect Str box. Doing so will cause a little button with three dots to appear at the end of the box. Clicking on this button will invoke the
“connection string builder” utility. This nice feature will allow you to select an ODBC data source, user name, and password and save that information right in the query. It evens formats the ODBC connection string for you. (Editor’s note: You may need to install certain features of Microsoft Access before you can use this feature. In testing this article on Office 2000, Access automatically prompted me for the Office 2000 installation CDROM from which it automatically installed additional components.)
There are a few things to remember when you are configuring a pass-through query’s ODBC connection string:
• If you choose to save a user name and password, there is a possible security breach for users who know how to view this information inside of Access. By default, object security is implemented when developing ODBC applications. For instance, if a knowledgeable user has update rights for a file, he may issue an unauthorized UPDATE SQL against the file.
• You may leave the connection string blank so that Access will prompt you to specify the data source every time the query is run.
• You may choose to omit the user name and password from the connection string but still specify a data source name. If an AS/400 connection has already been established, the ODBC driver will “borrow” the security information for the current connected user. (This may vary depending on how Client Access has been configured.)
• Other Client Access-specific options used to override the ODBC data source may be manually entered here. See the ODBC topics (specifically the connection string) in your Client Access help text for more information.
• When running a non-SELECT SQL statement (such as CREATE INDEX and DROP TABLE), the RETURNS RECORDS property (under the View/Properties menu bar item) should be changed to NO. Otherwise, when the SQL statement is completed, if no records are returned, Access will display an annoying message stating that the server didn’t return any records.
Once the SQL statement is passed to the server, the server will validate the request, create an execution plan, run the statement, and pass the results (if any) back to Access.
Since the SQL statement is directly given to the server, the server performs all the work associated with joining tables, selecting rows, updating tables, etc. This saves time, reduces the load on the PC, and allows the server to do what it was optimized to
do—process data. However, in certain instances, creating a standard Access query on linked tables may be faster than a pass-through query, because pass-through queries have some overhead of their own. It is usually beneficial to test a query using both methods to see which is faster. Some determining factors include the complexity of the SQL statement, the number of rows being processed, and the number of columns being returned.
Uses for Pass-through Queries
In Access, pass-through queries may be used as the recordsource for a form, query, or report—just like a normal table or query. They may also be used for running SQL statements. When I’m working on an AS/400 that doesn’t have the SQL Development Kit installed (which includes the interactive STRSQL command), I usually use a pass-through query to accomplish any SQL tasks.
So what can you do with a pass-through query that you can’t do with a linked table? Here’s an example. You have a software package on your AS/400 that has a lame data entry module, so you create a new data entry front-end with Microsoft Access. Next, say that when the data entry is done for the day, you use the export function to move the data from Access to the AS/400 as a file called QGPL.DATAENTRY. Once the data is on the AS/400, you want to call a CL program to validate and load the data into your AS/400 software.
You can automate this process completely with pass-through queries. First, you would need to delete the data entry table on the AS/400 (assuming it was already there from a previous run) to ensure your Access application doesn’t receive a “table already exists” error. To do this, create a pass-through query and enter DROP TABLE QGPL.DATAENTRY as the SQL text.
Next, you can use Access’s export function to transfer the data entry table to QGPL.DATAENTRY on the AS/400. (See the Related Materials section at the end of this article for an article that explains how to do this.)
If required, you can create multiple indexes on the AS/400 table by creating pass- through queries that issue the CREATE INDEX statement. Remember, if you create indexes on the data entry table, you will have to issue a DROP INDEX command for each of the indexes before issuing the DROP TABLE statement.
Finally, to run the CL program, use SQL in a pass-through query to call a stored procedure or to make use of the QMCDEXC API to execute a CALL command. Here’s what a sample call to QCMDEXEC might look like:
CALL QSYS.QCMDEXC (‘CALL QGPL.PROC_DATA’,0000000019.00000).
This example assumes that QCMDEXC has not been defined as a stored procedure. Therefore, it is necessary to specify the second parm of the QCMDEXC API with all of the digits (in this case, 15 digits with 5 decimal places). Without a stored procedure definition, SQL will examine the parm constant to determine the precision of the parm being passed. If the second parm was simply defined as 19, then the SQL interpreter would not have determined the second parm size as 15,5 and therefore would have generated a decimal-data error. See the AS/400’s SQL Reference guide, my article "Not Able to Adopt ADO? Why Not Use ODBCDirect?" on ANE Web at www.midrangecomputing.com/ane, and the Related Materials section at the end of this article for more information on using QCMDEXC as a stored procedure.
Coding Techniques
Once a pass-through query has been created, it can be executed like any other Access query by using the OpenQuery action (in macros) or method (in VBA code). OpenQuery is used to execute both SELECT and NON-SELECT “action” queries. If OpenQuery is run for a SELECT pass-through query, it will return a data sheet grid from the server with all of the requested rows. When run for a NON-SELECT action query, it will not return any completion notification unless there is an error with the query. Finally, when using an action pass-through query within a macro or code, remember to issue the “SetWarnings False” statement if you want to avoid Access’s generic warning message before running the query. Likewise, issue “SetWarnings True” after the query has executed to restore
Access’s warning message capability.
Two common problems associated with pass-through queries are the storage of AS/400 login information and hardcoding library names into the queries. Saving login information in the pass-through query may create problems since users are allowed to view this information (unless additional Access security measures are implemented to prevent this). Additionally, some users might be authorized to run a certain pass-through query on the AS/400 while others may not. In this case, saving a single user login becomes impossible since the pass-through query would have to be run by multiple users.
Likewise, many AS/400 shops have software that runs over multiple library sets. There may be a library designated for testing and a library for production. Since only one library may be stored in the query, how does one run an Access application in multiple environments?
One of many solutions to these problems would be to create temporary “pass- through” queries on the fly that contain the proper library name or login information. After the query has been run, the temporary query can be removed from the database.
Figure 3 contains a sample VBA function that creates a temporary pass-through query. The CreatePassThruQry function accepts an SQL statement and a parm indicating whether or not the query processor should expect to receive records from the server. Optionally, an ODBC user name, password and the max number of records to be returned by the server can be specified. (Note for Access 2000 users: When entering this code into a module, be sure to reference the DAO 3.6 library in the Tools/References option. The References option is only available when editing inside a VBA module.)
The CreatePassThruQry function uses a random number generator to assign a unique name to the query it is creating. If the function successfully creates the query, it will return the resulting query name. If unsuccessful, an empty string will be returned and an error message will display. The query attributes are assigned using a generic ODBC connection string, the SQL parm string, etc. Remember, the SQL syntax will not be checked until the query reaches the server, so just because the function creates a pass- through query does not necessarily mean it will run. Also note that the temporary query will be added to the querydefs collection.
If a user name and password are not specified, then you will be prompted for a user name or password when the query is run. Or, if you already have an AS/400 connection, then it will use the current user name. Specifying the maximum number of records to be returned by the server can be useful in emulating Access’s “TOP” SQL predicate. For example, if you are selecting data from a sales rep summary table and would only like to select the top 20 out of 1000 sales reps, then specify an ORDER BY clause in the SQL statement to sort by sales dollars (descending) and specify 20 as the maximum number of records returned. The resulting recordset will only return the first 20 rows.
To use this function in a VBA code module, pass an SQL statement, specify whether or not the server will return records, and set up a string variable to receive the query name. (Because the function returns a query name value that should be stored in a variable, this function is not readily useable within a macro unless some special techniques, which are beyond the scope of this article, are used.) For instance, code sQryName=CreatePassThruQry(“DROP TABLE QGPL. DATAENTRY”, False, “USER”, “PWD”) to create and retrieve the name of the temporary pass-through query.
Once the query name has been returned, use OpenQuery to run the query (this function does not run the query).
Additionally, this function may be used when coding a form’s Open event. In this case, the form’s .RecordSource property may be set to the name of the newly created pass- through query so that the form can be used with an SQL statement that has a dynamically created WHERE clause. This method is useful when the data set in the form is to be controlled by the user (e.g., this technique simulates a parameter query). One big disadvantage of a pass-through query is that it doesn’t allow for parameter passing. In order to simulate the passing of parms, you will have to change the pass-through query’s SQL every time to select the desired values. When finished with the query, make sure to remove the temporary query. All queries created by this function begin with qryTMP. You may use the DeleteObject statement to remove the query from the database. Don’t forget to compact the database from time to time to reclaim the storage space used by these temporary queries.
For those with the multiple environment dilemma, a possible enhancement to this function would be to pass an entire DSN string as a parameter. This way, if an application has two ODBC data sources (one for test and one for production), the function could be used to control the environment in which the query runs. This option provides an alternative to coding library names inside the query.
Performance Hints
The Client Access ODBC extended dynamic support (EDS) option should be enabled on your ODBC data source. This check box can be found on the Package(s) tab when you are configuring your ODBC data source. You may also notice that your query will run faster after the first time you run it. This is because, when EDS is enabled, it will cache the SQL statement’s execution plan into an SQL package so that when that SQL statement is resubmitted, it will not have to be compiled again.
When designing a query in Access using either linked tables or a pass-through query, never select more fields than necessary. As I was testing relative query speeds using the QSYS2.SYSCOLUMNS table (which stores every field name in every file on the AS/400), I created a query that selected all fields (SELECT *). That query took almost 2 minutes to run because the table’s record length is very large. When I changed the SELECT statement to include only the fields I needed, the query speed was reduced to less than 10 seconds. Also, when executing a pass-through query, make sure that all of the necessary WHERE conditions have been included.
Microsoft Office (including Access) and Visual Basic come with a database engine called JET. JET is an engine designed for access to indexed sequential access method-type flat file databases. It has many wonderful SQL features, but it is not well-geared for large client/server environments. On IBM’s Visual Basic performance Web site, IBM offers some very helpful material for understanding JET and what it does. The portion of this Web site that discusses JET and ODBC is www.as400.ibm.com/developer/client/performance/visualbasic/csvbtpg5.html.
Pass It On
The proper use of pass-through queries can greatly enhance the efficiency of your client/server applications. The capability of sending SQL statements directly to the server without the overhead of JET opens up a fast pathway for processing data. Also, pass- through queries are a developer’s friend because, in many cases, they eliminate the need for tedious VBA programming, reducing development time and simplifying database maintenance.
Related Materials
“Not Able to Adopt ADO? Why Not Use ODBCDirect?" Michael Sansoterra, AS/400 Network Expert, November/ (Web edition), www.midrangecomputing.com/ane
“Links, Imports, Exports: Using ODBC to Share OS/400 Data with Microsoft Access,” Michael Sansoterra, AS/400 Network Expert, September/October 1999 (Includes examples on how to export a Microsoft Access table to an AS/400.)
“More AS/400 Client/Server Programming with ADO and VBA,” Michael Sansoterra, AS/400 Network Expert, July/August 1999 (Includes information on executing AS/400 stored procedures in SQL.) DB2 UDB for AS/400 SQL Reference, AS/400 Information Starting Point Information Center: publib.boulder.ibm. com/pubs/html/as400/infocenter.htm (Enter the Information Center and perform a search on “SQL Stored Procedures” for stored procedure information.)
“Configuring 32-Bit Client Access/400 ODBC, Part 1,” Shannon O’Donnell, Client Access/400 Expert, September/October 1998
“Configuring 32-Bit Client Access/400 ODBC, Part 2,” Shannon O’Donnell, Client Access/400 Expert, November/December 1998 Figure 1: The SQL Pass-Through Query function allows you to easily perform a number of functions without the need for any tedious VBA programming
Figure 2: You can manually specify an ODBC Connection String for your pass-through query by using the Query Properties screen.
Function CreatePassThruQry(sSQL As String, _
bReturnsRecs As Boolean, _
Optional sUser As String = “”, _
Optional sPwd As String = “”, _
Optional lMaxRecs As Long = 0) As String
Dim db As Database
Dim qd As QueryDef
Dim sName As String
Dim errL As Error
Randomize Timer
On Error Resume Next
Set db = CurrentDb ‘Current Database
sName = “qryTMP” & Trim(Str(Timer * 100)) ‘Query name
Set qd = db.CreateQueryDef
‘
‘ Set Pass-through query attributes
‘
With qd
‘ Change the DSN (AS400) to match your ODBC DSN
.Connect = “ODBC;DSN=AS400;UID=” & _
sUser & “;PWD=” & sPwd & “;”
.ReturnsRecords = bReturnsRecs
.MaxRecords = IIf(lMaxRecs > 0, lMaxRecs, 10000)
.SQL = sSQL
.Name = sName
End With
‘
‘ Save the query
‘
db.QueryDefs.Append qd
qd.Close
db.QueryDefs.Refresh
‘
‘ Error Check
‘
If Errors.Count > 0 Then
For Each errL In Errors
MsgBox errL.Number & “ “ & errL.Description
Next
sName = “”
End If
CreatePassThruQry = sName
End Function
Figure 3: This function is used to create a pass-through query entirely with VBA code.
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online