7,763
社区成员
发帖
与我相关
我的任务
分享
Me.Label2.Caption = "正在执行SQL语句......"
DoEvents
rs.Open sql, conn, 1, 1, adAsyncExecute
End Sub
Private Sub conn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
'生成EXCEL
Me.Label2.Caption = "正在生成EXCEL文件......"
DoEvents
Set e_app = CreateObject("excel.application")
e_app.Visible = False
Set e_wkbk = e_app.workbooks.Open(App.Path & "\mb\aaaa.xls")
Set e_sheet = e_wkbk.sheets(1)
i = 3
dt = Mid(Me.Text1.Text, 1, 4) & "年" & Mid(Me.Text1.Text, 5, 2) & "月"
'Debug.Print rs.RecordCount
While Not rs.EOF
e_sheet.cells(i, 1) = dt
e_sheet.cells(i, 4) = rs(1)
e_sheet.cells(i, 5) = rs(1)
i = i + 1
rs.MoveNext
DoEvents
Wend
e_sheet.cells(i, 1) = dt
rs.Close
Set pRecordset = Nothing
e_wkbk.saveas "d:\1.xls"
e_wkbk.Close
e_app.quit
conn.Close
Set conn = Nothing
MsgBox "测试成功"
End Sub
如果已经将 CursorLocation 属性设置为 adUseClient,就可以采用两种途径之一异步检索行。建议使用的方法是将 Options 设置为 adAsyncFetch。或者,可以使用在 Properties 集合中的“异步行集合处理”动态属性,但如果未将 Options 参数设置为 adAsyncFetch,则可能丢失相关的被检索事件。
'添加一个Form和一个Command,引用ADO2.8,并添加一个Datagrid.
Dim WithEvents CONN As ADODB.Connection
Dim WithEvents RS As ADODB.Recordset
Private Sub Command1_Click()
Set RS = New ADODB.Recordset
RS.CursorLocation = adUseClient '如果是Open方法执行,且Connection未设置游标位置,则此句不可少
RS.Open "Select * From Sysobjects", CONN, adOpenForwardOnly, adLockReadOnly, adAsyncExecute Or adAsyncFetch
'Set RS = CONN.Execute("Select * from sysobjects", , adAsyncExecute Or adAsyncFetch)
While RS.State = adStateExecuting
DoEvents
Debug.Print "正在执行"
Wend
Debug.Print "执行完毕1" '注意不要MsgBox,否则不会执行ExecuteComplete和FetchProgress和FetchComplete事件
Set grid1.DataSource = RS
End Sub
Private Sub CONN_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
Debug.Print "执行完毕2" '注意不要换成MsgBox,否则很可能不会执行FetchProgress和FetchComplete事件
End Sub
Private Sub CONN_WillExecute(Source As String, CursorType As ADODB.CursorTypeEnum, LockType As ADODB.LockTypeEnum, Options As Long, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
Debug.Print Source
End Sub
Private Sub Form_Load()
Set CONN = New ADODB.Connection
CONN.CursorLocation = adUseClient '如果用Execute方法执行SQL,则此句不可少
CONN.Open "Provider=SQLOLEDB.1;User ID=用户名;PWD=密码;Server=服务器地址;Initial Catalog=数据库名"
End Sub
Private Sub RS_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
MsgBox "读取完毕"
End Sub
Private Sub RS_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
Debug.Print Progress '输出提取进度
End Sub
Private Sub rs_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)