Open DB From Excel VBA Works for one db but not another

[cwojtak]

Hello, I have a sub that opens a database that works every time. Now I tried to create a new sub, in the same workbook but a different module that opens a different database, using the same exact code and it doesn't work. No errors or anything, it just opens the db then closes it immediately.

Working code:

Sub OpenLinearityDB()


    Dim appAccess As Object


    'create new access object
    Set appAccess = CreateObject("Access.Application")


    'open the acces project
    Call appAccess.OpenCurrentDatabase("C:\Users\username\Documents\Databases For Interlock\IBP_LINEARITY_WORKER.accdb")
    appAccess.Visible = True


End Sub

Not Working Code:

Sub OpenDEPDatabase()


    Dim appAccess As Object


    'create new access object
    Set appAccess = CreateObject("Access.Application")


    'open the acces project
    Call appAccess.OpenCurrentDatabase("C:\Users\username\Documents\Databases For Interlock\DEP_DEAL_WORKER.accdb")
    appAccess.Visible = True


End Sub

[Bob Phillips]

Isn't it just because the variable appAcccess is garbage cleared as soon as the sub exits. Try setting global variables

Public appAccess1 As Object
Public appAccess2 As Object

and use each in the two procedures.

[cwojtak]

Thank you for the quick response xld! I tested it out and no luck. I did find that if I open three tables in the DB and then make it visible it stays up. Unfortunately I cannot edit anything in the DB without it crashing or acting very buggy when I open it using this method. When I open it manually through finder though it works fine so I know it is not a corrupt db issue.


Solution:

Sub OpenDEPDatabase()


    Dim appAccess As Object


    'create new access object
    Set appAccess = CreateObject("Access.Application")


    'open the acces project
    Call appAccess.OpenCurrentDatabase("C:\Users\username\Documents\Databases For Interlock\DEP_DEAL_WORKER.accdb")
    appAccess.DoCmd.OpenTable "DEP_AMS", acViewNormal
    appAccess.DoCmd.OpenTable "DEP_AP", acViewNormal
    appAccess.DoCmd.OpenTable "DEP_EU", acViewNormal
    appAccess.Visible = True


End Sub

[cwojtak]

I found that if I define the access application at the top of the module rather than in the sub it works! One caution - when I open the book manually and delete records from a table it asks me if I'm sure I wanna do that, when I open it using the code below I get no warnings.

Dim appAccess As Access.Application


Sub OpenDEPDatabase()


    'create new access object
    Set appAccess = CreateObject("Access.Application")


    'open the acces project
    Call appAccess.OpenCurrentDatabase("C:\Users\username\Documents\Databases For Interlock\DEP_DEAL_WORKER.accdb")
    appAccess.DoCmd.OpenTable "DEP_AMS"
    appAccess.DoCmd.OpenTable "DEP_AP"
    appAccess.DoCmd.OpenTable "DEP_EU"
    appAccess.Visible = True


End Sub