vbs 连接oracle 数据库,VBS访问、操作oracle数据库

Sub FetchDataCount()

Dim sheetName As String

sheetName = "test"

Dim dbServiceName As String

dbServiceName = Sheets(sheetName).Cells(1, 1)

If dbServiceName = "" Then

MsgBox "服务名不能为空"

Return: FetchDataCount

End If

Dim tableName As String

tableName = Sheets(sheetName).Cells(1, 2)

Dim sqlCommand As String

sqlCommand = Sheets(sheetName).Cells(1, 3)

Set con = CreateObject("adodb.connection")

con.connectionstring = "provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=sde;password=sde;Data Source=" & dbServiceName

con.Open

If Err <> 0 Then

MsgBox "连接数据库失败"

WScript.Quit

End If

' Dim Rs As ADODB.Recordset

Dim strSQL

Dim rs

If sqlCommand = "" And tableName = "" Then

MsgBox "既未输入查询语句 ，也没输入表名"

Return: FetchDataCount

End If

If sqlCommand = "" Then

strSQL = "select * from " & tableName + " where 1=1 "

Else

strSQL = sqlCommand

End If

'定义查询命令对象

Set rs = con.Execute(strSQL)

Dim flag As Boolean

flag = False

Dim index As Integer

index = 2

Dim iColIndex As Integer

'iColIndex = 1

Do Until rs.EOF

Set fld = rs.Fields

If flag = False Then

For i = 0 To fld.Count - 1 Step 1

Sheets(sheetName).Cells(index, i + 1) = rs.Fields(i).Name

Next i

flag = True

index = index + 2

End If

For m = 0 To fld.Count - 1 Step 1

Sheets(sheetName).Cells(index, m + 1) = rs.Fields(m).Value

Next m

index = index + 1

rs.movenext

Loop

con.Close

Set cmd = Nothing

Set con = Nothing

End Sub