ADODB记录集recordcount始终返回-1 [英] ADODB recordset recordcount always returns -1

问题描述

我正在尝试检索数据以在MS Access中形成数据库.但是，记录集的recordcount属性始终返回-1，尽管出于其他目的，该代码也可以正常工作.

I am trying to retrieve data to excel form a database in MS access. However the recordcount property for recordset always return -1 though for other purposes the code is working fine.

我正在使用的代码如下:`Sub datarecordset()

The code I am using is as follows : `Sub datarecordset()

Dim cn As adodb.Connection
Dim oRs As adodb.Recordset
Set cn = CreateObject("ADODB.Connection")
DBPath = "C:\[databse path]" & "\[database name].accdb"
dbWs = "[excel sheet name]"
scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBPath
dsh = "[" & "[excel sheet name]" & "$]"
cn.Open scn
Dim sSQL As String

Dim F As Integer

sSQL = "Select 'W',a.[Subledger],NULL,sum(a.[Amount]) from GL_Table a where a.[Opex_Group] = 10003 and year(a.[G/L Date]) = " & Year(Sheets("Repairs").Cells(1, 4)) & " and month(a.[G/L Date]) = " & Month(Sheets("Repairs").Cells(1, 4))
sSQL = sSQL & " group by " & "a.[Subledger],(year(a.[G/L Date])),(month(a.[G/L Date]))"
Set oRs = cn.Execute(sSQL)
Debug.Print oRs.RecordCount
oRs.Close
....... further code to print to excel here

cn.Close
End Sub`

该代码将获取记录集中的数据并写入excel.但是，由于recordset属性未返回recordcount，因此无法将记录集中各个字段的值打印到excel工作表的不同单元格中.

The code will fetch data in recordset and write in excel. But since the recordset property is not returning the recordcount so can't print values of various fields in recordset to different cells of excel worksheet.

我在google上搜索并了解到，我需要声明记录集类型，因此必须使用connection.open代替connection.execute.但是我试图更改代码，然后给出错误对象变量或With变量未定义.

I searched on google and understood that I need to declare the recordset type and for that I have to use connection.open in place of connection.execute. But I am trying to change the code then it gives the error object variable or With variable not defined.

任何快速帮助都将受到欢迎.谢谢.

Any quick help will be welcome. Thanks.

推荐答案

@BitAccesser的链接提供了有效的解决方案.在您的情况下快速实现方法:代替 Set oRs = cn.Execute(sSQL)

The link by @BitAccesser provides a valid solution. Quick how-to-implement in your situation: Instead of Set oRs = cn.Execute(sSQL)

Set oRS = CreateObject("ADODB.Recordset")
oRS.CursorLocation = adUseClient
oRS.Open sSQL, cn

当ADO无法确定记录数，或者提供程序或游标类型不支持RecordCount时，

ADO 的recordcount属性将返回-1.在这种情况下，最后一个是正确的.

ADO's recordcount property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. That last one is true for this case.

为避免这种情况，有几种解决方案.最简单的方法是使用我刚刚演示的客户端游标，但是@BitAccesser的链接中提供了更多替代解决方案

To avoid this, there are several solutions. The most simple one is to use a client-side cursor, which I just demonstrated, but more alternative solutions are provided in the links by @BitAccesser

这篇关于ADODB记录集recordcount始终返回-1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！