Solved

Executing a simply query against an Oracle database, using VBA

Posted on 2011-09-07
2
440 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 500 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
minium over 4 numeric columns for each row in oracle 2 37
sql script with strange password string 1 45
Password_rules_securitty.. 12 34
Dynamic Table mySQL stored procedure 5 37
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

733 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