Article ID: 129570
Article Last Modified on 10/11/2006
APPLIES TO
- Microsoft Excel 95a
- Microsoft Excel 5.0c
- Microsoft Excel 5.0 Standard Edition
- Microsoft Excel 5.0a for Macintosh
This article was previously published under Q129570
SYMPTOMS
In Microsoft Excel, if you use the XLODBC.XLA function SQLRetrieve to retrieve consecutive sets of data on the same channel, the SQLRetrieve function may fail to return any data and one of the following error messages will occur:
-or-
For example, the second SQLRetrieve command called may fail if it is expected to return fewer columns of data than the first SQLRetrieve command called on the same channel.
RESOLUTION
This problem no longer occurs if you use the new Visigenic 32-bit Oracle 7 ODBC driver and Microsoft Excel for Windows 95 version 7.0.
STATUS
Microsoft has confirmed this to be a problem in the XLODBC.DLL that comes with Microsoft Excel version 5.0 for Windows NT.
MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The following macro example illustrates a situation in which the second SQLRetrieve command does not retrieve any data:
Sub GetData()
Dim Chan as Integer
' Establish a connection with the Oracle Data Source.
Chan = SQLOpen("DSN=oracledata;UID=userid;PWD=password")
' Execute a query which is expected to return two columns of data.
SQLExecQuery Chan, _
"SELECT EMPS.LAST_NAME, EMPS.FIRST_NAME FROM NORTHWIND.EMPS EMPS"
SQLRetrieve Chan, ActiveSheet.Range("A1")
' Execute a second query which is expected to return only one column
' of data.
SQLExecquery Chan, _
"SELECT EMPS.EMPLOYEE_ID FROM NORTHWIND.EMPS EMPS"
SQLRetrieve Chan, Activesheet.Range("D1")
' Close the connection to the Oracle data source.
SQLClose Chan
End Sub
This macro fails because the second query returns fewer columns than the first query--the second query retrieves only one column of data (EMPLOY_ID) and the first query retrieves two columns of data (LAST_NAME, FIRST_NAME).
If you are unable to use Microsoft Excel version 7.0 (as recommended in the "Resolution" section of this article), use either of the following sample macros:
Sample 1
You could successfully execute the macro example above if you interchange the SQLExecquery lines so that one column is returned in the first SQLExecquery and two columns are returned in the second. For example:
Sub GetData1()
Dim Chan as Integer
' Establish a connection with the Oracle data source.
Chan = SQLOpen("DSN=oracledata;UID=userid;PWD=password")
' Execute a query which is expected to return only one column of
' data.
SQLExecquery Chan, _
"SELECT EMPS.EMPLOYEE_ID FROM NORTHWIND.EMPS EMPS"
SQLRetrieve Chan, Activesheet.Range("D1")
' Execute a second query which is expected to return two columns of
' data
SQLExecquery Chan, _
"SELECT EMPS.LAST_NAME, EMPS.FIRST_NAME FROM NORTHWIND.EMPS EMPS"
SQLRetrieve Chan, Activesheet.Range("A1")
' Close the connection to the Oracle data source.
SQLClose Chan
End Sub
Sample 2
You can also successfully execute the macro example GetData if you close the data source connection after the first query and then re-establish the connection prior to executing the second query. For example:
Sub GetData2()
Dim Chan as Integer
' Establish a connection with the Oracle data source.
Chan = SQLOpen("DSN=oracledata;UID=userid;PWD=password")
' Execute a query which is expected to return two columns of data.
SQLExecquery Chan, _
"SELECT EMPS.LAST_NAME, EMPS.FIRST_NAME FROM NORTHWIND.EMPS EMPS"
SQLRetrieve Chan, Activesheet.Range("A1")
' Close the connection to the Oracle data source.
SQLClose Chan
' Re-establish the connection to the Oracle data source.
Chan = SQLOpen("dsn=oracledata;uid=userid;pwd=password")
' Execute a second query which is expected to return only one column
' of data.
SQLExecquery Chan, _
"SELECT EMPS.EMPLOYEE_ID FROM NORTHWIND.EMPS EMPS"
SQLRetrieve Chan, Activesheet.Range("D1")
' Close the connection to the Oracle data source.
SQLClose Chan
End Sub
Additional query words: 5.00c addin
Keywords: kbbug kbcode kbfix kbprogramming KB129570
