Iterating Access db's

[aziz rasul]

Does anyone have any VBA code to be used in Excel that loops through say 5 open Access db's and enables me to close a specific one?

 

[Minty]

Cross posted here https://www.utteraccess.com/forum/index.php?showtopic=2053158&hl=

 

[The_Doc_Man]

Aziz, the problem with externally closing a specific Access database from the outside is that you cannot know what is pending inside each one. To force a database to exit, you have to be able to look inside its memory to find open structures so you can close them in an orderly manner. Otherwise you run the risk of locking something in an inconsistent state, which renders it useless in the future. And it is FORBIDDEN (by Windows rules as well as government standards on system security) to actually look inside the memory of another process unless you have not only full admin privileges but also full permissions. (If you are admin, of course you CAN get those permissions or bypass them.) Let's not even BEGIN to think about what it would mean if the five DBs were on five different machines. The point is that the ability to externally compromise another process is a hacker type of action. I don't think you are a hacker based on your many previous posts here, so I can only think that you didn't realize quite what you were asking.

You would do better to build something in each of those databases that involves some sort of timer, then putting a table in the back-end to signal "externally forced shutdown" so that each DB could do what it needed to shut itself down. The only process that can safely shut down an active DB session is that session itself. Search the forum for the topic of "forcing database shutdown" because this has come up before, and not terribly long ago if I recall correctly.

You should also remember that we like to know if you are cross-posting.

 

[aziz rasul]

Thanks The_Doc_Man. Point noted regarding cross-posting.

What I was essentially trying to do was to import data from an Access table into Excel, once done close that specific db.

 

[MajP]

Keep a pointer to each db. This demo I open two dbs and then close them one by one.

Dim appAccess As Object
Dim appAccess2 As Object

Sub Example1()
'Access object
'create new access object
Dim myDir As String
myDir = CurDir
Debug.Print myDir
Set appAccess = CreateObject("Access.Application")
Set appAccess2 = CreateObject("Access.Application")
'open the acces project
 appAccess.OpenCurrentDatabase myDir & "\" & "zigzag.accdb"
 appAccess.Visible = True
 appAccess2.OpenCurrentDatabase myDir & "\" & "zigzag2.accdb"
 appAccess2.Visible = True
 MsgBox "2 DBs are open. Close the first"
 CloseDb appAccess
 MsgBox "Close the Second"
 CloseDb appAccess2
End Sub


Public Sub CloseDb(TheDb As Object)
  TheDb.CloseCurrentDatabase
End Sub

 

[MajP]

Sorry, you probably want to close the db and quit the application. Add "thedb.quit".

Public Sub CloseDb(TheDb As Object)
  TheDb.CloseCurrentDatabase
  TheDb.Quit
End Sub

 

[aziz rasul]

My issue is slightly different. I have separate code that opens up different MS Access databases. At one point I now want to close a specific MS Access database. To do that I need to grab the particular instance of the specific MS Access file and close it. One way of doing this, I thought, was to iterate through the open MS Access databases, 'catch' the one I want and close it, much like you do when you iterate through open workbooks e.g.

    Dim objExcelApp As Object
    Dim wb As Excel.Workbook

    Set objExcelApp = GetObject(, "Excel.Application")
    
    For Each wb In objExcelApp.Workbooks
        If wb.Name = strExcelFile Then
                wb.Close True
                Exit For
        End If
    Next wb
 
    If objExcelApp.Workbooks.Count = 0 Then
        objExcelApp.Application.Quit
    End If
    
    Set objExcelApp = Nothing

Whatever code I use, it just opens a second instance of the file I am trying to close. Arrrgh.

Note that I'm only grab data from the db's i.e. I don't do anything complicated. Closing the specific database, prevents the user from closing the database manually.

 

[Minty]

If you only want data simply connect to the database concerned, and get your data.
It shouldn't matter if it's open?

 

[aziz rasul]

That's true Minty. However it would be a nice touch that each of the 5 MS access databases are automatically closed once the data processing has been completed for each database. This way the end user, doesn't have to manually close all 5 MS Access databases.

I know that when I have finished all the coding, the end users will say, can't you add some code that will close the databases?

 

[MajP]

My issue is slightly different. I have separate code that opens up different MS Access databases. At one point I now want to close a specific MS Access database. To do that I need to grab the particular instance of the specific MS Access file and close it. One way of doing this, I thought, was to iterate through the open MS Access databases, 'catch' the one I want and close it, much like you do when you iterate through open workbooks e.g.

There is nothing to iterate because there is no collection that holds pointers to multiple instance of Access. You would have to do that yourself. I would use a dictionary object to hold my pointers and then you could close them simply by name. In this example I open 2 instances of access at one time and store the references in a collection. At a later time I can close them by name

'Need reference to Microsoft Scripting Runtim
Dim MyDict As Dictionary

Sub Example1()
 'create new access object
 Dim appAccess As Object
 Dim myDir As String
 Set MyDict = New Dictionary
 myDir = CurDir
 
 Set appAccess = CreateObject("Access.Application")
 'open the acces project
 appAccess.OpenCurrentDatabase myDir & "\" & "zigzag.accdb"
 appAccess.Visible = True
 'store in dictionary
 MyDict.Add appAccess.currentproject.Name, appAccess
 Set appAccess = CreateObject("Access.Application")
 appAccess.OpenCurrentDatabase myDir & "\" & "zigzag2.accdb"
 MyDict.Add appAccess.currentproject.Name, appAccess
 appAccess.Visible = True
End Sub



Public Sub CloseDbByName(strName As String)
  Dim TheDb As Object
  If MyDict.Exists(strName) Then
    Set TheDb = MyDict(strName)
    MyDict.Remove strName
    TheDb.CloseCurrentDatabase
    TheDb.Quit
  End If
End Sub

' Call the db by name to close it
Public Sub PromptClose()
  CloseDbByName "zigzag.accdb"
End Sub
Public Sub PromptClose2()
  CloseDbByName "zigzag2.accdb"
End Sub

 

[theDBguy]

Hi Aziz. Pardon me for jumping in but rather than try to close the database, I think Minty was trying to say don’t even open them, so there’s no need to close them. As he said, you should be able to pull data from a database without opening it first.

 

[aziz rasul]

Minty, theDBguy, I didn't realise that you could do that. Would you be able to give me a simple example of how that would work?

 

[The_Doc_Man]

The point being made about not even opening the DB, just grabbing data from it, is valid and I will not interfere with TheDBGuy or Minty on anything they wanted to tell you.

The point I was making is that if you DIDN'T open the database, you cannot expect to be able to close it. Because the database object associated with an open database resides in the workspace of the task / session / process that opened it. If that ain't you, your closure attempt ain't gonna work without doing damage.

So in looking for / iterating through databases... if you were thinking about iterating through files determined via the File System Object, my technical objection applies. If you were thinking about opening the DB, pulling data, and LEAVING IT OPEN (for a while) then you might do that. But in that case your process would have the DB structure (actually, workspace structure) in its own memory and the collection of open databases would be in the workspace object.

I'm with TheDBGuy and Minty, though. Better if you didn't open the files at all.

 

[aziz rasul]

MajP, how can I amend your code so that MyDict retains the values of a specific database when run in the opening function and a separate closing function within the same module closes the specific database when the opening function has gone out of scope? Hope that makes sense.

 

[MajP]

Actually my code posted was dumb. My assumption was you actually were running multiple Access applications. If you simply want to open multiple dbs and pull data I would use dao. Define a workspace as Doc_Man said which holds a collection of DBs. Then you can close as desired.
https://docs.microsoft.com/en-us/of...e-reference/workspace-opendatabase-method-dao

 

[The_Doc_Man]

I will not make assumptions on MajP's answer about specific code, but the workspace contains a collection of currently open databases that you could iterate through.

I am not sure of the effects of having a copy of the DB object residing in a dictionary, which in MajP's example is a persistent object external to the function. I don't know if that is enough to keep the "real" DB structure from becoming dereferenced (i.e. usage count drops to zero) and thus eligible for automatic cleanup.

EDIT: I see that MajP and I crossed answers. He apparently agrees with me that if YOU do the opening, you can also manage the closure.

 

[Minty]

Going back to the suggestion of simply interrogating another database something like

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = Opendatabase("PATH AND FILENAME TO DB.mdb")

Set rs = db.Openrecordset("NAME OF TABLE")

Would open a recordset based on the table in the remote database?
Does that steer you in the right direction?


EDIT: Having had a think, as another option, you could simply create a link to the remote table do your stuff (Query /copy / report) then remove the link again if you don't want to make it permanent ?

 

[The_Doc_Man]

Technically, I think making the link to a table in this "other" database opens the DB when you open the link. I.e. I believe the .LDB opens on the BE even though the thing that Access opened directly was the FE. I know I have seen .LDB files opened for my BE via linked tables being opened by the FE. The good news is that if you unlink the table, the link's closure will unlink from the .LDB file, which COULD result in its deletion if you were the last user of that BE file at the time.

 

[Minty]

I am writing code in Excel.

Ah - no mention of this earlier.
The same methods may not be suitable.

 

[aziz rasul]

Mentioned in opening post.

MajP's code did work according to what I wanted if the code remained within scope when I was closing the specific database. However if I have code that opens the specific database and then the function ends and afterwards I use the snippet of code to close the said database in a separate function, then it doesn't work as it doesn't seem to 'remember' the value of the specific db in MyDict.