ACC: Sample Code for Running Temporary SQL Pass-Through Query

ID: Q124391

The information in this article applies to:

Microsoft Access version 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article lists sample code that you can use to call and run a temporary SQL pass-through query.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

You can use the following sample Visual Basic code to call and run a temporary SQL pass-through query:

   Function ExecuteSPT (sqltext As String, connectstring As String)

   ' Purpose: Run a temporary pass-through query.
   ' Accepts: sqltext: SQL string to run.
   '          connectstring: Connection string, which must be at least
   '          "ODBC;".
   ' Returns: nothing.

   Dim mydb As Database, myq As QueryDef
   Set mydb = DBEngine.Workspaces(0).Databases(0)

   ' Create a temporary QueryDef object that is not saved.
   Set myq = mydb.CreateQueryDef("")

   ' Set the ReturnsRecords property to False in order to use the
   ' Execute method.
   myq.returnsrecords = False

   myq.connect = connectstring
   myq.sql = sqltext

   myq.Execute
   myq.Close

   End Function

To run a stored procedure called sp_example on your SQL Server, you could type the following line in the Debug window (or Immediate window in version 2.0):

   ?ExecuteSPT("sp_example","ODBC;")

Note that this code accepts SQL statements that are specific to the server that you are using. Please refer to your server's documentation to determine valid SQL statements for your server.

REFERENCES

For more information about pass-through queries, search the Help Index for "pass-through queries," or ask the Microsoft Access 97 Office Assistant.

Additional query words:

Keywords          : kbprg kbusage QryPass 
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbinfo

Last Reviewed: November 21, 1998