XL: SQLExecQuery and SQLRequest Limited to 255 Characters

ID: Q114992

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

In Microsoft Excel, if you use the SQLExecQuery function or the SQLRequest function in a Visual Basic procedure to run a query on an external data source, and your query is larger than 255 characters, you may receive a general protection (GP) fault when you run the procedure.

CAUSE

When you use a function in the XLODBC.XLA add-in, such as SQLRequest, and you use a query that contains more than 255 characters, the query is truncated after the first 255 characters.

As a result, you can convert your query string into an array, where each element of the array can contain up to 255 characters; however, you may receive a GP fault if an element in the array is larger than 127 characters. Note that this behavior does not occur every time you run the procedure. The results are not consistent.

You can also use the Range object to reference multiple cells that contain your query. Again, you may receive a GP fault if any of the cells in the range that contains your query contain more than 127 characters.

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.

Insert a new module in your workbook.

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.

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

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:

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.

Insert a new module sheet in your workbook.

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.

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

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.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version 5.0c and Microsoft Excel for Windows 95 version 7.0.

Note that the 255 character limit when you use a function in the XLODBC.XLA add-in still exists, but you will no longer receive a GP fault.

MORE INFORMATION

You can use the SQLRequest function to connect to an external data source and run a query from a worksheet, and then return the result as an array. The SQLExecQuery function executes a query on a data source with a connection that has been established using SQLOpen. Both of these functions are contained in the XLODBC.XLA add-in. You must establish a reference to the XLODBC.XLA file using the References dialog box before you can use either function in a procedure.

REFERENCES

For more information about the SQLExecQuery function, choose the Search button in the Visual Basic Reference and type:

SQLExecQuery function

For more information about the SQLRequest function, choose the Search button in the Visual Basic Reference and type:

SQLRequest function

Additional query words: 1.00 5.00c msquery sql addins addin macro gpf

Keywords : kbprg xlvbahowto
Version : WINDOWS:5.0,5.0c; Win95:7.0
Platform : Win95 WINDOWS
Issue type :