Solved

How to close a specific Access.Application object

Posted on 2010-08-22
6
538 Views
Last Modified: 2012-05-10
I'm trying to make sure that a specific Access.Application object is closed before running an Excel macro. Right now I just call Access.Application.Quit when the Excel macro starts. That works except that it closes every instance of Access instead of one. Is there a way to close an Application.Access object by file name?

Thanks in advance!
0
Comment
Question by:ltdanp22
6 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 33501622
If you assign the Application object to a variable, say appAccess, then you can close that specific instance using the variable.  If you are talking about a file name, that would be a database.  Here is some code for working with the Access Application object and a specific database:
Public Sub OpenAnotherDatabase()

'Created by Helen Feddema 14-Feb-2010

'Last modified by Helen Feddema 14-Feb-2010



   Dim appAccess As New Access.Application

   Dim strDBNameAndPath As String

   Dim dbs As DAO.Database

   Dim rst As DAO.Recordset

   Dim dbe As DAO.DBEngine

   

   'Change to your db name and path

   strDBNameAndPath = "G:\Documents\Access 2002-2003 Databases\General.mdb"

   appAccess.Visible = True

   appAccess.OpenCurrentDatabase filepath:=strDBNameAndPath, _

      exclusive:=False

      

   'Run a procedure

   'appAccess.Run "PrintOrdersReport"

   

   'Run a macro

   'appAccess.DoCmd.RunMacro "mcrPrintOrdersReport"

   

   'Run an action query

   'appAccess.DoCmd.OpenQuery "qryDeleteSomeOrders"

   

   'Run SQL code

   strSQL = "DELETE tblOrders.ShippedDate FROM tblOrders WHERE ShippedDate = #8/4/1994#;"

   Debug.Print "SQL string: " & strSQL

   'appAccess.DoCmd.RunSQL strSQL

   

   'Iterate through a recordset

   Set dbe = appAccess.DBEngine

   Set dbs = dbe.OpenDatabase(strDBNameAndPath)

    

   Set rst = dbs.OpenRecordset("tblCategories")

   Do Until rst.EOF

      Debug.Print rst![CategoryName]

      rst.MoveNext

   Loop

   rst.Close

    

   Set dbs = Nothing

   Set appAccess = Nothing

   

End Sub

Open in new window

0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 250 total points
ID: 33501798
The trick is to determine if there is an open instance of the automation object before creating a new instance.  This applies equally to any automation object. The way it works is thru the use of the GetObject method to attempt to grab an open instance of the object (e.g. Access). If there is no open instance, the application will return an error #429; at which point the code will resume and create a new instance using the CreateObject method.  You therefore, can limit the number of instances opened to one, and can close the open instance before exiting the procedure.
                 See   http://support.microsoft.com/kb/288902
     
0
 
LVL 15

Assisted Solution

by:dbbishop
dbbishop earned 250 total points
ID: 33502729
When you execute Access.Application.Quit, in this context 'Access' is referring to the object you set up tp reference a specific Access database. Not sure what you mean by closing an application without closing all instances of Access.

Unless you used GetObject to grab an already open instance of Access, CreateObject shoud create a new instance of Access and your Quit method should only work against the new instance, and not any existing instances.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:ltdanp22
ID: 33581143
To recap, I have an Excel macro that opens an Access database. If the code breaks, this instance of Access is left open (it's also invisible which is why I'm trying to close it for the user).
The file will always have the same name: "ISCM Database.accdb". Unfortunately, the path to the Access file is not always. I've only seen examples that pass the full file path as a parameter.
Is it possible to use GetObject with only the file name and not the full file path? If so, what's the correct syntax?
0
 

Author Comment

by:ltdanp22
ID: 33581174
Is it possible to loop through all the open Access objects and check the filename, and then get the file path if the file name matches "ISCM Database.accdb"
0
 
LVL 1

Expert Comment

by:JamesCronshaw1
ID: 33626169
If your code breaks for any reason, you shouldn't need to know which/what databases are currently open, since you have created a specific object/connection to the database you are working within("ISCM Database.accdb").

Upon a break, the error handling you should have in place would clean up the issue for you.

E.g. (note: borrowing some of Helen's code posted) this would be a very basic way of handling things:
Public Sub OpenDatabase()



Dim appAccess As Access.Application

Dim strDBName As String



On Error Goto ErrHandler



    Set appAccess = New Access.Application



'-- Set the filepath/name

    strDBName = "...ISCM Database.accdb"



'-- Open the database

    appAccess.OpenCurrentDatabase filepath:=strDBName, _

                                  exclusive:=False



'-- Display (or hide) the app window

    appAccess.Visible = True



'-- Do stuff......





'-- Close the database

    Set appAccess = Nothing

    

Exit Sub



ErrHandler:



'-- Do something with the error

    msgbox Err.Number & " - " & Err.Description



'-- Close the database

    Set appAccess = Nothing



End Sub

Open in new window

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now