ACC1x: RunSQL Action SQL Statement Limited to 255 Characters

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
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbinfo