ID: Q114992
The information in this article applies to:
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.
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.
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:
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:
a. 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.
b. From the Tools menu, choose References. In the Available References
list, select the XLODBC.XLA check box, and choose OK.
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
4. To run your procedure, choose Macro from the Tools menu. From the Macro
Name/Reference list, select Return_Data, and choose Run.
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
2. Insert a new module sheet in your workbook.
3. Reference the XLODBC.XLA add-in in your Visual Basic module with the
following steps:
a. 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.
b. From the Tools menu, choose References. In the Available References
list, select the XLODBC.XLA check box, and choose OK.
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
Name/Reference list, select From_Worksheet, and choose Run.
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.
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.
For more information about the SQLExecQuery function, choose the Search button in the Visual Basic Reference and type:
SQLExecQuery function
SQLRequest function
Keywords : kbprg xlvbahowto kbbuglist
Version : 5.00 5.00c 7.00
Platform : WINDOWSLast Reviewed: November 19, 1998