Running macro in Access from Excel, but Access doesn't close after running

Question

I am opening Microsoft Access from Excel VBA and running a macro, and then running more code in Excel. Everything worked fine a number of days ago, but now I'm having two problems. First Access doesn't close after running the macro, so Excel times out (It gets a message box that pops up and says Microsoft Excel is waiting for another application to complete an OLE action). That is actually the other problem, because when it times out I have to interact with Excel again to make the macro in Excel continue running. I'm trying to not have to touch anything after I first start my macro in Excel until it is done. And I think it might also be timing out because I added more steps onto my macro in Access so it takes longer...

First, here is the Excel code that calls Access:

Sub Opendatabase()

Dim accessApp
Set accessApp = CreateObject("Access.Application")
accessApp.Visible = True
accessApp.OpenCurrentDataBase ("I:\MyPath\MyFile.accdb")
accessApp.Run "ImportDataRunQueriesExportData"


'Run the next part of Excel macro
Application.Run "PullinTheExportedAccessData"

End Sub

And then in Access, it does this (sorry it is so long). The purpose it to pull in data, run queries (Which I do by opening and closing them, I'm not sure if there is a faster way) and then exporting data. I want it to close at the end, too, and right now that part doesn't work.

Sub ImportDataRunQueriesExportData()

DoCmd.RunSavedImportExport "Import-ERT Aggregate Macro"

'Open master book January to refresh

 DoCmd.SetWarnings False
 DoCmd.OpenQuery ("Remove HP not Area8")
 DoCmd.OpenQuery ("Remove Non Area 8 HP")
 DoCmd.SetWarnings True
DoCmd.OpenTable ("Remove HPnonArea8")
DoCmd.Close acTable, "Remove HPnonArea8"
DoCmd.OpenQuery ("Summary For January")

'Open Duplicate search queries across all c
DoCmd.OpenQuery ("S+D")
DoCmd.OpenQuery ("S+D+L")
DoCmd.OpenQuery ("S+D+L+F")
DoCmd.OpenQuery ("S+D+L+F+M")
'Open Duplicate search queries within c
DoCmd.OpenQuery ("C+S+D")
DoCmd.OpenQuery ("C+S+D+L")
DoCmd.OpenQuery ("C+S+D+L+F")
DoCmd.OpenQuery ("C+S+D+L+F+M")


'Save and close queries, made with create Macro function
    DoCmd.Close acQuery, "Summary For January"
    DoCmd.Close acQuery, "S+D"
    DoCmd.Close acQuery, "S+D+L"
    DoCmd.Close acQuery, "S+D+L+F"
    DoCmd.Close acQuery, "S+D+L+F+M"
    DoCmd.Close acQuery, "C+S+D"
    DoCmd.Close acQuery, "C+S+D+L"
    DoCmd.Close acQuery, "C+S+D+L+F"
    DoCmd.Close acQuery, "C+S+D+L+F+M"

'Open Summary Queries
DoCmd.OpenQuery ("New January")
DoCmd.OpenQuery ("Renew January")
DoCmd.OpenQuery ("Count October")
DoCmd.OpenQuery ("Count November")
DoCmd.OpenQuery ("Count December")
DoCmd.OpenQuery ("5B")
DoCmd.OpenQuery ("5CW")
DoCmd.OpenQuery ("5ML")
DoCmd.OpenQuery ("5M")
DoCmd.OpenQuery ("5S")
DoCmd.OpenQuery ("RA")

'Save and close queries and table
    DoCmd.Close acQuery, "New January"
    DoCmd.Close acQuery, "Renew January"
    DoCmd.Close acQuery, "Count October"
    DoCmd.Close acQuery, "Count November"
    DoCmd.Close acQuery, "Count December"
    DoCmd.Close acQuery, "5B"
    DoCmd.Close acQuery, "5CW"
    DoCmd.Close acQuery, "5ML"
    DoCmd.Close acQuery, "5M"
    DoCmd.Close acQuery, "5S"
    DoCmd.Close acQuery, "RA"

'Make Table, delete w/in c dups, Open Data Table S D Between C, close table
  DoCmd.SetWarnings False

  DoCmd.OpenQuery ("Table S+D")
  DoCmd.OpenQuery ("Delete C+S+D")

  DoCmd.SetWarnings True

DoCmd.OpenTable ("S D Between C")
DoCmd.Close acTable, "S D B C"

'Run saved exports of the queries to folder
DoCmd.RunSavedImportExport "Export-S+D"
DoCmd.RunSavedImportExport "Export-S+D+L"
DoCmd.RunSavedImportExport "Export-S+D+L+F"
DoCmd.RunSavedImportExport "Export-S+D+L+F+M"
DoCmd.RunSavedImportExport "Export-C+S+D"
DoCmd.RunSavedImportExport "Export-C+S+D+L"
DoCmd.RunSavedImportExport "Export-C+S+D+L+F"
DoCmd.RunSavedImportExport "Export-C+S+D+L+F+M"
DoCmd.RunSavedImportExport "Export-New January"
DoCmd.RunSavedImportExport "Export-Renew January"
DoCmd.RunSavedImportExport "Export-Count October"
DoCmd.RunSavedImportExport "Export-Count November"
DoCmd.RunSavedImportExport "Export-Count December"
DoCmd.RunSavedImportExport "Export-5B"
DoCmd.RunSavedImportExport "Export-5CW"
DoCmd.RunSavedImportExport "Export-5ML"
DoCmd.RunSavedImportExport "Export-5M"
DoCmd.RunSavedImportExport "Export-5S"
DoCmd.RunSavedImportExport "Export-S D Between C"
DoCmd.RunSavedImportExport "Export-RA"

    Dim n As Integer
    Dim db As DAO.Database

    Set db = CurrentDb
    For n = db.TableDefs.Count - 1 To 0 Step -1
    ' loop through all tables
        If InStr(1, db.TableDefs(n).Name, "ImportError") > 0 Then
        ' if table is import errors table
            DoCmd.DeleteObject acTable, db.TableDefs(n).Name
            ' delete table
        End If
    Next n

Application.SetOption "Auto compact", True

End Sub

Any help would be greatly appreciated. Thank you.

^^ That. Also you may need to Set accessApp = Nothing too. — Mathieu Guindon
– Mathieu Guindon, Commented Nov 9, 2016 at 20:16
you may also gain some speed by using With DoCmd ... End With instead of calling DoCmd on each line. — Scott Holtzman
– Scott Holtzman, Commented Nov 9, 2016 at 20:48
Thanks, I will try all of these suggestions. Scott, I'm not super experienced in writing code, so could you explain a little more how to use the With DoCmd ... End With ? — Lui
– Lui, Commented Nov 9, 2016 at 22:16
Ok, I tried that and it doesn't work. Access still sits open and doesn't close. Can I close it somehow from inside of the Access VBA? — Lui
– Lui, Commented Nov 9, 2016 at 23:18

Xam Eseerts · Accepted Answer · 2016-11-16 23:37:08Z

1

From what I can see your problem lies in lines as the following:

DoCmd.OpenQuery ("5ML")

Change this to:

accessApp.DoCmd.OpenQuery ("5ML")

To elaborate this a little bit: The DoCmd-Method is from the Access VBA Library. So you need an access application object to run it. You created this object so your code works. But when you invoke a method that is part of the access application and do not reference the application object you create a global reference to the automated application (in this case Access). That's why the application will not shut down, even if you close it properly at the end. That's of course something you should do as well. So what you have to do is first put the application object before every method that is part of the access application and second close the application properly. As a rule:

accessApp.AMethodOfTheAccessApplication
...
accessApp.CloseCurrentDatabase
accessApp.Quit
Set accessApp = Nothing

Here is an article that explains this more clearly, correctly and in detail: Program won't quit

Edit: To get your TableDef-Deletion-Loop running in Excel you can go with the following code (please note that you have to reference both, the Microsoft 16.0 Access database engine Object Library, as well as the Microsoft Access 16.0 Object Library (verion 16 is not important, just take any latest version of these libraries that is available to you)):

Dim AccessApp As New Access.Application
Dim tdf As DAO.TableDef

AccessApp.OpenCurrentDatabase ("SomeDrive:\SomePath\SomeDb.accdb")

For Each tdf In AccessApp.CurrentDb.TableDefs
' loop through all tables
    If InStr(1, tdf.Name, "tblDelete") > 0 Then
    ' if table is import errors table
        AccessApp.DoCmd.DeleteObject acTable, tdf.Name
        ' delete table
    End If
Next tdf

Set tdf = Nothing

AccessApp.CurrentDb.Close
AccessApp.Quit
Set AccessApp = Nothing

As you can see I used late binding instead of early binding (for testing purposes). So if you decide to go with early binding (like in your code above (to not worry about references I guess)) you just have to figure out how to declare the tdf object. What does this binding stuff mean?

Edit Edit: And here is the same loop with late binding:

Dim AccessApp As Object
Set AccessApp = CreateObject("Access.Application")
Dim tdf As Object

AccessApp.OpenCurrentDatabase ("SomeDrive:\SomePath\SomeDb.accdb")

For Each tdf In AccessApp.CurrentDb.TableDefs
' loop through all tables
    If InStr(1, tdf.Name, "tblDelete") > 0 Then
    ' if table is import errors table
        AccessApp.DoCmd.DeleteObject 0, tdf.Name
        ' delete table
    End If
Next tdf

Set tdf = Nothing

AccessApp.CurrentDb.Close
AccessApp.Quit
Set AccessApp = Nothing

edited Nov 16, 2016 at 23:37

answered Nov 13, 2016 at 16:47

Xam Eseerts

3673 silver badges19 bronze badges

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

11 Comments

Lui Over a year ago

Ok, that makes a lot of sense. I will try this! I do have a question though, do I add the access.App. inside of my macro in Access? Or should I move everything from that macro to Excel? It looks like I should copy the Access code to excel..

Xam Eseerts Over a year ago

Ah! Oh I am sorry, I didn't even see that the second macro was located in Access. I just assumed it was all in Excel. Well it depends. You certainly can move it to Excel. I would only move it to Excel if you only use the macro from your Excel file though. Otherwise keep it in a central place. Since your second macro is already in the Access database I am not sure if a global reference is causing the behaviour you are seeing though. It actually shouldnt, because when you are in Excel and type in Application.Something application is your Excel application. If you are in Access, the same holds.

Xam Eseerts Over a year ago

So maybe just try closing the Access application with the 3 lines I wrote at the end of my code sample?

Lui Over a year ago

This worked really well actually! I decided not to use the macro that I had made in Access at all, I just copied it into excel and added accessApp. before all the command lines. I did have to get rid of the loop that deleted the import error tables though, because that did not run nicely from Excel. (Any suggestions?) Thank you! (I have been having trouble trying to accept answers to questions, so I'm emailing with the stack overflow team and trying to resolve it. I'll accept your answer whenever I can!)

Xam Eseerts Over a year ago

Before the loop, did you change Set db = CurrentDb to Set db = accessApp.CurrentDb? Also what do you mean by did not run nicely from Excel? Do you mean in terms of performance? In that case I don't really know. I do loops with TableDefs differently though. I just do Dim tdf As DAO.TableDef, For each tdf in db.TableDefs .... Maybe this runs faster? It could however also be, that the loop is for some reason just doomed to run slower in Excel. I actually have the same problem the other way around with an application I am building.

|

Collectives™ on Stack Overflow

Running macro in Access from Excel, but Access doesn't close after running

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related