Microsoft KB Archive/114992

= SQLExecQuery and SQLRequest Limited to 255 Characters =

Article ID: 114992

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0c

-



This article was previously published under Q114992





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:  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. 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 </li> To run your procedure, choose Macro from the Tools menu. From the Macro Name/Reference list, select Return_Data, and choose Run.</li></ol>

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

</li></ol>

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.</li> Reference the XLODBC.XLA add-in in your Visual Basic module with the following steps:  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.</li> From the Tools menu, choose References. In the Available References list, select the XLODBC.XLA check box, and choose OK.</li></ol> </li>  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 </li> To run your procedure, choose Macro from the Tools menu. From the Macro Name/Reference list, select From_Worksheet, and choose Run.</li></ol>

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

<div class="status_section">

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.

<div class="moreinformation_section">

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.

<div class="references_section">