Microsoft KB Archive/115185

{|
 * width="100%"|

ACC1x: RunSQL Action SQL Statement Limited to 255 Characters

 * }

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

Issue type : kbinfo

Technology :