Excel VBA Executing Access Query that Links to Oracle

Question

Let me start of by saying I am new to both this site as well as VBA so please bear with me. Thank you in advance for your help.

I have a VBA function that runs an existing query from Access. Some of the tables being queried are stored in an Oracle database that require a user specific password access. Right now, a sub that I wrote to automate a report calls this function 7 times and requires the user to input their Oracle password each time the function is called (it also stops the sub and gives an error message if they type in the password incorrectly which I see as a likely event if they need to do it 7 times). The code works but I would like to find a way to have the code ask for the password once and be done with it. All of the solutions I have found involve connecting to and querying Oracle directly which requires very complicated SQL coding that I am by no means capable of writing.

I am also having an issue where the columns show up in the excel sheet in a different order than they do in Access for some of the queries. This seems to be consistent so it isn't to big of a problem but I would like to know how to prevent this to prevent any future issues.

Here is the code for the function I am currently using. Any insight would be greatly appreciated!

Option Explicit

'Single Argument "qryName" as string. Runs access qry and copys recordset to active sheet.
Function AccessQueryPull(qryName As String)

'Declare variables
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

'open the connection to the access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=filePath.accdb;"

'format the command to run the query
Dim cmd As New ADODB.Command
cmd.CommandType = adCmdStoredProc
cmd.CommandText = qryName 
cmd.ActiveConnection = cn

'execute the query
Set rs = New ADODB.Recordset
Set rs = cmd.Execute()

'copy data to excel sheet
ActiveSheet.Cells(2, 1).CopyFromRecordset rs

'Cleanup
rs.Close
Set rs = Nothing

Why are you calling the function seven times? And please show the sub that calls this function. — Parfait
– Parfait, Commented Oct 31, 2016 at 23:46
I am calling the function 7 times in the sub because there are separate historical and forward looking queries already set up in access for each of our plants as well a query that helps determine the cutoff date for the forward looking queries based on plant schedules. The sub is simple. It just sets variables = to qry names, clears existing data, performs a few calculations, formats charts etc. Are there ways to allow an undefined number of queries to be run with this function so that it may only be called once? — Andrew L.
– Andrew L., Commented Nov 1, 2016 at 13:39

Parfait · Accepted Answer · 2016-11-01 00:04:16Z

1

The reason you are prompted each time for Oracle credentials is that you create a fresh, new connection to MS Access each time the function is called.

Simply persist the MS Access connection by connecting once in the Sub that calls function and pass connection object as parameter. In this way, any connection error is caught in the parent routine. As for column order simply declare the columns in the needed order in SQL statement.

Sub RunQueries()
   Dim rs As ADODB.Recordset
   Dim cn As ADODB.Connection

   'open the connection to the Access database
   Set cn = New ADODB.Connection
   cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=filePath.accdb;"

   Call AccessQueryPull(cn, "SELECT Col1, Col2, Col3 FROM Qry1")   ' AND OTHER 6 CALLS

   cn.Close
   Set cn = Nothing    
End Sub

Function AccessQueryPull(accConn As ADODB.Connection, qryName As String)    
    'format the command to run the query
    Dim cmd As New ADODB.Command
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = qryName 
    cmd.ActiveConnection = accConn

    'execute the query
    Set rs = New ADODB.Recordset
    Set rs = cmd.Execute()

    'copy data to excel sheet
    ActiveSheet.Cells(2, 1).CopyFromRecordset rs

    'Cleanup
    rs.Close
    Set rs = Nothing: Set cmd = Nothing
End Function

answered Nov 1, 2016 at 0:04

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew L. Over a year ago

Thank you, that worked for the credentials problem. I did not try your solution for the column order as the SQL is very complicated for the queries I am running so I'm just letting access handle that. I figured out a way to pull column headers along with the data which serves my purposes just fine! Thanks for the help.

Collectives™ on Stack Overflow

Excel VBA Executing Access Query that Links to Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related