SQLExecQuery and SQLRequest Limited to 255 Characters (114992)

WORKAROUND

To avoid receiving a GP fault, and to avoid having your query truncated when you use the SQLExecQuery or the SQLRequest functions with a query that is larger than 255 characters, use one of the following methods:

Array Method

Limit the elements in the array that you create to handle your query to 127 characters or less. This example uses the QueryToArray function to return your query as an array of values that each contain 127 characters of your query. This array can then be used as your "new" query.

1. Insert a new module in your workbook.
2. Reference the XLODBC.XLA add-in in your Visual Basic module with the following steps:
   1. From the File menu, choose Open. From the Directories list, select the \LIBRARY\MSQUERY subdirectory of the Microsoft Excel directory. From the File Name list, select XLODBC.XLA and choose OK.
   2. From the Tools menu, choose References. In the Available References list, select the XLODBC.XLA check box, and choose OK.
3. In the module, enter the following code. Note that this example uses the NWind data source installed with Microsoft Query, and the query used in the example selects data from the Employee table. You can modify this example to use any available data source and table.

      Sub Return_Data()
   
         ' Dimensions variables
         Dim chan As Integer, longquery As Variant, query As Variant
   
         ' Establish a connection to NWind data source and set
         ' value of variable 'chan' to channel number returned
         ' Note that in the following line, C:\WINDOWS should be your
         ' Microsoft Windows directory
         ' If using Microsoft Excel 7.0, the DBQ string will be different
         ' from that shown here.
         chan = sqlopen("DSN=NWind;DBQ=C:\WINDOWS\MSAPPS\MSQUERY;FIL=dBase4;")
   
         ' Set value of variable 'longquery' to query you want to run
         longquery = "SELECT employee.ADDRESS, employee.BIRTHDATE, " & _
          "employee.CITY, employee.COUNTRY, employee.EMP_TITLE, " & _
          "employee.EMPLOY_ID, employee.EXTENSION, " & _
          "employee.FIRST_NAME, employee.HIRE_DATE, employee.HOME_PHONE, " & _
          "employee.LAST_NAME, employee.NOTES, employee.REGION, " & _
          "employee.REPORTS_TO, employee.ZIP_CODE FROM " & _
          "c:\windows\msapps\msquery\employee.dbf employee"
   
         ' Use 'QueryToArray' function with variable 'longquery'
         ' Set value of variable 'query' to value returned by function
         query = QueryToArray(longquery)
   
         ' Execute query using variable 'query' returned by function
         SQLExecQuery chan, query
   
         ' Return data starting in cell A1 on Sheet1
         SQLRetrieve chan, Application.Worksheets("Sheet1").Cells(1, 1)
   
         ' Close channel
         SQLClose chan
   
      End Sub

      ' Dimension variables
      Dim Shift As Integer, Size As Integer, I As Integer
   
      ' If no maximum string length is provided (MaxLength), then
      ' set maximum size of string in array to 127 characters
      If IsMissing(MaxLength) Then MaxLength = 127
   
      ' Account for Option Base default value
      Shift = LBound(Array(1)) - 1
   
      ' Determine the number of elements that will be contained
      ' in the array that this function returns by dividing the
      ' number of characters in the long query by the maximum
      ' size of the string
      Size = (Len(Q) + MaxLength) / MaxLength
   
      ' Dimension array 'TmpArr'
      ReDim tmparr(Size + Shift, 1 + Shift) As String
   
      For I = 1 To Size
      ' Load array 'TmpArr' with one value for each 127 characters
      ' of your query
         tmparr(I + Shift, 1 + Shift) = _
          Mid$(Q, (I - 1) * MaxLength + 1, MaxLength)
      Next I
   
      ' Assign array returned by QueryToArray function to temparr
      QueryToArray = tmparr
   
   End Function

4. To run your procedure, choose Macro from the Tools menu. From the Macro Name/Reference list, select Return_Data, and choose Run.

The data returned from the query is now contained in cell A9 on Sheet1 in your workbook.

Range Method

To avoid truncating your query when you use the SQLExecQuery function or the SQLRequest function, you can also use the Range method to reference a query contained in multiple cells on a worksheet. The following is an example of using this method:

1. Enter your query string on a worksheet in your workbook. For example, enter the following on Sheet1:

   A1: SELECT employee.ADDRESS, employee.BIRTHDATE, employee.CITY,
   A2: employee.COUNTRY, employee.EMP_TITLE, employee.EMPLOY_ID,
   A3: employee.EXTENSION, employee.FIRST_NAME, employee.HIRE_DATE,
   A4: employee.HOME_PHONE, employee.LAST_NAME, employee.NOTES,
   A5: employee.REGION, employee.REPORTS_TO, employee.ZIP_CODE FROM
   A6: c:\windows\msapps\msquery\employee.dbf employee
   

NOTE: To avoid receiving a GP fault when you use this method in a procedure in Microsoft Excel version 5.0, make sure that each cell in the range (A1:A6 above) that contains your query, does not contain more than 127 characters. Also, add a space at the end of lines A1:A5. A6 does not require the extra space at the end of the line.

1. Insert a new module sheet in your workbook.
2. Reference the XLODBC.XLA add-in in your Visual Basic module with the following steps:
   1. From the File menu, choose Open. From the Directories list, select the \LIBRARY\MSQUERY subdirectory of the Microsoft Excel directory. From the File Name list, select XLODBC.XLA and choose OK.
   2. From the Tools menu, choose References. In the Available References list, select the XLODBC.XLA check box, and choose OK.
3. In the new module, enter the following:

      Sub From_Worksheet()
         ' Dimensions variables
         Dim chan As Integer, query As Variant
   
         ' Establish a connection to NWind data source and set
         ' value of variable 'chan' to channel number returned
         chan = sqlopen("DSN=NWind;DBQ=C:\WINDOWS\MSAPPS\MSQUERY;FIL=dBase4;")
   
         ' Set value of variable 'query' to query contained
         ' in cells A1:A6 on Sheet1
         query = Worksheets("Sheet1").Range("A1:A6")
   
         ' Execute query using variable 'query' returned from worksheet
         SQLExecQuery chan, query
   
         ' Return data to cell A9 on Sheet1
         SQLRetrieve chan, Application.Worksheets("Sheet1").Cells(9, 1)
   
         ' Close channel
         SQLClose chan
   
      End Sub

4. To run your procedure, choose Macro from the Tools menu. From the Macro Name/Reference list, select From_Worksheet, and choose Run.

The data returned from the query is now contained in cell A9 on Sheet1 in your workbook.