Opening Access File from Excel

[RFeniuk]

I am trying to open and access file from and Excel module. I have been able to get the coding in to get the access file to open but it won't stay open. I would also like to have it stay on top until the user clicks the "exit" command button. Here is the coding I have thus far can someone out there help me. I know the problem is in the last two lines of code. Thanks all.

Sub openDatabase()

Dim appAccess As Object

' Code to open the chosen file

Set appAccess = CreateObject("Access.Application&quot
appAccess.openCurrentDatabase "c:\my directory\myfilename.mdb"

appAccess.doCmd.openform "MainMenu"
appAccess.Visible = True


appAccess.Application.Quit acExit
Set appAccess = Nothing


End Sub

 

[xlbo]

Just get rid of the last 2 lines completely - all they do is close access and set the varoiable to empty to preserve memory

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[RFeniuk]

Thanks Geoff I tried that but all that happens is the file pops up and then closes again.

 

[Zathras]

The problem is that by putting the Dim appAccess inside of the routine (i.e., between the Sub and End Sub), Excel destroys the object when the sub ends.

To fix, move the Dim appAccess to the Declarations area (i.e., before the Sub).

To experiment, leave the Dim where it is and put a MsgBox before the End Sub.

If you need to work with the appAccess object from some other module or form, then change Dim to Public :

Option Explicit
Public appAccess As Object

Sub OpenDatabase()
  ' Code to open the chosen file
  Set appAccess = CreateObject("Access.Application")
  With appAccess
    .openCurrentDatabase "c:\my directory\myfilename.mdb"
    .doCmd.openform "MainMenu"
    .Visible = True
  End With
End Sub

 

[RFeniuk]

Thanks Zathras. That worked great. Now how do I get Access to stay open on the desktop, i.e. open on top at the main menu. Now it just opens and sends it to the taskbar.

Thanks

 

[RFeniuk]

I actually got the command to work so the file stays ope on the desktop but there are two things that continue.

1. Access opens minimized, I have the file opening to maximize but not the application. How do I get the application to open maximized.


2. When I close access from access the file closes but the aplication remains open until I click stop in VB. How do I get the module to release the Access application so I can close it?

Thanks for the help on this.