Using VBA to switch to a particular Access database

  • Thread starter Thread starter Robert Bonds
  • Start date Start date
R

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!
 
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.
Click to expand...
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 Hoffmann said:
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 <--
.
Click to expand...

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
 
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
Click to expand...
Ahh, there is a Err.Clear missing before the OpenCurrentDatabase line.


mfG
--> stefan <--
 
Stefan said:
On Local Error Resume Next
Click to expand...

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.
 
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.
Click to expand...
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 <--
 
Back
Top