Access Database command button to open external database

[ttbeverley]

Gday Guys,

I have an issue, well i have lots, but only the one im dealing with right now.

I have an access database that i have made with a form containing buttons, i would like these buttons to be able to link to other databases (external) using VB code, what i used to use was:


Private Sub ear_Click()
On Error GoTo Err_ear_Click
Dim x As Integer
Dim AppName As String
AppName = "C:\Program Files\Microsoft Office 2003\OFFICE11\Msaccess.exe \\serveraddress\database.mdb"
x = Shell(AppName, 1)
Exit_ear_Click:
Exit Sub
Err_ear_Click:
MsgBox Err.Description
Resume Exit_ear_Click

End Sub

This worked fine in office 2000 and prior, but have recently been forced to update to 2003 as the minimum, so these buttons have stopped working properly.

The other issue i have is that the reference to the installed office (msaccess.exe file), i would like to remove this reference completely, so as its not linked in the same manner due to the difference in files needed between pcs on the network.....

If you have any idea of somethign i can try, your help will be greatly appreciated.

Thanks for your help.

 

[wiklendt]

this works for me in 2007 to open another DB using the current install of Access (no matter what version). i have it in a standard module, but you can have it in a form module:

after the "Option Explicit" but before code...

Const cstrClientFEPath = "C:\Program Files\RLSODB\"
Const cstrFEFile = "ORDERS_ReferenceLabs_fe.mdb"

then in the subroutine or function:

    Dim intX As Integer             'Utility var
    Dim strCommandLine As String    'Application to run and file to open
    Dim strMSAccessAPP As String    'Folder path of Access Executable
    
    strMSAccessAPP = SysCmd(acSysCmdAccessDir) & "MSAccess.exe"
    
    'Build string
    strCommandLine = strMSAccessAPP & " " & Chr(34) & cstrClientFEPath & cstrFEFile & Chr(34)
    
    'Open FE .mdb
    intX = Shell(strCommandLine, 3)
    
    'Close this .mdb
    DoCmd.Quit

...and it is part of a longer code which tests for the FE version and updates it from the server if it's old. here's a more detailed discussion (edit: you can skip page 1 without missing too much, i guess) i recently had on AWF on that.

 

[ttbeverley]

Thanks for your help, havent had a chance to look in to it just yet, so will let you know when its tested, hopefully with good news.

Thanks for the link as well.

Tristan