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

ID: 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("QE", "System")
      If Err Then
         Err = 0
         Result = Shell("QE", 2)
         If Err Then
             MsgBox "ERROR: Unable to start Q+E. Q+E must be in your _
                PATH statement", 16, "SQL PassThru"
             Exit Function
         End If
         chan = DDEInitiate("QE", "System")
      End If
      DDEExecute chan, "[LOGON(SQLServer)]"
      DDEExecute chan, "[OPEN('USE " & SQLDatabaseText & "; " & _
         SQLCommandText & "','SQLSERVER')]"
      DDEExecute chan, "[EXIT()]"
      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("sp_addlogin Ted, ted","master")

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 "ted." 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 "Q+E for Microsoft Excel User's Guide."

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.

REFERENCES

Microsoft Access "Language Reference," version 1.0, pages 115-126

Microsoft Access "Introduction to Programming," version 1.0, Chapter 2, pages 1-8

Microsoft Excel "Q+E for Microsoft Excel User's Guide," version 4.0, pages 101-102

Keywords : kbinterop QryPass
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbhowto