Oracle 817 Odbc Driver
Discussions
CHOOSE A TOPIC
You re now being signed in.
Solve problems - It s Free
Create your account in seconds
E-mail address is taken
Between 5 and 30 characters. No spaces please
The Profile Name is already in use
Password
Notify me of new activity in this group:
Real Time
Keep me informed of the latest:
White Papers
Newsletter
Jobs
By clicking Join Now, you agree to Toolbox for Technology terms of use, and have read and understand our privacy policy.
Excel VBA to run Oracle query on computer w/o Oracle
Goodbye asked Sep 24, 2008 Replies 18
Is this even possible.
I have an Excel file which is used to generate reports. When it is opened, manually or via schedualled tasks, it runs an Oracle query through VBA and creates a new Excel report.
I ve moved this file online so the new reports are availble to view in an online folder. I would like to add the function than any user viewing the online folder can open the template file, thus creating an up-to-the-minute report.
Currently this would require that they have Oracle installed, otherwise they get an error. Is there anyway to do this.
Here s the code I m using:
Const HOST db-vipxxx ServerName Server hosting the Oracle db
Const DBNAME Rsvaxxx DatabaseName
strConOracle Driver Microsoft ODBC for Oracle ; CONNECTSTRING DESCRIPTION ADDRESS PROTOCOL TCP
strConOracle strConOracle HOST HOST PORT 41521 CONNECT_DATA SERVICE_NAME DBNAME
strConOracle strConOracle ; uid ORACLE_USER_NAME ;pwd ORACLE_PASSWORD ;
Set oConOracle CreateObject ADODB.Connection
Set oConOracle Nothing
End Sub
If you have any suggestions I m all ears, but if not, I guess I ve got to get Oracle for all my users.
Mark this reply as the best answer.
Choose carefully, this can t be changed
Oops1 replied Sep 24, 2008
Do they have Data Sourse Name created on their machines. Each PC needs
DSN. Installing Oracle for each user would be too much:-
Goodbye replied Sep 24, 2008
Just to make sure we re on the same page
DSN, this is what I set-up when I made the original connection to the database right.
Control Panel, Admin tools, Microsoft ODBC Administrator
System DSN tab
and then select the driver right.
In this case: Oracle in ORA92_HOME
Wouldn t they need Oracle instlled to have access to that right driver.
Or am I not following you correctly.
Sorry, I m not very experienced with this.
Well, I ve made some progress, but I m stuck again.
My IT set up a website which connects direct to the Oracle database.
Any user can go to the site, enter a query, and get the results. They do not need any ODBC or even Oracle to do this.
I want my Excel VBA to do this..
My IT guy laughed at me. It s not gonna happen unless I make it happen. Well, really it s not gonna happen unless I get some serious help.
I ve made an ad-hoc solution for now, basically VBA naviagtes to the site, enters the query, and copies the results back into Excel.
It worksbut it s not really very efficient or practical. Plus IT will not support the site forever.
If I get the details from him on how the website works, can I design an Excel/VBA file that operates the same way.
Thank you..
Kevin Barnes replied Sep 24, 2008
Have you ever connected to a database before using excel VBA.
Saving
Ever. well yes, the VBA code shown in my first post works just fine, but only beacuse I have Oracle and the ODBC setup on my PC.
I want to create a way to bypass the ODBC and Oracle driver setup requirements though, as not all users will have that.
The web site my IT built does just that, and I want to see if I can put the same functionality into an shared Excel file which is saved online.
Michael Meyers-Jouan
replied Sep 24, 2008
You are using ADO in your VBA code. With ADO, you are defining the DSN in line rather than using the ODBC applet.
Because the Microsoft Oracle ODBC driver is distributed as a standard component on Windows XP and Windows Vista, your users should be able to use your Excel workbook without installing anything else, as long as they have access to the server that is running the Oracle database.
However, you should probably investigate the use of ASP.Net code to build a Web site with the functionality you now have in the Excel workbook. If you can implement that, your users don t need access to ANYTHING except the Web site. Your IT guy should be willing to support the ASP.NET web pages as part of the corporate web site.
Michael S. Meyers-Jouan
Ah, you are talking about a driver. Sorry - I did not understand what
was Oracle :-
Yes, you need it. I think it s part of Windows standard set up. At least
my machine has it.
There was a way get around without DSN: pass all the parameters in
connect string. But you cannot do it without ODBS driver for Oracle
which is not an Oracle :-
Please google DSN-less connection
Goodbye replied Sep 25, 2008
Oops1:
Ok, I am experimenting with the DSN-Less connection. but I can t get it quite right.
I found a few examples, but I can t get it to connect.
Example:
CN.ConnectionString Provider OraOLEDB.Oracle.1;Password 4SBariRO ;Pers ist Security Info True;User ID t847712;Data Source LSRPRsva
CN.Open Open connection
I get a Provider cannot be found, it may not be properly installed error here
Why is there no room in this example for me to input the server info, or the port info.
Michael:
I ll start looking into ASP.Net, but my IT is really not going to do anything else from here on in, so it s up to me.
I m thinking that developing supporting a new web page is more than I want to do.
I already have a web folder which stores Excel reports for users to view. Each morning an Excel file on my hard drive connects to the Oracle database and refreshes the data, and uploads a new report to the online folder.
I would like my users to be able to generate a new report mid-day if needed. So I uploaded an Excel file containing a VBA macro which connects to the database and saves a new report for them to the online folder. This macro will only work for them IF they have Oracle installed on their computer.
In an attempt to bypass the need for Oracle on all computers, I have modified the VBA to open a new website - which is connected to the Oracle database - run the query there, and copy the results back into the Excel file, and this gives them the new report.
I am concerned that this website I m using to run the query will not be running for the long term. And I am positive that they will not develop or support an ASP.Net page.
Do I understand you correctly that I can modify my VBA to connect using the ODBC applet, and this would then work for any user with access to the server which the Oracle database is on.
If sohow.
- Oracle Technology Network. Welcome to the world s largest community of developers, admins, and architects using industry-standard technologies.
- How to connect SqlServer through oracle using generic connectivity. what changes require in ORacle_home hs folder. I mean in inithsodbc,listener,tnsnames after i.
- You are using ADO in your VBA code. With ADO, you are defining the DSN in line rather than using the ODBC applet. Because the Microsoft Oracle ODBC driver is.
- 4. Cursors Implicit in the execution of an SQL query via ODBC is the opening of a cursor. The cursor tells ODBC where the first row of the result set is.
- Hi Tom What s the difference between connections, sessions and processes. I read a note from Metalink about the difference but I simply dont get it.
Listado de ejemplos. Lista todos los ejemplos del manual. Example 0 - Un ejemplo introductorio; Example 1 - Nuestro primer script de PHP: hola.php; Example 2.
Welcome to the world s largest community of developers,
admins, and architects using industry-standard technologies
in combination with Oracle products.
Win7 安装 mirror driver 的搜索结果. 相关的搜索 mirror driver 监控流 win8安装sql2008安装报错:试图执行未经授权的操作.
Apr 08, 2015 오픈소스컨설팅 Java Performance Tuning 1. Java Performance Tuning Certified Partner by 2. 2 - Internal Use Only - 회사 소개 오픈소스.
