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?

I don't work with access but I can make it out that you are latebinding with access. In such a case have you declared the ACCESS Constants? Const acOutputQuery As Long = 1 and Const acExportQualityPrint as Long = 0 — Siddharth Rout
– Siddharth Rout, Commented Oct 29, 2013 at 18:37
I have not, didn't even know to do that. Why would I need to do those things? Just for my own education.... I did find a work-around that was successful. I decided to string these queries together inside an Access macro and run it using acApp.DoCmd.RunMacro. That succeeded in doing everything I really wanted, so it was a different direction I took but achieved the right end. — Nate
– Nate, Commented Oct 29, 2013 at 18:39
acOutputQuery and acExportQualityPrint are ACCESS Constants. Excel will not understand them. Put the code that I gave in the comment above at the very top of your code. — Siddharth Rout
– Siddharth Rout, Commented Oct 29, 2013 at 18:41

HansUp · Accepted Answer · 2013-10-29 19:24:13Z

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

Collectives™ on Stack Overflow

Excel VBA to Run and Export Access Queries

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related