Connect to APEX database using VBA+ADO code in Microsoft Excel & Word?

Advertisement

I am looking at APEX as a potential replacement for a growing reporting/analysis system. As yet I have not found a clear answer on an important question.
The current system is a shared database and a collection of Microsoft Excel and Word templates. Each template contains underlying VBA code that establishes a direct connection to the database via OLE DB (i.e. ADODB objects in VBA via the OLE DB Provider for JET).
Each template’s underlying VBA code executes stored procedures or queries as required, the results of which are returned as Recordset objects and rendered to the Excel worksheet or Word document. Similarly, in some cases, data entered into an Excel worksheet by a user can be submitted back to the database at the click of a button, again via Recordset objects.
Now most of our templates could easily be ported to APEX. However, there is a strong preference that some of our templates continue to exist and maintain current functionality. So we have a requirement that Excel and Word can seamlessly and directly interact with an APEX database.
Long question short – is there a way to establish a connection and retrieve/insert data from an APEX database using OLE DB (ADODB) in VBA, as described above? I understand that APEX sits ‘on top’ of a real Oracle database, and I know Oracle provides an OLE DB Provider (OraOLEDB) for Oracle databases. Would OraOLEDB do the trick, or is it not as simple as that?
Many thanks for your insights on this,
Michal

Advertisement

The current system is a shared database and a collection of Microsoft Excel and Word templates. Each template contains underlying VBA code that establishes a direct connection to the database via OLE DB (i.e. ADODB objects in VBA via the OLE DB Provider for JET).
Long question short – is there a way to establish a connection and retrieve/insert data from an APEX database using OLE DB (ADODB) in VBA, as described above? I understand that APEX sits ‘on top’ of a real Oracle database, and I know Oracle provides an OLE DB Provider (OraOLEDB) for Oracle databases. Would OraOLEDB do the trick, or is it not as simple as that?Hi,
Apex gets installed as a separate schema in an existing database.
So it will not in any way interfere with your existing setup. You can continue to connect directly to the database from your spreadsheets, call stored procedures from VBA/ADO, etc.
- Morten
http://ora-00001.blogspot.com

November 30, -0001

Connect to APEX database using VBA+ADO code in Microsoft Excel & Word?

I am looking at APEX as a potential replacement for a growing reporting/analysis system. As yet I have not found a clear answer on an important question. The current system is a shared database and a collection of Microsoft Excel and Word templates.

How to connect to oracle10g Database using oracle11g Enterprise Manager.

Hi, please suggest me how to connect to oracle10g Database using oracle11g Enterprise Manager. i did setting in tnsnames.ora file of oracle11g database but when i am trying to connect [email protected] and password using oracle11g Enterprise manager its no

Connecting to SQL database using IP address

Hi I'm writing some SQL code to connect to a database on another site. I have been given - Server IP addres Database name User ID Using the database connectivity tools it seems like I have to use a dsn to connect to the database - is it possible to d

Connecting to MSAcess database using JDBC in JSP

Hi, I have created an Aceess database file (C:\db1.mdb) and created a DSN=db1. I could able to connect to the database and retrieve the information from the tables using Sun's JDBC OdBc bridge drivers. DriverManager.getConnection("jdbc:odbc:db1"

Connect applet to database using JDBC

I try to connect Applet to database, but have error: Access denied (Java.lang.Runtime.Permission access Class In paclage.sun.jdbc.odbc) I think the reason is the security issue, but how to implement the security issue in the code? Help me...If you ar

ORA-12514 when trying to connect to the database using SERVICE

Hi, Version 10204 In a single instance database i would like to create new SERVICEs in order to use them later with Resource Manager. The instance name is DWH. I run the following commands: I added to the tnsnames.ora file the following input: XYZ =

Problem connecting to XE database using Forms Developer 10.1.2.0.2

