从 Excel VBA 使用 ODBC 连接到 Oracle 10g
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1727267/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Connecting to Oracle 10g with ODBC from Excel VBA
提问by Peter Goras
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
以下代码有效。连接打开正常，但当表中有数据时，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
回答by Dan
If you want Recordcount to work, especially without fetching the records, you need to use adUseClientnot adUseServer
如果你想让 Recordcount 工作，特别是不获取记录，你需要使用adUseClientnotadUseServer
If 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.
但是，如果这是一个大型操作并且您将经常执行它，那么您应该真正选择 count() 并让数据库为您进行计数。随着adUseClientADO将静默获取的所有记录到你的应用程序的内存不管你喜欢还是不喜欢。
回答by Peter Goras
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.
Excel 似乎崩溃了，因为它无法处理某些 Oracle 数据类型。select * 返回一个类型为 TIMESTAMP(3) WITH TIME ZONE 的列。一旦我尝试从记录集中读取，这将导致 Excel 崩溃。
