Link Table between MS Access files from MS Excel

Question

How can I create a Table link within a MS Access *.accdb file, linking to another table in an MS Access *.accdb file, but from VBA code within an MS Excel *.xlsx file.

---

Database1 in file1

Database2 in file2

Excel VBA Code in file3

Execute code in file3 to link a table of file2 to file1, so that it appears as linked table within file1.

---

Details:

I have a complex script creating a large table and join mappings based on a MS Excel Design, since it is more user friendly to design the things in an MS Excel Table. Everything works great so far, but I need the last step, LINK the original table.

Within MS Access I would do

DoCmd.TransferDatabase TransferType:=acLink, _
    DatabaseType:="Microsoft Access", _
    DatabaseName:=SRC_FILE_PATH, _
    ObjectType:=acTable, _
    Source:=SRC_TABLE_NAME, _
    Destination:=DESTINATION_TABLE_NAME

But since I am executing VBA Code within Excel, only using the connection to the database via a DAO.Database object, this command doesn't work.

Answer 1

Using the DAO reference library in Excel

Dim wrkDAOWorkspace As DAO.Workspace
Dim dbsDAODatabase As DAO.Database
Dim tdfNewLinkedTable As DAO.TableDef

    Set wrkDAOWorkspace = CreateWorkspace("WorkspaceForLinkCreation", "Admin", "", dbUseJet)
    Set dbsDAODatabase = wrkDAOWorkspace.OpenDatabase("c:\file1.accdb")

    Set tdfNewLinkedTable = New DAO.TableDef
    With tdfNewLinkedTable
        .Name = "TestTableLinked"
        .Connect = ";DATABASE=c:\file2.accdb"
        .SourceTableName = "TestTableLinkTo"
    End With

    dbsDAODatabase.TableDefs.Append tdfNewLinkedTable

Answer 2

Something like this should do it for you. Modify to suit your needs...

Private Sub Command1_Click()
DoCmd.SetWarnings False

Dim InputFile As String
Dim InputPath As String

InputPath = "C:\your_path_here\"
InputFile = Dir(InputPath & "*.xls")

Do While InputFile <> ""
        DoCmd.TransferSpreadsheet acImport, , InputFile, InputPath & InputFile, True '< The true is for column headers
        InputFile = Dir
Loop

End Sub