ACC1x: RunSQL Action SQL Statement Limited to 255 Characters

Last reviewed: June 8, 1997
Article ID: Q115185

The information in this article applies to:

Microsoft Access versions 1.0, 1.1

SUMMARY

The SQL statement in a RunSQL action is limited to 255 characters. This article describes a sample user-defined function called RunSQL2() that you can use in place of the RunSQL action to run large SQL statements.

MORE INFORMATION

The RunSQL2() function creates a new query programmatically using the CreateQueryDef method with the SQL statement you pass to it as an argument. Once the query is created, it is run using the Execute method, and then deleted using the DeleteQueryDef method.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual.

The RunSQL2() Function

   '************************************************************
   'Declarations section of the module.
   '************************************************************
   Option Explicit

   '=============================================================
   ' Create the following function in the module.
   '=============================================================
   Function RunSQL2(ByVal SQL As String)
      Dim db As Database
      Dim Q As QueryDef
      Dim SQL As String

      Set db = CurrentDB()
      Set Q = db.CreateQueryDef("TempQuery", SQL)
      Q.Execute
      Q.Close
      db.DeleteQueryDef("TempQuery")

   End Function

How to Use the RunSQL2() Function

The following sample user-defined Access Basic function uses the RunSQL2() function to run an SQL statement that is longer than 255 characters. This function, when run in the sample database NWIND.MDB, will create a table called New Customers Table with data from the Customers table. The query is a make-table query.

   Function CreateNewCustomersTable()
      Dim SQL As String

      SQL = "SELECT  DISTINCTROW Customers.[Customer ID], "
      SQL = SQL & "Customers.[Company Name], Customers.[Contact Name], "
      SQL = SQL & "Customers.[Contact Title], Customers.Address, "
      SQL = SQL & "Customers.City, Customers.Region, Customers.[Postal
         Code], "
      SQL = SQL & "Customers.Country, Customers.Phone, Customers.Fax "
      SQL = SQL & "INTO [New Customers Table] FROM Customers "
      SQL = SQL & "WITH OWNERACCESS OPTION;"

      RetVal = RunSQL2(SQL)

   End Function

Keywords : kbprg PgmOthr
Version : 1.0 1.1
Platform : WINDOWS
Hardware : X86
Issue type : kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 8, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.