Solved

Executing a simply query against an Oracle database, using VBA

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

717 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