Excel VBA выполнение запроса доступа, который ссылается на Oracle

Позвольте мне начать с того, что я новичок как на этом сайте, так и на VBA, поэтому, пожалуйста, потерпите со мной. Заранее спасибо за помощь.

У меня есть функция VBA, которая выполняет существующий запрос из Access. Некоторые из запрашиваемых таблиц хранятся в базе данных Oracle, для которых требуется пароль пользователя. Прямо сейчас, sub, который я написал, чтобы автоматизировать отчет, вызывает эту функцию 7 раз и требует, чтобы пользователь вводил свой пароль Oracle каждый раз, когда функция вызывается (он также останавливает sub и выдает сообщение об ошибке, если они вводят пароль неправильно, который я вижу как вероятное событие, если им нужно сделать это 7 раз). Код работает, но я хотел бы найти способ, чтобы код попросил пароль один раз и сделать с ним. Все решения, которые я нашел, включают в себя подключение и запрос Oracle напрямую, который требует очень сложного SQL-кодирования, которое я никоим образом не способен писать.

У меня также возникает проблема, когда столбцы отображаются на листе excel в другом порядке, чем в Access для некоторых запросов. Это кажется последовательным, поэтому это не большая проблема, но я хотел бы знать, как предотвратить это, чтобы предотвратить любые будущие проблемы.

Вот код функции, которую я сейчас использую. Любая проницательность была бы очень оценена!

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

1 ответ

  1. Причина, по которой каждый раз запрашиваются учетные данные Oracle, заключается в том, что при каждом вызове функции создается новое подключение к MS Access.

    Просто сохраняйте соединение MS Access, подключившись один раз в Sub, который вызывает функцию и передает объект подключения в качестве параметра. Таким образом, любая ошибка подключения фиксируется в родительской подпрограмме. Что касается порядка столбцов, просто объявите столбцы в нужном порядке в инструкции SQL.

    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