Solved Open Access Database From Excel

[Emma35]

Hi All,
I'm using Excel and Access 2016. Is there a way i can add a command button to Excel which opens an Access database at the switchboard ?. I've seen a few threads which have offered a solution but i can't get it to work. Is it a matter of adding a macro to the command button ?

Thanks

 

[Ranman256]

paste this code into a module. (Alt-F11, insert , module)
Then it will open ANY file via its extension, not just access....
.pdf files will open in acrobat,
.doc files in word
etc

USAGE:
OpenNativeApp "c:\folder\myDb.accdb"

Option Compare Database
Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
Private Declare Function GetDesktopWindow Lib "user32" () As Long
Const SW_SHOWNORMAL = 1
Const SE_ERR_FNF = 2&
Const SE_ERR_PNF = 3&
Const SE_ERR_ACCESSDENIED = 5&
Const SE_ERR_OOM = 8&
Const SE_ERR_DLLNOTFOUND = 32&
Const SE_ERR_SHARE = 26&
Const SE_ERR_ASSOCINCOMPLETE = 27&
Const SE_ERR_DDETIMEOUT = 28&
Const SE_ERR_DDEFAIL = 29&
Const SE_ERR_DDEBUSY = 30&
Const SE_ERR_NOASSOC = 31&
Const ERROR_BAD_FORMAT = 11&

Public Sub OpenNativeApp(ByVal psDocName As String)
Dim r As Long, msg As String

r = StartDoc(psDocName)
If r <= 32 Then
    'There was an error
    Select Case r
        Case SE_ERR_FNF
            msg = "File not found"
        Case SE_ERR_PNF
            msg = "Path not found"
        Case SE_ERR_ACCESSDENIED
            msg = "Access denied"
        Case SE_ERR_OOM
            msg = "Out of memory"
        Case SE_ERR_DLLNOTFOUND
            msg = "DLL not found"
        Case SE_ERR_SHARE
            msg = "A sharing violation occurred"
        Case SE_ERR_ASSOCINCOMPLETE
            msg = "Incomplete or invalid file association"
        Case SE_ERR_DDETIMEOUT
            msg = "DDE Time out"
        Case SE_ERR_DDEFAIL
            msg = "DDE transaction failed"
        Case SE_ERR_DDEBUSY
            msg = "DDE busy"
        Case SE_ERR_NOASSOC
            msg = "No association for file extension"
        Case ERROR_BAD_FORMAT
            msg = "Invalid EXE file or error in EXE image"
        Case Else
            msg = "Unknown error"
    End Select
'    MsgBox msg
End If
End Sub

Private Function StartDoc(psDocName As String) As Long
Dim Scr_hDC As Long

