'数据库用户密码
'Public Const db_name As String = "cjw"
'Public Const UserName As String = "tbcs"
'Public Const Password As String = "tbcs"
Public Const db_name As String = "sdmcc"
Public Const UserName As String = "sdmcc"
Public Const Password As String = "sd0129mcc"
'定义变量，添加引用
Public cnOra As ADODB.Connection
Public rsOra As ADODB.Recordset
'数据库查询
Public Function getQuery(sql As String) As ADODB.Recordset
Set cnOra = New ADODB.Connection
Set rsOra = New ADODB.Recordset
'打开连接
cnOra.Open "DSN=" + db_name + ";UID=" + UserName + ";PWD=" + Password + ";"
rsOra.CursorLocation = adUseServer
rsOra.Open sql, cnOra, 1
rsOra.MoveFirst
Set getQuery = rsOra
End Function
'数据库dml
Public Function dbExecute(sql As String)
Set cnOra = New ADODB.Connection
'打开连接
cnOra.Open "DSN=" + db_name + ";UID=" + UserName + ";PWD=" + Password + ";"
cnOra.Execute sql
End Function
'关闭rsOra
Public Function RsClose()
rsOra.Close
cnOra.Close
End Function
-------------查询
Dim getdate As String
getdate = "select * from
WORK_LOGIN where workid='" & workid & "' AND
trunc(createdate)=to_date('" & checkdate.Value &
"','yyyy-mm-dd') order by createdate"
'得到最新的结果
Set rs = getQuery(getdate)
Dim i As Integer
i = 0
Do While Not rs.EOF
Worksheets("check").Cells(2 + i, 1).Value = rs.Fields("opername")
Worksheets("check").Cells(2 + i, 2).Value = rs.Fields("workid")
Worksheets("check").Cells(2 + i, 3).Value = rs.Fields("createdate")
Worksheets("check").Cells(2 + i, 4).Value = rs.Fields("logintype")
Worksheets("check").Cells(2 + i, 5).Value = rs.Fields("state")
rs.MoveNext
i = i + 1
Loop