Hi I installed Oracle XE successfully and then Oracle developer 10.1.2.0.2. I can't connect to the xe database I guess because the tnsnames.ora doesn't have the correct entry for this connection. Can anyone help me with creating the correct tnsnames.

Connectivity with  oracle database using web Dynpro(for java)

Hi, In web Dynpro we will connect to SAP systems by using Adaptive RFC.  Then How to connect the oracle 8i/9i using webdynpro? Plese tell me the procedure to establish the connecting using enterprise portal? Thanks & Regards, Mastanvali ShaikHi, Chec

Error in connecting to mysql database using jsp

Hi I've encountered this error: java.lang.ClassNotFoundException: org.gjt.mm.mysql.Driver while trying to connect to MySQL db. I've downloaded the driver from www.mysql.com, and set the classpath as: "C:\mm.mysql.jdbc-1.2b;%CLASSPATH%;" But it j

Cannot connect to Database using JavaWebServer

Hello, I am trying to connect to the database using the javawebserver2.0 but when executed i get the following exception can any please tell when what cud be the possible problem Exception is: 500 Internal Server Error The servlet named TxValidateUse

Cannot connect local database using net service_name

Good Morning to all ; FYI : This  question wrongly posted under high availability. Now it was removed from there. I am trying to connect my local database  using remote authentication. but getting error. Client & Server reside on same  server. SQL>

Help with connection to mysql database, Getting error : No suitable Driver

Below is my code : 1) i have added the mysql driver (mm.mysql-2.0.14-bin.jar)in my classpath 2) the code is getting compiling properly 3) while running it using tomcat, it says "NO SUITABLE DRIVER" PLEASE HELP !! import java.io.*; import java.te

Yesterday I was able to connect Azure SQL Database - Today I am not

Hello Everyone, I have problem with establishing connection with Azure SQL Database. I have read many articles here, tried some solutions but none worked. 0. On last Friday everything worked well - today it does not. 1. I added my public IP Address i

Access to database using ColdFusion & Flex

Hi, I install a ColdFusion MX 7 Updater 2 in my ColdFusion Server but don't work it.... my server has a Windows Server 2003 Enterprise and we are developing an aplication in Flex 2 and we are using ColdFusion for connect to data base but Flex 2 need

Connection String example required using ODAC 10.2

Hi All, Apologies if this has already been covered... I have a .net app running on W2K3 SP2 x86. It uses an Oracle 10G (10.2.0.4) database backend running on Redhat Linux x86_64. I have two oracle client homes on the app server; one for full admin (1

Problem accessing informix ODBC 32 bit System DSN using Microsoft Query external data sources in Microsoft Excel 2010

Hi all... I'm hoping if anybody could help me. I'm using Microsoft Excel 2010 + Windows 7 Professional 64 bit. I've installed the informix client sdk for odbc drivers to create odbc 32 bit connection. Before this, i had successfully installed in anot

How to connect oracle database using DSN  from jsp

hello, can any know how can i connect to[b] oracle database using DSN name from jsp .I am using oracle 9i and Tomcat 5 Using odbc tool i have created the dsn name but ithe connection does not make .here is the code that i have tried Connection connec

Failed to open the connection. Details: [Database Vendor Code: 53 ]

I'm upgrading one of our applications from Visual Studio 2005 to 2010. In the process I'm also switching our reports to run using Stored Procedures instead of Tables/Views.  Everything runs fine on development machines after all the migration work. 

Moving apex database to production instance using datapump

Currently the apex database resides on a windows server and we would like to move it to our production server (unix). I don't want to create a new database for apex on the unix server but would prefer to import the apex tablespaces and users into the

Failed to open the connection. Details: [Database Vendor Code: 12154].

I have installed Crystal reports server 2008 VI on a Windows 2008 64bit OS. I have added SQL server and Oracle reports through CMC. I then installed Oracle 10g client 32bit on the server and created and tested the required DSNs using ODBC 32bit. All