Ms Access vba to open datasheet view of table in another database

Question

This statement will open the datasheet view of the named table in the current database:

DoCmd.OpenTable sTablename, acViewNormal

Is there a way to achieve the same result for a table in another database?

I have a form where I can select an Access database, then a pulldown menu is populated with all the tables in that database. I want to be able to select a table from that list and open the datasheet view of the selected table both in normal and design views.

Any help will be very appreciated.

Michael

Answer 1

It can be done in the following way.

1. Create a link table to another database.
2. Open the link table.

Private Sub OpenTableInAnotherDatabase()
    Dim sDatabasePath As String
    sDatabasePath = "another_database.accdb"
    
    Dim sTablename As String
    sTablename = "foo"
    
    Dim db As Database
    Dim tbl As TableDef
    
    ' Create link table
    Set db = CurrentDb
    Set tbl = db.CreateTableDef(sTablename)
    
    tbl.Connect = ";DATABASE=" & sDatabasePath
    tbl.SourceTableName = sTablename
    db.TableDefs.Append tbl

    ' Open link table
    DoCmd.OpenTable sTablename, acViewNormal
End Sub

You can delete the created link table with DeleteObject.

DoCmd.DeleteObject acTable, "foo"

If you intend to open the table in another window, the following code works.

' Note this variable is public so that the opened window doesn't disappear when the procedure exits. 
Public app As New Access.Application

Sub OpenAnotherDatabaseTable()
    ' Open another database in a new Access process
    app.Visible = True
    app.OpenCurrentDatabase "another_database.accdb"
    
    Dim sTablename As String
    sTablename = "foo"
    
    app.DoCmd.OpenTable sTablename, acViewNormal
End Sub