06-19-12, 07:37 #1Registered User
- Join Date
- Jun 2007
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:
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
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
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
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.
06-19-12, 07:55 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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 dbI'd rather be riding on the Tiger 800 or the Norton
06-19-12, 08:03 #3Registered User
- Join Date
- Jun 2007
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...