Opening ms access db from current using vba

[deepPowdah]

Is there any way to open another Access (MS Access 2007) from an open MS Access 2007 Database? In earlier versions, you use the shell command but I don't think this is an option in Access 2007. Is it possible in Access 2010?



Thanks for your help.

[pbaldy]

What gave you the idea that Shell was no longer available? It is. You can also use FollowHyperlink.

[djoosten]

Why do you want to open the second database? Do you need to actually see the other open database or are you interested in just processing information from another database?

I haven't tried it but I don't see why the Shell command would not work as long as the program to open argument is pointing to a the full path of Access and the database. Shell is a part of VBA so that should not have changed with versions.

You might also want to take a look at the OpenDatabase method of DAO.

[pkstormy]

Using the FollowHyperlink as Paul suggested, I've used the following which has worked very well:

dim strFile as variant
strFile = "C:\FolderName\SomeMdbFile.mdb"
(or strFile = "C:\FolderName\SomeWordDoc.Doc")
(or strFile = "C:\FolderName\SomeExcelFile.xls")
Application.FollowHyperlink strFile

(note: to open a folder itself....strFile = "C:\FolderName")

[orange]

I have used the following to open a second database (Acc2000/Acc2003)

'---------------------------------------------------------------------------------------
' Procedure : testdb
' Author    : Jack
' Created   : 12/8/2009
' Purpose   : Test opening second A2003 database.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Sub testdb()

Dim dbsCurrent As DAO.Database
Dim dbsSecond As DAO.Database
Dim tbl As DAO.TableDef
   On Error GoTo testdb_Error

Set dbsCurrent = CurrentDb
Set dbsSecond = DBEngine.Workspaces(0).OpenDatabase("c:\users\jack\a2k\db1new.mdb")
Debug.Print dbsCurrent.name
Debug.Print dbsSecond.name

For Each tbl In dbsSecond.TableDefs
Debug.Print "  " & tbl.name
Next
Debug.Print Now()

   On Error GoTo 0
   Exit Sub

testdb_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testdb of Module Module5"

End Sub