vba连接oracle3706,vba连接oracle - janv的日志 - 网易博客

'数据库用户密码

'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