Opening Access From Excel

[yoritomo]

Hi all, I have a program in which I would like to open an access database (which has a form on open). however the code I have only works every second time I run the code, and to be honest, I have no idea why. I have tried multiple things, but none of them work (any more than every second try). If you can see a way to do this it would be great, heres what i have tried that came close.

stDocName = "FrmBookIn"
    
    stLinkCriteria = "[BookIn Referance]= 0108061500"
    Shell "msaccess.exe """ & Path & """", vbMaximizedFocus
    Access.DoCmd.OpenForm stDocName, , , stLinkCriteria

Shell "msaccess.exe """ & Path & """", vbMaximizedFocus
    Wait = Timer
        While Timer < Wait + 3
            DoEvents  'do nothing
        Wend
    Access.Forms![FrmBookIn]![BookIn Referance].SetFocus
    Access.DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

 

[ByteMyzer]

Within Excel's VBA, you need to create an Access Application object and do the form manipulation from the object.

Try modifying your code to something like:

Public Function MyAccessFunction()

Dim msApp As Object

Dim Path As String
Dim stDocName As String
Dim stLinkCriteria As String
Dim Wait As Single

stDocName = "FrmBookIn"
stLinkCriteria = "[BookIn Referance]= 0108061500"
Path = [b]"C:\MyDatabase.mdb"[/b] ' Change this to the actual database path

Set msApp = CreateObject("Access.Application")
msApp.Visible = True
msApp.OpenCurrentDatabase Path
msApp.DoCmd.OpenForm stDocName, , , stLinkCriteria

Wait = Timer
    While Timer < Wait + 3
        DoEvents  'do nothing
    Wend
msApp.Forms("FrmBookIn").[BookIn Referance].SetFocus
msApp.DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Set msApp = Nothing

End Function

See if this works for you.

 

[yoritomo]

Thanks for the response
I managed to get it sort of, its a work around, but it works hehe
I get the form to open when the database opens (its pretty much the only form anyway, everything else can be accessed form it) and for it to open with the find box opened. And Excel runs this code.

    Dim bookIn As String
   
    bookIn = Selection.Value
    bookIn = GetLastName(bookIn)
   
    'Opens Microsoft Access
    Shell "msaccess.exe """ & Path & """", vbMaximizedFocus
         
    SendKeys bookIn & "{ENTER}"

I'll give your code a whirl as well and tell you the results

*Edit*
Seems to fail on the Open Form part