[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Executing a simply query against an Oracle database, using VBA

Posted on 2011-09-07
2
Medium Priority
?
445 Views
Last Modified: 2012-05-12
I need to use VBA to populate a Worksheet with the results of a simply query against a table.

Do I need a stored procedure to return the values?

Besides a connection string to access the Oracle Database, what else do I need?

I have the SQL tested in SQL Tools.

Please let me know.

Thanks.
0
Comment
Question by:newbieweb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 13

Accepted Solution

by:
khairil earned 2000 total points
ID: 36498686
Hi,

You had to have ODBC connector to connect your VBA to Oracle using the connection string you supplied. Having sproc is the best in terms of performance and programming flexibility but it is not a must. Simple SELECT statement can also return you value.

Other thing you need to consider is the SQL dialect, not all the MSSQL query can runs on Oracle and vice versa, test you connection and query first. You can trace your VBA code in debugging mode. Over here you can use Watch to see the value inside the return variable or recordset.

Firewall is the next issue, both on the cleint computer and the server.

Also make sure client computer can access the server and have the right credential on connection string. Oracle do support digest authentication but my suggestion for you to have Oracle own user credential instead of using Windows creadential.
0
 

Author Closing Comment

by:newbieweb
ID: 36498793
Thanks for the feedback.

I have a simple Select, so SQL dialect is not an issue.

SELECT *
FROM tableName
WHERE YesNoValue='Y'
ORDER BY DESCRIPTION DESC

Yes, I now use breakpoints in my code, thanks.

I have an ODBC connection to the database, and the test succeeds.

I have SQL Tools successfully executing a query and getting back real data, so the firewall will not be an issue.

I need to know where to put the code, but I guess I can stick it anywhere for test purposes.

Thanks!
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Via a live example, show how to take different types of Oracle backups using RMAN.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question