SAMPLE: SQLPASS2 - Sample Application

PSS ID Number: Q103305
Article last modified on 08-25-1993

3.10
WINDOWS

----------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Windows Software Development Kit (SDK) for Windows
   version 3.1
----------------------------------------------------------------------

DEMOSPT 1.1: Passes SQL Commands to SQL Back-End Via ODBC
DEMOSPT.MDB is a Microsoft Access® database. It uses a dynamic-link
library (DLL) that enables Microsoft Access Basic programmers to pass
SQL commands directly to SQL back-end databases via open database
connectivity (ODBC).
DEMOSPT is one of several samples and tools that demonstrate what
other developers have done with Microsoft Access databases. These
tools have not been tested, although some of them have been around for
a while. The Microsoft Access tools are not supported by Microsoft
Product Support Services (PSS) or on CompuServe®; please use these
tools at your own risk.
To run DEMOSPT, copy the SPT.DLL file into your Windows SYSTEM
directory (or into the directory your system checks for DLL files),
and double-click the DEMOSPT.MDB file.
The SQL Pass-Through DLL (SPT.DLL) provides SQL support beyond that
provided by Microsoft Access linked tables. Although linked tables
cover a wide range of requirements for transparent access to remote
data (including binding Microsoft Access form, report, and query
objects directly to remote tables and views and performing
local-remote joins), linked tables don't support functionality such as
executing row-returning stored procedures, remote DDL, and
back-end-specific SQL. SPT.DLL provides these capabilities. The DLL
requires Microsoft Access and ODBC.
SPT.DLL allows an Access Basic programmer to pass back-end-specific
(possibly row-returning) SQL strings to an SQL processing back-end. In
other words, the DLL is simply a link between Microsoft Access and
ODBC. ODBC is a specification that defines a call-level interface, or
set of function calls, for applications. The interface enables
communication between an application and one or more database
management systems. Note that SPT.DLL doesn't parse the passed string.
For example, it doesn't check to see if a called function is actually
supported by the back-end. The assumption is that users of this DLL
will know the type of back-end they are connected to and the specific
version of SQL that the back-end uses. The Message table documents any
errors.
SPT.DLL contains four functions: one establishes a connection with a
remote database; one terminates that connection; a third performs the
SQL pass-through and deals with any returned items or messages; and a
fourth returns the last error. This last function is provided in case
an error occurs before the Message table can be created. SPT.DLL
creates two types of tables in Microsoft Access: Message tables and
Result tables.
In the course of establishing links and running SQL and stored
procedures, errors and application-specific messages originate from
the remote server itself, from Microsoft Access, and from ODBC.
SPT.DLL inserts the text for errors and messages in a Message table. A
specific Message table is defined for each connection. Multiple
connections may share the same Message table. Depending on how you set
the AppendMsgs argument when you make an SQL pass-through call, you
can either append new messages to the end of the Message table with
each call, or purge the Message table with each call.
You are responsible for deleting the Message tables when they are no
longer needed. For more information on Message tables, see the
SPTDESCR.TXT file.
DEMOSPT was built and tested under Microsoft Access version 1.1.
KEYWORDS: CD4
For Microsoft OnLine customers, SQLPASS2 is available in the
Software/Data Library as 4X89.ZIP and can be found by searching on the
word SQLPASS2, the Q number of this article, or S14264. For CompuServe
Customers, SQLPASS2 can be downloaded from the file 4-89.ZIP in
library section number 4 of the MSDNLIB forum. SQLPASS2 was archived
using the PKware file-compression utility.

Additional reference words: 3.10
KBCategory:
KBSubcategory:
Copyright Microsoft Corporation 1993.