怎样设置excel可以使用vba连接oracle数据库

发布网友发布时间：2022-04-07 23:50

共2个回答

懂视网时间：2022-04-08 04:11

Sub ConOra()Dim sht As Worksheet Set sht = ActiveSheet On Error GoTo ErrMsg: ‘连接串 Dim ConnDB As ADODB.Connection Set ConnDB = New ADODB.Connection Dim ConnStr As String ‘ 结果集 Dim DBRst As ADODB.Recordset Set DBRst = New ADODB.Recordset ‘SQL文 Dim SQLRst As String Dim OraOpen As Boolean OraOpen = False ‘Oracle数据库的相关配置 ConnStr = getConnStr() ‘ ConnDB.CursorLocation = adUseServer ConnDB.Open ConnStr ConnDB.Execute ("select * from dual") OraOpen = True ‘成功执行后，数据库即被打开 ‘MsgBox "Connect to the oracle database Successful!", vbInformation, "Connect Successful" ‘DBRst.ActiveConnection = ConnDB ‘DBRst.CursorLocation = adUseServer ‘DBRst.LockType = adLockBatchOptimistic SQLRst = "Select * From " & getTableName() & " where rownum = 1 " ‘DBRst.Open SQLRst, ConnDB, adOpenStatic, adLockBatchOptimistic DBRst.Open SQLRst, ConnDB

查询之后，对于RecordSet 的使用：

1、获取列名：

 For i = 1 To DBRst.Fields.Count
 sht.Cells(2, i) = DBRst.Fields(i - 1).Name
 Next

2、获取值：

 Do Until DBRst.EOF
 For i = 1 To DBRst.Fields.Count
  sht.Cells(4, i) = DBRst.Fields(i - 1).Value
 Next i
 DBRst.MoveNext
 Loop

3、判断RecordSet是否为空：

 If DBRst.BOF And DBRst.EOF Then
 MsgBox "数据库中未查询出任何数据，请将做成的数据写入第4行！", vbInformation, "提示"
 End If

4、记得关闭连接哟。

5、如果按照以上方法，仍无无法连接DB，并提示：连接无法打开，这是因为Office的安装目录中包含（86）导致的，重新按照OFFICE到其他非program文件夹下即可解决。

ExcelVBA连接Oracle

标签：

热心网友时间：2022-04-08 01:19

Sub GetData()

Dim SQL_String As String
Dim dbConnectStr As String
Set con = New ADODB.Connection
Set recset = New ADODB.Recordset
Dim recordCount As Long
dbConnectStr = "Provider=msra;Data Source=" & "Oracle_Database_Name;"

User Id=userId" & "; Password=" & "password"
'The statement above has been commented out. I use the statement below to prompt the user for the userId and password - which is what I prefer!

con.ConnectionString = dbConnectStr
con.Properties("Prompt") = adPromptAlways
con.Open dbConnectStr 'ConnectionString

'This is an example SQL code that you might want to run
'Select * From MyTable

'SQL_String = "Select count(*) from adm_user"
recset.Open SQL_String, con
recset.MoveLast
recordCount = recset.recordCount
recset.MoveFirst
Do While Not recset.EOF = True
'Have a loop here to go through all the fields
recset.MoveNext
Loop
recset.Close
End Sub