Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Posts
    65

    Unanswered: Microsoft Access instead of Oracle

    Hello to all.
    AT my company we use an Oracle database. There was the need of creating a few Excel reports containing data from the Oracle database. I was able to achieve that by connection to the database when opening the workbook and writing a few User Defined Functions that retrieve the data from the database into the worksheets cells. This is the way I do it.
    When starting Excel:

    HTML Code:
    Private Sub Workbook_Open()
    If ConnectStat Then
        OraDatabase.Close
        ConnectStat = False
    End If
        Set OraSession = CreateObject("OracleInProcServer.XOraSession")
        Set OraDatabase = OraSession.OpenDatabase("PRODL", "apps/apps", 0&)
        ConnectStat = True
        executar
        Exit Sub
    End Sub
    On the executar Sub I have the database queries (including parameters):

    HTML Code:
    Public Sub executar()
    If ConnectStat Then
    Dim Qry6 As String
        Qry6 = "BEGIN SELECT max(l.description) INTO :desc_deb FROM gl_je_lines l, gl_code_combinations cc" _
        & " WHERE cc.code_combination_id = l.code_combination_id" _
        & " AND l.accounted_dr = :debito AND cc.code_combination_id = 127123 AND " _
        & " l.set_of_books_id = 53 AND NOT EXISTS" _
        & " (SELECT 1 FROM ce_statement_reconcils_all WHERE reference_id = l.je_line_num" _
        & " and l.accounted_dr = amount AND current_record_flag = 'Y'); end;"
        OraDatabase.Parameters.Add "debito", 0, ORAPARM_INPUT
        OraDatabase.Parameters.Add "desc_deb", 0, ORAPARM_OUTPUT
        Set DescDebSet = OraDatabase.CreateSql(qry1, ORASQL_FAILEXEC)
    End if
    End Sub
    Finally, I have the User Defined Function which only refresh the data obtained on the queries:

    HTML Code:
    Public Function Descr_Deb(ValorDeb As String) As Variant
        If ConnectStat Then
            OraDatabase.Parameters("debito").Value = ValorDeb
            DescDebSet.Refresh
                If DescDebSet.RecordCount = 0 Then
                Descr_Deb = "Not Found"
            Else
                If IsEmpty(OraDatabase.Parameters("desc_deb").Value) Or OraDatabase.Parameters("desc_deb").Value = "" Then
                    Descr_Deb = 0
                Else
                    Descr_Deb = OraDatabase.Parameters("desc_deb")
                End If
            End If
        Else
            Descr_Deb = "No/connection"
        End If
    End Function
    The Oracle tool used is OO4O (Oracle Objects for OLE)
    My question is: Does anyone know if there is a chance to develop something similar to achieve the same result but using Microsoft Access and VBA (ADO)?
    Thanks in advance for any kind help.
    Octavio

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    because although slightly different Excel & Access can both use VBA. so you coudlk use Access as a bridge between your Oracle DB and the Excel spreadsheets. however you need to give some thought about what you are proposing.

    its quite common to see data ported down from a corporate server into Access for onward analysis or reporting eslewhere. but you do need to make certain that the original data is alwasy the server NOT the Access db. so users shouldnt' be adding 'stuff' to ther Acess DB that shgoudl really be in the server db
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2007
    Posts
    65

    Unhappy

    Thank you for answering.
    My question is that i need to build the posted code to work between Excel and an Access 2007 database. Oracle is out of the way. There are no UPDATE statements, only SELECT statements.Each one of my queries allways return a single value which is stored in spreadsheet cell.
    I tried to build the code using recordsets but i'm stucked on the parameters values and on the recordset population...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •