Iterate all open Dbs in all instances of MSA 2003

M

mindprism

How can I iterate through all databases (opened by various access instances)
using VBA?
 
C

Chris O'C via AccessMonster.com

I don't think there's an easy way to do this in vba. If you use getobject in
Access, it grabs the currently open instance of Access, not some other ones
you have open. It's part of the feature that lets you double click on a db
file name and if you already have that app open, it sets focus on that window.


You could link every db app to a special back end table that adds a record
indicating which db is connected and remove that record when the app closes.
Then use vba code to check the table, but this method is unreliable because
db apps can get disconnected or shut down without removing the "I'm open!"
record.

Chris
Microsoft MVP
 
A

Alex Dybenko

Hi,
if you need to close all these instances at the end - then you can use
GetObject(,"Access.Application") to get running instance.
Else I don’t know. Perhaps you can explain what you want to achieve

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
 
E

Eric Gerds

Hope this starts you in the right direction
replace msaccess.exe with the name of your ms access 2003 name

Private Sub Access_Instances()
'citing source concept
http://msdn.microsoft.com/en-us/library/aa394372(VS.85).aspx
On Error Resume Next
Dim objWMIService As Object, colProcesses As Object, objProcess As Object,
objAccess As Object
Dim ProcessId As Long
Set objWMIService = GetObject("winmgmts:")
Set colProcesses = objWMIService.ExecQuery("Select * from Win32_Process
where name = 'msaccess.exe'")
For Each objProcess In colProcesses
ProcessId = objProcess.ProcessId
Debug.Print ProcessId & " " & objProcess.Caption
Set objAccess = objProcess
With objAccess
'reference access application object here
End With
Next
Set objAccess = Nothing
Set objProcess = Nothing
Set colProcesses = Nothing
Set objWMIService = Nothing
End Sub
 
Top