Launch MS Access DB from a button in Excel

[deeds]

I am trying to create a button within Excel that will launch MS Access & open
a database. Any ideas?

Thanks

 

[Joel]

set AcDb =getobject("C:\temp\MyDB.mdb")

 

[deeds]

Thanks....However, I put this code in, run it, and it seems to run but Access
does not open....any thoughts?...

 

[Joel]

Add a visible statment. If you check your task manager you probably still
have the database running as a Process.

set AcDb =getobject("C:\temp\MyDB.mdb")
acdb.Application.Visible = True

 

[egun]

Try this:

Option Explicit
Option Base 1

Sub Open_Access()
Dim accApp As Access.Application
Dim accPath As String
Dim accDB As String
'
' Define path to your database and database name here
'
accPath = "U:\"
accDB = "db1.mdb"
'
' Create a new instance of Access and make it visible
'
Set accApp = New Access.Application
accApp.Visible = True
'
' Open the database
'
accApp.OpenCurrentDatabase filepath:=accPath & accDB
'
' Do stuff in the Access database here...
'
' Close the database, exit Access and release the object
'
accApp.CloseCurrentDatabase
accApp.Quit
Set accApp = Nothing
'
End Sub

 

[egun]

Of course, you stick this code in your button's "Click" event!

 

[deeds]

Thanks!...I hate to say it...but I can't seem to get this to work. Could you
walk through the process of putting this on a button? I did everything you
said but still get errors.
Thanks

 

[egun]

Here are the steps I just went through (starting with a new workbook):
1. Go into Design Mode on whatever worksheet you want to have the button
(in my case, Sheet1). The design mode button is in on the Visual Basic
toolbar, so if that is not showing, go to View/Toolbars, and select Visual
Basic).
2. Show the Control Toolbox using the same method.
3. Add a Command Button to the worksheet by selecting it in the control
toolbox and then clicking or click-dragging on the worksheet. By default,
mine was named CommandButton1.
4. Right click on the button you just added, and select "View Code". The VB
editor opens up, and automatically adds the button's "Click" event.
5. Type in "Open_Access", which will call the subroutine that I gave you
earlier.
6. In the VB editor, insert a module by selecting Insert/Module. This will
insert a new source code module into the VBA project.
7. Cut and paste the example I gave you into this module, in its entirety.
You will need to change the path and the filename to point to an actual
Access database on your machine.
8. Make sure that Excel has the references it needs to work with Access by
selecting Tools/References... in the Visual Basic editor, and checking the
box for "Microsoft Access 11.0 Object Library", or whatever version you are
running.
8. Hide the control toolbox and exit Design Mode.
9. Save your workbook!
10. Press the button. It should open up Access and the database you
specified.

That's the mechanics for hooking up a button to some VBA source. You can
now modify the source code so that the button does whatever you want it to
do. Hope this helps.

 

[deeds]

Thanks....1 more problem...it seems to work...and actually opens Access
however I get the following VBA message...Microsoft Access cannot open the
database because it is missing, or opened exclusively by another user"....I
do not have the database open when I try this....can't figure out why it
won't open it...any ideas?

Thanks again

 

[egun]

I'm not really much of an Access expert, so I'm not sure why you're getting
that message. Check your path and filename to make sure they are correct.
Look for a ".ldb" file in the folder to see if anyone else has the database
open. If the lock file is there but you're sure there is no one using the
database, delete the ".ldb" file and try running the macro again. After
that, I suggest going to the Access newsgroup and ask an expert there.

One more thing. Is the database secured? Does it require a
username/password to open it? If so, you might want to read this article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;192919

 

[deeds]

I get a quick flash of the database opening...then it just shuts down...no
sign of it in the Task Mgr....strange. I see it open and then shuts down.
Anyone have ideas? Thanks again egun for the help

 

[dan dungan]

What code are you using to open the database?

If you are using the code posted earlier in this thread, it is
doing just what the code tells it to do.

' Open the database
'
accApp.OpenCurrentDatabase filepath:=accPath & accDB
'
' Do stuff in the Access database here...
'
' Close the database, exit Access and release the object

There are no commands to the database except to close.

What do you want to do in the database?