Microsoft KB Archive/90102

{|
 * width="100%"|

ACC1x: How to Get SQL Pass-Through Functionality Using Q+E

 * }

Q90102

-

The information in this article applies to:


 * Microsoft Access versions 1.0, 1.1

-

SUMMARY
Microsoft Access does not directly support the use of pass-through SQL (the ability to pass SQL commands directly to a server). However, you can use Access Basic to perform dynamic data exchange (DDE) with the Q+E application, which does support pass-through SQL. Using this method, you can pass SQL commands to run stored procedures and other SQL commands.

MORE INFORMATION
To use Q+E's pass-through SQL capabilities in Microsoft Access, create the following function in a new or existing module.

NOTE: In the following sample code, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

  Function SQLPassThru (SQLCommandText, SQLDatabaseText) On Error Resume Next Err = 0 chan = DDEInitiate(&quot;QE&quot;, &quot;System&quot;) If Err Then Err = 0 Result = Shell(&quot;QE&quot;, 2) If Err Then MsgBox &quot;ERROR: Unable to start Q+E. Q+E must be in your _ PATH statement&quot;, 16, &quot;SQL PassThru&quot; Exit Function End If        chan = DDEInitiate(&quot;QE&quot;, &quot;System&quot;) End If     DDEExecute chan, &quot;[LOGON(SQLServer)]&quot; DDEExecute chan, &quot;[OPEN('USE &quot; & SQLDatabaseText & &quot;; &quot; & _        SQLCommandText & &quot;','SQLSERVER')]&quot; DDEExecute chan, &quot;[EXIT]&quot; DDETerminate chan End Function

The SQLPassThru function initiates a DDE channel with Q+E. If Q+E is not available, the function attempts to start Q+E. If Q+E cannot be started, the function displays an error message and ends. If Q+E can be started, a DDE channel is initiated. The SQLPassThru function then directs Q+E to display its Logon dialog box so you can log on to SQL Server. The SQL USE command is used to ensure that SQLPassThru is in the database you specify, and SQLPassThru runs the SQL command. After the command is run, SQLPassThru exits the Q+E application and terminates the DDE channel that the function established.

The following demonstrates a sample call to the SQLPassThru function:

  =SQLPassThru(&quot;sp_addlogin Ted, ted&quot;,&quot;master&quot;)

In the above example, the SQLPassThru function will start Q+E and initiate a DDE channel, if possible. You will then be prompted to log on to a SQL Server. After you log on, the function will use the master database on the SQL Server and run the stored procedure sp_addlogin, which will add a login for a user named Ted with a password of &quot;ted.&quot; After the command runs, the function will exit the Q+E application and terminate the DDE channel.

NOTE: You must include quotation marks around both of the parameters to ensure that they are used properly. You may also have to include additional quotation marks in the SQL command you are sending to the SQL Server to ensure that the command is being processed correctly. For examples of the types of adjustments that you may need to make to the SQL command string, see the &quot;Q+E for Microsoft Excel User's Guide.&quot;

You can modify this function in many different ways, including:


 * To accept additional parameters and have additional corresponding DDEExecute commands, thereby allowing the execution of multiple SQL commands with one function call
 * To use as a Sub procedure in which you can hard-code the parameters for the command and SQL database in the Access Basic code
 * To retrieve information from the SQL server, or send information to the SQL server using the DDERequest and DDEPoke commands
 * To use in conjunction with a form to allow on-line SQL command execution
 * To break into multiple functions with different capabilities, such as one function to initiate the channel, another function to execute the commands, and another function to terminate the channel
 * To access database types that Microsoft Access does not support but that Q+E does.