Open Access Database from Excel 1

[corycrum]

Hi,

I have an Access db that I would like to open each time i open an Excel workbook. As soon as I select "Open" on the "Unsafe Expressions Are Not Blocked" dialoge, the database opens and then instantly closes. Why does it keep closing on me? The code is below. Any help would be greatly appreciated.

Thanks!
Cory

Private Sub Workbook_Open()

    Dim strMyDatabase As String
    Dim appAccess As Access.Application
    
    strMyDatabase = "C:\DatabaseName.mdb"
    
    Set appAccess = CreateObject("Access.Application")
    appAccess.OpenCurrentDatabase strMyDatabase
    appAccess.Visible = True
    Set appAccess = Nothing

End Sub

 

[SkipVought]

Hi,

Open for what purpose?

1) as if you opened and now you want to view tables, create queries, reports & forms OR

2) you want to be able to PROGRAMATICALLY access objects.

Which one?


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[corycrum]

Primarily number 1. Do I have to choose between 1 or 2? Can i get them both?

Thanks Skip.
Cory

 

[SkipVought]

I do #2 (ahem...referring to the above items) often, although it is more like...
[tt]
1. open the db
2. loop thru some list accessing data from the database
3. close the db
[/tt]
Interesting, that when I run your code, using one of my Access dbs, MSACCESS.EXE loads for a few seconds and then goes away when the procedure ends.

So what I would suggest is declaring the scope of the Access Application Object as a MODULE variable, rather than a procedure variable.

Dim appAccess As Access.Application

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    appAccess.Quit
    Set appAccess = Nothing
End Sub

Private Sub Workbook_Open()
    Dim strMyDatabase As String
    
    strMyDatabase = "C:\DatabaseName.mdb"
    
    Set appAccess = CreateObject("Access.Application")
    appAccess.OpenCurrentDatabase strMyDatabase
    appAccess.Visible = True[s][b]
'    Set appAccess = Nothing[/b][/s]
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[corycrum]

I'll try it out this evening and let you know how it works. Thanks Skip.
Cory

 

[corycrum]

That worked! Thanks so much!

Cory