Connect to and operate Oracle using Excel VBA

Source: Internet
Author: User

The following is an example of using Excel VBA to connect to Oracle and operate Oracle-related data.
To connect to the database through VBA, Excel needs to install the corresponding Oracle client tool and reference the relevant components of ADO. to reference the components of ADO, follow these steps:
1. Open the VBA editor and click "Tools" and "Reference" in the menu ";
2. Make sure that "Microsoft ActiviteX Data Objects 2.8 Library" and "Microsoft ActiviteX Data ObjectS Recordset 2.8 Library" are checked.
The code for establishing a connection is as follows:
Copy codeThe Code is as follows:
Public Sub ConOra ()
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
Dim SQLRst As String
Dim OraOpen As Boolean
OraOpen = False
OraID = "Orcl" 'oracle Database Configuration
OraUsr = "user"
OraPwd = "password"
ConnStr = "Provider = MSDAORA.1; Password =" & OraPwd &_
"; User ID =" & OraUsr &_
"; Data Source =" & OraID &_
"; Persist Security Info = True"
ConnDB. CursorLocation = adUseServer
ConnDB. Open ConnStr
OraOpen = true' after successful execution, the database is opened
'Msgbox "Connect to the oracle database Successful! ", VbInformation," Connect Successful"
DBRst. ActiveConnection = ConnDB
DBRst. CursorLocation = adUseServer
DBRst. LockType = adLockBatchOptimistic
SQLRst = "Select * From TstTab"
DBRst. Open SQLRst, ConnDB, adOpenStatic, adLockBatchOptimistic
DBRst. MoveFirst
Exit Function
ErrMsg:
OraOpen = False
MsgBox "Connect to the oracle database fail, please check! ", VbCritical," Connect fail! "
End Function
Public Sub ConOra ()
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
Dim SQLRst As String
Dim OraOpen As Boolean
OraOpen = False
OraID = "Orcl" 'oracle Database Configuration
OraUsr = "user"
OraPwd = "password"
ConnStr = "Provider = MSDAORA.1; Password =" & OraPwd &_
"; User ID =" & OraUsr &_
"; Data Source =" & OraID &_
"; Persist Security Info = True"
ConnDB. CursorLocation = adUseServer
ConnDB. Open ConnStr
OraOpen = true' after successful execution, the database is opened
'Msgbox "Connect to the oracle database Successful! ", VbInformation," Connect Successful"
DBRst. ActiveConnection = ConnDB
DBRst. CursorLocation = adUseServer
DBRst. LockType = adLockBatchOptimistic
SQLRst = "Select * From TstTab"
DBRst. Open SQLRst, ConnDB, adOpenStatic, adLockBatchOptimistic
DBRst. MoveFirst
Exit Function
ErrMsg:
OraOpen = False
MsgBox "Connect to the oracle database fail, please check! ", VbCritical," Connect fail! "
End Function

You can adjust the SQL statement as needed to obtain relevant data and output it to Excel for data processing.
The above code is tested and passed in Windows XP SP3/2003 SP2 + Office2003.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.