使用 ODBC 从 Excel VBA 连接到 Oracle 10g
以下代码有效。连接打开正常，但当表中有数据时，recordset.recordCount 始终返回 -1。如果我尝试调用记录集上的任何方法/属性，它会使 Excel 崩溃。有什么想法吗？ 谢谢
Sub GetData()
Dim conn As New ADODB.connection
Dim connString
connString = "DSN=name;Uid=user;Pwd=pass"
Dim rsRecords As New ADODB.recordSet
conn.Open connString
rsRecords.CursorLocation = adUseServer
rsRecords.Open "select * from xxx", conn, adOpenForwardOnly, adLockReadOnly
If conn.State = adStateOpen Then
MsgBox rsRecords.RecordCount
Else
MsgBox "no connection"
End If
rsRecords.Close
Set rsRecords = Nothing
conn.Close
Set conn = Nothing
End Sub
The following code works. the connection opens fine but recordset.recordCount always returns -1 when there is data in the table. ANd If I try to call any methods/properties on recordset it crashes Excel. Any ideas??
Thanks
Sub GetData()
Dim conn As New ADODB.connection
Dim connString
connString = "DSN=name;Uid=user;Pwd=pass"
Dim rsRecords As New ADODB.recordSet
conn.Open connString
rsRecords.CursorLocation = adUseServer
rsRecords.Open "select * from xxx", conn, adOpenForwardOnly, adLockReadOnly
If conn.State = adStateOpen Then
MsgBox rsRecords.RecordCount
Else
MsgBox "no connection"
End If
rsRecords.Close
Set rsRecords = Nothing
conn.Close
Set conn = Nothing
End Sub
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您希望 Recordcount 正常工作，尤其是在不获取记录的情况下，则需要使用
adUseClient而不是adUseServer如果这是一项大型操作，并且您会经常执行此操作，您实际上应该选择 count() 并让数据库为您进行计数。使用
adUseClient，无论您是否愿意，ADO 都会默默地将所有记录提取到应用程序的内存中。If you want Recordcount to work, especially without fetching the records, you need to use
adUseClientnotadUseServerIf this is a large operation and you'll be doing it frequently, though, you should really select the count() instead and make the database do the counting for you. With
adUseClientADO will silently fetch all the records into your application's memory whether you like it or not.Excel 似乎崩溃了，因为它无法处理某些 Oracle 数据类型。
select * 返回类型为 TIMESTAMP(3) WITH TIME ZONE 的列。
当我尝试从记录集中读取数据时，这会导致 Excel 崩溃。
It seems Excel was crashing because it cannot handle some Oracle datatypes.
The select * was returning a column of type TIMESTAMP(3) WITH TIME ZONE.
This would cause Excel to crash as soon as I tried to read from the recordset.