Excel VBA to Run and Export Access Queries

Question

I have the following code that kicks off a series of queries in an Access database from Excel. When these queries are run by themselves in Access, they work fine and succeed in generating the right file, but when I convert the macros to run in Excel using a button click, I run into some problems. See my code below:

Sub AccessImport()

Dim acApp As Object
Dim MyDatabase As String
Dim question As String

question = MsgBox(Prompt:="Are you sure you want to complete this action?  Running this process is lengthy and could take a couple minutes to complete.", Buttons:=vbYesNo, Title:="Run SOD Matrix")

If question = vbYes Then

MyDatabase = "directory string"
OutputFile = "output string"

    'open the database and apend the combination table to existing
    Set acApp = CreateObject("Access.Application")
        acApp.OpenCurrentDatabase (MyDatabase)
        acApp.Visible = True
        acApp.UserControl = True
        acApp.DoCmd.SetWarnings False
        acApp.DoCmd.OpenQuery "QRYDELETE_PS_ROLE_NAMES", acViewNormal, acEdit
        acApp.DoCmd.OpenQuery "QRYDELETE_PS_ROLE_USER", acViewNormal, acEdit
        acApp.DoCmd.OpenQuery "QRYDELETE_SOD_TBL", acViewNormal, acEdit
        acApp.DoCmd.OpenQuery "QRYAPPEND_PS_ROLE_NAMES", acViewNormal, acEdit
        acApp.DoCmd.OpenQuery "QRYAPPEND_PS_ROLE_USER", acViewNormal, acEdit
        acApp.DoCmd.OpenQuery "QRYAPPEND_SOD_TBL", acViewNormal, acEdit
        'acApp.DoCmd.OpenQuery "QRY_HIGH", acViewNormal, acEdit
        acApp.DoCmd.OutputTo acOutputQuery, "QRY_HIGH", "ExcelWorkbook(*.xlsx)", OutputFile, _
            False, "", , acExportQualityPrint
        acApp.DoCmd.SetWarnings True
        acApp.CloseCurrentDatabase
        acApp.Quit
    Set acApp = Nothing

Else
MsgBox ("Process has been cancelled.")
    Exit Sub
End If
MsgBox ("Process has completed successfully.")
End Sub

For the last query, meant to export and save the output, I run into an error telling me Property is not found. I've tried changing the DoCmd to TransferSpreadsheet, the format type of acFormalXLS to prevent conversion issues, but I still cannot get it to complete successfully. Do I need to put this code in a module instead of keeping it on the sheet itself? Thoughts/help?

Answer 1

Siddharth identified the issue with this statement:

acApp.DoCmd.OutputTo acOutputQuery, "QRY_HIGH", "ExcelWorkbook(*.xlsx)", OutputFile, _
    False, "", , acExportQualityPrint

Without a reference to the Access object library, Excel will know nothing about the Access constants acOutputQuery and acExportQualityPrint.

You would be wise to add Option Explict to the module's Declarations section and then run Debug->Compile from the VB Editor's main menu. When you do that, I suspect you will discover similar problems with the lines such as this ...

acApp.DoCmd.OpenQuery "QRYDELETE_PS_ROLE_NAMES", acViewNormal, acEdit

Excel will also know nothing about the Access constants acViewNormal and acEdit. However, if your intent was to execute "action" queries (INSERT, UPDATE, DELETE, etc), it's good that those constants are unrecognized. Otherwise you would be opening those queries in Design View instead of executing them.

Consider a different approach ...

Const dbFailOnError As Long = 128
'acApp.DoCmd.SetWarnings False ' leave SetWarnings on!
acApp.CurrentDb.Execute "QRYDELETE_PS_ROLE_NAMES", dbFailOnError
acApp.CurrentDb.Execute "QRYDELETE_PS_ROLE_USER", dbFailOnError