这是一段在excel执行的SQL程序
Sub ls()
Dim Sql$ ', Cnn As ADODB.Connection
Dim Rst As ADODB.Recordset
Set Cnn = CreateObject("ADODB.Connection")
Cnn.Open "Provider = MicroSoft.Jet.OLEDB.4.0; Extended Properties = Excel 8.0; Data Source = " & ThisWorkbook.FullName
Sql = "Select * from [Sheet3$] Where ObjectName like '%Dimension%'"
Set Rst = Cnn.Execute(Sql)
Debug.Print "Line", Rst.RecordCount
' 为什么在这里rst.REcordCount会得-1
Sheet1.Range("A:Z").ClearContents
Sheets(1).Range("A1").CopyFromRecordset Rst '---会到Excel后是需要的数据
Rst.Close
End Sub
Sub ls()
Dim Sql$ ', Cnn As ADODB.Connection
Dim Rst As ADODB.Recordset
Set Cnn = CreateObject("ADODB.Connection")
Cnn.Open "Provider = MicroSoft.Jet.OLEDB.4.0; Extended Properties = Excel 8.0; Data Source = " & ThisWorkbook.FullName
Sql = "Select * from [Sheet3$] Where ObjectName like '%Dimension%'"
Set Rst = Cnn.Execute(Sql)
Debug.Print "Line", Rst.RecordCount
' 为什么在这里rst.REcordCount会得-1
Sheet1.Range("A:Z").ClearContents
Sheets(1).Range("A1").CopyFromRecordset Rst '---会到Excel后是需要的数据
Rst.Close
End Sub
Sheets(1).Range("A1").CopyFromRecordset Rst '---传到Excel后,需要的数据完成正确.
Sql = "Select * from [Sheet3$] Where ObjectName like '%Dimension%'"
rst.cursorlocation=adUseClient
Set Rst = Cnn.Execute(Sql)
-1 就是不返回记录数。因为返回记录数是需要遍历记录的。大于大数据库,这是非常费时的。所以缺省条件是不返回记录数。如果确实需要记录数,需要:1 采用客户端游标:(打开记录机之前)
Rst.CursorLocation = adUseClient2 遍历记录:(打开记录机之后)
Rst.MoveLast
此时便可取得记录数了。
如果在查询中采用了 Order by,Group by 子句则第二步可省,因为查询中已经遍历了记录。否则,需要第二步。不然的话,得到的记录数可能不是全部记录。
Dim Cnn As New ADODB.Connection
Dim Rst As New ADODB.Recordset
Cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1';data source=" & ThisWorkbook.FullName
Sql = "Select * FROM [Sheet3$] where ObjectName like '%Line%'"
'(5)设置 Sql 语句
Rst.Open Sql, Cnn, adOpenStatic 'adOpenStatic静态指针,以便计算记录个数
我要用
with rst
for ii = 0 to .RecordCount
next ii
end with
请问这位大侠,RecordCount = -1的情况主要在什么地方应用??谢谢。
你完全可以
with rst
Do Unitl .EOF .MoveNext
Loop
end with事先不必知道记录数
我要用
with rst
for ii = 0 to .RecordCount
next ii
end with
请问这位大侠,RecordCount = -1的情况主要在什么地方应用??谢谢。
Set mm = rr
With mm
.MoveFirst
Do 'While .EOF
Debug.Print .Fields(2) .MoveNext
Loop
End With
上面程序的输出结果
说明MoveFirst的结果为Null,MoveNext的结果为有数值。见,
Null
92.1500
87.6000
87.5000
'''
Function rr() As ADODB.Recordset
Dim Sql$ ', Cnn As ADODB.Connection
Dim Rst As ADODB.Recordset
Set Cnn = CreateObject("ADODB.Connection")
Cnn.Open "Provider = MicroSoft.Jet.OLEDB.4.0; Extended Properties = 'excel 8.0;imex=1'; Data Source = " & ThisWorkbook.FullName
Sql = "Select * from [Sheet3$]" ' Where ObjectName like '%Dimension%'"
Set Rst = Cnn.Execute(Sql)
Set rr = Rst
End Function