Using VBA to switch to a particular Access database

[Robert Bonds]

Need a bit of help, please, with some VBA code that I've written in Microsoft
Outlook 2007. It is intended to call up a specific Microsoft Access database
and open a particular form and subform, so that it can then go on to take
various actions based on the values from Outlook. Here's the basic code:

------

Sub SwitchToProjects()

Dim appAccess As Access.Application
Dim ThisDatabaseFileName As String

ThisDatabaseFileName = "C:\myFile.mdb"
Set appAccess = GetObject(ThisDatabaseFileName, "Access.Application")

appAccess.Forms!frmHome!frmSplash.Visible = False
appAccess.Forms!frmHome!frmCompanies.Visible = False
appAccess.Forms!frmHome!frmProjects.Visible = True

'Do additional actions here

End Sub

------

The code works fine, except that it opens a new instance of Access and the
database every time it's called. What I'm trying to write is some kind of IF
THEN clause at the beginning of this procedure that will test whether that
particular database is already open, and if so, simply switch to it and open
the correct form.

I've tried every syntax I can think of or learn about, so far without
success. At one user's group on line I was able to find a procedure using the
Windows API (which I'm not too familiar with yet) that will check whether
Access is running and, if so, switch the focus over to the Access window.
That works, but it doesn't seem to be able to distinguish among Access
windows if more than one database is open. And even if it does switch to the
correct database, I need some way to actually work with it... and so far I
haven't been able to find the syntax to have a reference to the Access object
without opening it again.

Any suggestions would be most welcome. Thank you!

 

[Stefan Hoffmann]

hi Robert,

ThisDatabaseFileName = "C:\myFile.mdb"
Set appAccess = GetObject(ThisDatabaseFileName, "Access.Application")

------

The code works fine, except that it opens a new instance of Access and the
database every time it's called. What I'm trying to write is some kind of IF
THEN clause at the beginning of this procedure that will test whether that
particular database is already open, and if so, simply switch to it and open
the correct form.

Try this:

Option Compare Database
Option Explicit

Public Sub SwitchToProjects()

On Local Error Resume Next

Const DATABASE As String = "C:\myFile.mdb"

Dim appAccess As Access.Application

Set appAccess = GetObject(, "Access.Application")
If (Err.Number <> 0) _
Or (appAccess.CurrentDb.Name <> DATABASE) Then
Set appAccess = CreateObject("Access.Application")
appAcceass.OpenCurrentDatabase DATABASE
If Err.Number <> 0 Then
GoTo LocalError
End If
End If

On Local Error GoTo LocalError

appAccess.Forms!frmHome!frmSplash.Visible = False
appAccess.Forms!frmHome!frmCompanies.Visible = False
appAccess.Forms!frmHome!frmProjects.Visible = True

'Do additional actions here

Exit Sub

LocalError:
MsgBox Err.Description

End Sub


http://msdn.microsoft.com/en-us/library/aa221388(office.11).aspx

mfG
--> stefan <--

 

[Robert Bonds]

hi Robert,


Try this:

Option Compare Database
Option Explicit

Public Sub SwitchToProjects()

On Local Error Resume Next

Const DATABASE As String = "C:\myFile.mdb"

Dim appAccess As Access.Application

Set appAccess = GetObject(, "Access.Application")
If (Err.Number <> 0) _
Or (appAccess.CurrentDb.Name <> DATABASE) Then
Set appAccess = CreateObject("Access.Application")
appAcceass.OpenCurrentDatabase DATABASE
If Err.Number <> 0 Then
GoTo LocalError
End If
End If

On Local Error GoTo LocalError

appAccess.Forms!frmHome!frmSplash.Visible = False
appAccess.Forms!frmHome!frmCompanies.Visible = False
appAccess.Forms!frmHome!frmProjects.Visible = True

'Do additional actions here

Exit Sub

LocalError:
MsgBox Err.Description

End Sub


http://msdn.microsoft.com/en-us/library/aa221388(office.11).aspx

mfG
--> stefan <--
.

Stefan, thank you! This is perfect. For some reason I had to comment out the
little "If Err.Number <> 0 Then GoTo LocalError" section near the end to make
it work, but at least now it does work. Thanks again. -- Best, Robert Bonds

 

[Stefan Hoffmann]

hi Robert,

Stefan, thank you! This is perfect. For some reason I had to comment out the
little "If Err.Number<> 0 Then GoTo LocalError" section near the end to make
it work, but at least now it does work. Thanks again. -- Best, Robert Bonds

Ahh, there is a Err.Clear missing before the OpenCurrentDatabase line.


mfG
--> stefan <--

 

[Banana]

On Local Error Resume Next

This is tangential but I was intrigued because I never had seen "Local"
keyword. I tried it out and it does seems to be valid. There were no
documentation in the help files (the entry on "On Error" statement never
even mentions it at all).

Googling it a bit it seems to be supported for backward compatibility
and thus is functionally equivalent to "On Error..." and shouldn't be
used. Seem that the languages' ancestors did not have the error bubble
up as VB/VBA does so there was a need for both local and global error
handler but this no longer is the case in VB/VBA family.

Thought I'd mention this.

 

[Stefan Hoffmann]

hi,

Googling it a bit it seems to be supported for backward compatibility
and thus is functionally equivalent to "On Error..." and shouldn't be
used. Seem that the languages' ancestors did not have the error bubble
up as VB/VBA does so there was a need for both local and global error
handler but this no longer is the case in VB/VBA family.

Thought I'd mention this.

You're right. I'm using this idiom since decades - almost - and prefer
it over


Public Sub MyWhatSoEverCalledProc()

On Error GoTo Err_MyWhatSoEverCalledProc

Exit Sub

Err_MyWhatSoEverCalledProc:

End Sub


as it is easier to read (at least for me)


Public Sub MyWhatSoEverCalledProc()

On Local Error GoTo LocalError

Exit Sub

LocalError:

End Sub


mfG
--> stefan <--