linked table manager command

Question

I know they is a way to do this as have seen it before on databases, but my knowledge doesn’t go as far as creating a command button function for it.  Able to create the more simpler commands but this may go beyond my limitations, but always willing to learn from help given.

I have about 50 imported tables from Excel and rather than using the ‘external data’, ‘linked table manager’, I what to have within my current menu a command button that once clicked a list of all imported files pops up and you can select and update the relevant files required (basically the same options as the linked table manager pop up).  Or is there a better route?

Can someone help?

Thanks

Answer 1

Why not just create a button that runs Linked Table Manager?

DoCmd.RunCommand acCmdLinkedTableManager

Answer 2

It sounds as though you are talking about linked rather than importedtables.  If so you could create a dialogue form with a list box, lstLinkedTables say.  Add the following function to the form's module:

Private Function GetLinks(ctrl As Control)

    Dim tdf As DAO.TableDef

    ctrl.RowSourceType = "Value List"

    For Each tdf In CurrentDb.TableDefs

        If tdf.Connect <> "" Then

            ctrl.AddItem tdf.Name

        End If

    Next tdf

End Function

and in the form's open event procedure put:

   GetLinks Me.lstLinkedTables

Open the form in dialogue mode from a button on some other form.  The list box will list all linked tables.  If you only want to list those linked to Excel files and not to any other file type, amend the code to:

    If InStr(tdf.Connect, "Excel") <> 0 Then

This assumes that no other non-Excel files to which you have linked contains the word 'Excel' in the file name of course.