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.

newbiewebSr. Software EngineerAsked:
Who is Participating?
khairilConnect With a Mentor Commented:

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.
newbiewebSr. Software EngineerAuthor Commented:
Thanks for the feedback.

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

FROM tableName
WHERE YesNoValue='Y'

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.

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.

All Courses

From novice to tech pro — start learning today.