• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 451
  • Last Modified:

Executing a simply query against an Oracle database, using VBA

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
newbieweb
Asked:
newbieweb
1 Solution
 
khairilCommented:
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
 
newbiewebSr. Software EngineerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now