VBA to Open Access Database

[jdawg_1989]

Afternoon All,

I have some VBA code that looks to see if an Access Database is open, if it is it switch's to the database.

If not it opens up the Access DB

The problem is, it opens the access DB and then it closes straight away, and I can't understand why.

Here is my code:

Global oApp As Object

    Sub OpenAccess()
    On Error Resume Next

        Dim LPath As String
        Dim LCategoryID As Long
        Dim DATABASE As String
        Dim oApp As Access.Application
        
        DATABASE = "C:\Users\Joe Bloggs\Desktop\Databases\DB1\DB1.mdb"

        Set oApp = GetObject(, "Access.Application")
        If (Err.Number <> 0) _
        Or (oApp.CurrentDb.Name <> DATABASE) Then
        
        Set oApp = Nothing
        Set oApp = CreateObject("Access.Application")
            oApp.Visible = True
            oApp.OpenCurrentDatabase DATABASE
            
            Set oApp = CreateObject("Access.Application")
            oApp.Visible = True
            oApp.OpenCurrentDatabase DATABASE
            
        End If

        'Open form called Categories filtering by CategoryID
        LLocation = Range("A2").Value
        oApp.DoCmd.OpenForm "ReviewEBDOrdersFrm", , , "[Circuit]=" & "'" & LLocation & "'"

    End Sub

 

[Ruddles]

You've correctly declared oApp as Global, but then you created another variable called oApp inside OpenAccess() and used that. As it's local to OpenAccess() it gets destroyed when OpenAccess() ends.

Remove the Dim statement for oApp.

 

[Ruddles]

Or remove the Global declaration and change Dim to Static where you declare oApp.

 

[jdawg_1989]

Or remove the Global declaration and change Dim to Static where you declare oApp.

Problem Solved, Thanks Very Much! I'm learning VBA as I go along.

Thanks Again.

 

[Ruddles]

We make a good pair then, because I'm making it up as I go along!