Scr_hDC = GetDesktopWindow()
StartDoc = ShellExecute(Scr_hDC, "Open", psDocName, "", "C:\", SW_SHOWNORMAL)
End Function

 

[arnelgp]

you can have a button in excel.
assign a macro to it:

'set Reference to Microsoft Access XX.X Object Library
Dim msa As Access.Application
Dim db As AccessObject

public sub sOpendb()
Set msa = CreateObject("access.application")
msa.Visible = True
msa.OpenCurrentDatabase "yourDatabasePath + Name Here"
end sub

 

[Isaac]

I recommend using late binding, so you don't need to worry about Office versions of people who use your Excel workbook.

Four lines of code will do:

Dim accessapp As Object
Set accessapp = CreateObject("access.application")

accessapp.opencurrentdatabase ("path to database.accdb")
accessapp.UserControl = True

 

[Emma35]

Ranman and arnel thank you both. You won't be surprised to learn that i can't get either suggestion to work (VBA and me are not friends)

Ranman...i get an error in the first line which is red. I'm also not sure where to put the following ?.

USAGE:
OpenNativeApp "c:\folder\myDb.accdb" Error says 'Expected : Text or Binary'


arnel....im getting an error in this line Dim msa As Access.Application
It says 'User-defined type not defined'

 

[Isaac]

Ranman and arnel thank you both. You won't be surprised to learn that i can't get either suggestion to work (VBA and me are not friends)

Ranman...i get an error in the first line which is red. I'm also not sure where to put the following ?.

USAGE:
OpenNativeApp "c:\folder\myDb.accdb" Error says 'Expected : Text or Binary'


arnel....im getting an error in this line Dim msa As Access.Application
It says 'User-defined type not defined'

See my last post...

 

[Emma35]

I recommend using late binding, so you don't need to worry about Office versions of people who use your Excel workbook.

Four lines of code will do:

Dim accessapp As Object
Set accessapp = CreateObject("access.application")

accessapp.opencurrentdatabase ("path to database.accdb")
accessapp.UserControl = True

Thanks Isaac...do i put that code into a macro and attach it to a button ?

 

[Isaac]

I would right-click on the button (design view), choose View Code, and then paste that code in the button's click event.

No macros.

As always, when you're done editing VBA code, remember to click Debug>Compile, then Save. (top menu of VBA project window)

 

[Emma35]

I would right-click on the button (design view), choose View Code, and then paste that code in the button's click event.

No macros.

As always, when you're done editing VBA code, remember to click Debug>Compile, then Save. (top menu of VBA project window)

I would right-click on the button (design view), choose View Code, and then paste that code in the button's click event.

No macros.

As always, when you're done editing VBA code, remember to click Debug>Compile, then Save. (top menu of VBA project window)

Ok i did that and am getting the error message attached. it says the file isn't there but it is in my Documents folder

Sub Button1_Click()
Dim accessapp As Object
Set accessapp = CreateObject("access.application")

accessapp.opencurrentdatabase ("Documents\test.accdb")
accessapp.UserControl = True
End Sub

 

[Isaac]

Documents\test.accdb isn't a valid path

FYI, mine is something like this: C:\Users\MyUsername\OneDrive - My Company Name\Documents

Yours might just be: c:\users\yourusername\documents

But whatever it is, you have to type it in fully and correctly

 

[Emma35]

Documents\test.accdb isn't a valid path

FYI, mine is something like this: C:\Users\MyUsername\OneDrive - My Company Name\Documents

Yours might just be: c:\users\yourusername\documents

But whatever it is, you have to type it in fully and correctly

That worked great thanks....do you know if there's something i could add to the code to make the database open in a maximised window ?.

 

[Isaac]

Glad to hear it worked!

There are a couple ways to approach this, but one thing you can try is to run this code in the Load event of your startup Form: DoCmd.Maximize

 

[Emma35]

Thanks Isaac....this is actually an Excel sheet i'm working with so don't think the Access commands will work

 

[Isaac]

You have two options.

Either amend your excel code at the end to add this line:
accessapp.docmd.maximize

Or, go into your Access database. Open that startup form in Design view. Go to the Properties pane (be sure you are looking at the properties of the Form, not Detail or anything else). Find the Events portion of the properties. In the Load event, click the ellipsis button to the right of the empty property. That will take you into the Load event code. use: DoCmd.maximize. Debug, Compile, Save, Close

 

[Emma35]

You have two options.

Either amend your excel code at the end to add this line:
accessapp.docmd.maximize

Or, go into your Access database. Open that startup form in Design view. Go to the Properties pane (be sure you are looking at the properties of the Form, not Detail or anything else). Find the Events portion of the properties. In the Load event, click the ellipsis button to the right of the empty property. That will take you into the Load event code. use: DoCmd.maximize. Debug, Compile, Save, Close

Thanks for your help Isaac......much appreciated. Thanks also to Ranman and Arnel....you guys have dug me out of many a hole

 

[Isaac]

Thanks for your help Isaac......much appreciated. Thanks also to Ranman and Arnel....you guys have dug me out of many a hole

Glad to hear you got it working