如何用Ms Access VBA打开外部数据库表的数据表及设计视图？

Got it, let's tackle this problem step by step. The DoCmd.OpenTable method only works for tables in your current Access database, so we need a couple of reliable workarounds to open tables from an external database in normal (datasheet) or design view. Here are two approaches tailored to your form scenario:

This method creates a temporary linked table in your current database, lets you open it like a local table, and optionally cleans up the link afterward.

1. Link the external table first
   Use DoCmd.TransferDatabase to create a link. Make sure to avoid duplicate links by checking if the linked table already exists first:

   Dim strExternalDB As String
   Dim strTableName As String
   Dim strLinkedTableName As String
   
   ' Get these values from your form controls
   strExternalDB = Me.cboSelectedDatabase.Value ' Your database path dropdown
   strTableName = Me.cboSelectedTable.Value     ' Your table dropdown
   strLinkedTableName = "TempLinked_" & strTableName ' Unique name for the link
   
   ' Delete existing link if it exists
   If DCount("*", "MSysObjects", "Name='" & strLinkedTableName & "' AND Type=4") > 0 Then
       DoCmd.DeleteObject acTable, strLinkedTableName
   End If
   
   ' Create the link
   DoCmd.TransferDatabase _
       TransferType:=acLink, _
       DatabaseType:="Microsoft Access", _
       DatabaseName:=strExternalDB, _
       ObjectType:=acTable, _
       Source:=strTableName, _
       Destination:=strLinkedTableName
   

2. Open the linked table in your desired view
   Now you can use DoCmd.OpenTable just like you do for local tables:

   ' Open in normal (datasheet) view
   DoCmd.OpenTable strLinkedTableName, acViewNormal
   
   ' Or open in design view
   DoCmd.OpenTable strLinkedTableName, acViewDesign
   

3. Optional: Clean up the temporary link
   If you don't want to keep the linked table around, add this after opening (or in a form close event):

   If DCount("*", "MSysObjects", "Name='" & strLinkedTableName & "' AND Type=4") > 0 Then
       DoCmd.DeleteObject acTable, strLinkedTableName
   End If
   

This method launches a new Access window, opens the external database directly, and loads the table in the desired view. It avoids modifying your current database with links.

1. Create a new Access instance and open the external DB

   Dim appAccess As Object
   Dim strExternalDB As String
   Dim strTableName As String
   
   ' Get values from your form
   strExternalDB = Me.cboSelectedDatabase.Value
   strTableName = Me.cboSelectedTable.Value
   
   ' Create a new Access application object
   Set appAccess = CreateObject("Access.Application")
   
   ' Open the external database
   appAccess.OpenCurrentDatabase strExternalDB
   
   ' Make the new Access window visible
   appAccess.Visible = True
   

2. Open the table in your desired view

   ' Open in normal (datasheet) view
   appAccess.DoCmd.OpenTable strTableName, acViewNormal
   
   ' Or open in design view
   appAccess.DoCmd.OpenTable strTableName, acViewDesign
   

3. Optional: Clean up (if needed)
   If you want to close the instance later, use:

   appAccess.CloseCurrentDatabase
   appAccess.Quit
   Set appAccess = Nothing
   

   But most users will want to keep the new Access window open to work with the table.

To make this work with your dropdowns:

1. Load tables from the selected external DB
   Add this code to your database selection dropdown's AfterUpdate event to populate the table dropdown:

   Dim cat As Object
   Dim tbl As Object
   Dim strExternalDB As String
   
   strExternalDB = Me.cboSelectedDatabase.Value
   Me.cboSelectedTable.Clear ' Clear existing options
   
   ' Use ADOX to get table list (exclude system tables)
   Set cat = CreateObject("ADOX.Catalog")
   cat.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strExternalDB
   
   For Each tbl In cat.Tables
       ' Skip system tables and non-table objects (like queries)
       If tbl.Type = "TABLE" And Left(tbl.Name, 4) <> "MSys" Then
           Me.cboSelectedTable.AddItem tbl.Name
       End If
   Next tbl
   
   Set tbl = Nothing
   Set cat = Nothing
   

2. Add buttons to open the table
   Add two buttons to your form: "Open Datasheet View" and "Open Design View". In each button's Click event, call the code from either Approach 1 or 2 above.

Notes #

• For older .mdb databases, use Provider=Microsoft.Jet.OLEDB.4.0 instead of the ACE provider.
• Ensure the external database path is correct (use a file picker control if you want to make selecting the DB easier).
• Test for permissions: your app needs read/write access to the external database folder.

内容的提问来源于stack exchange，提问作者Miguel Velez