|
Private Sub srch()
If TextBox1.Text <> "" Then
Application.ScreenUpdating = False
Dim myData As String, myTable As String, SQL As String
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim i As Integer
'清除工作表的全部数据
On Error Resume Next
Range("A2:k" & [a65536].End(3).Row & "").AutoFilter Field:=2
On Error GoTo errhandle
ActiveSheet.Cells.Clear
sername = "\\" & readInfo("IP_Address")
myData = sername & "\share\Desktop\Database61.accdb" '指定数据库
myTable = "gzmdb" '指定数据表
'建立与数据库的连接
Set cnn = New ADODB.Connection
With cnn
.Provider = "microsoft.ACE.oledb.12.0"
.Open myData
End With
'查询数据表
SQL = "Select rq As 日期,bm As 工号,zw As 职位,xm As 姓名,"
SQL = SQL & "dq as 大区,qy as 区域,ybm As 原部门,xbm As 现部门,rzmx As 入职明细,"
SQL = SQL & "lzmx As 离职明细,zwsjmx As 职位升降 from " & myTable & " where xm like'" & TextBox1.Value & "%' order by xm,rq"
SQL2 = "select distinct bm from gzmdb where xm like'" & TextBox1.Value & "%'"
'MsgBox SQL
Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
rs2.Open SQL2, cnn, adOpenKeyset, adLockOptimistic
'显示查询结果
MsgBox "数据库中的记录数为:" & rs.RecordCount
'复制记录数据
If rs.RecordCount > 0 Then
每次运行到这里,都是rs.RecordCount =-1,但命名中数据库中是有数据的啊,不能进行数据的读取,求教大神,我已经无法了。。。。
With cnn
.Provider = "microsoft.ACE.oledb.12.0"
.Open myData
End With
改为
With cnn
.Provider = "microsoft.ACE.oledb.12.0"
.CursorLocation = adUseClient
.Open myData
End With
|
|