modOracle.bas

Sub ConnectToOracle()
    Dim conn As Object
    Dim cmd As Object
    Dim rs As Object
    Dim connString As String
    Dim sqlQuery As String
    Dim paramValue As String
    
    ' Set connection string
    connString = "Provider=MSDAORA;Data Source=YourOracleDB;User ID=YourUsername;Password=YourPassword;"
    
    ' Initialize connection object
    Set conn = CreateObject("ADODB.Connection")
    
    ' Open the connection
    conn.Open connString
    
    ' Check if connection is successful
    If conn.State = 1 Then
        MsgBox "Connection successful!"
        
        ' Prepare SQL query with parameter
        paramValue = "example_parameter_value"
        sqlQuery = "SELECT * FROM YourTable WHERE ColumnName = ?"
        
        ' Initialize command object
        Set cmd = CreateObject("ADODB.Command")
        With cmd
            .ActiveConnection = conn
            .CommandText = sqlQuery
            .CommandType = adCmdText
            
            ' Add parameter to the command
            .Parameters.Append .CreateParameter("paramName", adVarChar, adParamInput, Len(paramValue), paramValue)
        End With
        
        ' Execute the query
        Set rs = cmd.Execute
        
        ' Check if records are returned
        If Not rs.EOF Then
            Do While Not rs.EOF
                ' Access and process data here
                ' Example: MsgBox rs.Fields("ColumnName").Value
                
                ' Move to the next record
                rs.MoveNext
            Loop
        Else
            MsgBox "No records found."
        End If
        
        ' Close recordset
        rs.Close
        
        ' Clean up objects
        Set rs = Nothing
        Set cmd = Nothing
        
        ' Close the connection
        conn.Close
    Else
        MsgBox "Connection failed."
    End If
    
    ' Clean up connection object
    Set conn = Nothing
End Sub