How to get the name of Linked object?

I try to link a table from Excel.  So, I used this code:
      DoCmd.RunCommand acCmdLinkTables
But after that, how can I get the link object name?

After I link the data, I want to create the other query to filter the data and save into the master table.

Thanks for any help!

Johnny
LVL 1
johnnywong71Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:

using VBA codes

Dim td As DAO.TableDef

For Each td In CurrentDb.TableDefs
  If Len(td.Connect) > 0 Then
        Debug.Print  td.name
  End If
Next
0
 
jerryb30Commented:
mysysobjects table will contain info on linked tables and spreadsheets.



Select name, type, database from msysobjects where type = 6;
0
 
Leigh PurvisDatabase DeveloperCommented:
Are you talking about creating a linked table to an Excel worksheet?

DoCmd.RunCommand acCmdLinkTables
will only get you started in the wizard process - you'd have to create a table definition yourself to actually finish the process.

msysObjects indeed holds details about your tables and their connect strings.
You'd need to generate all that yourself.
Something like...

Sub AddLinkedExcel()

Dim db As Database
Dim tdf As New TableDef
   
    Set db = CurrentDb
    Set tdf = db.CreateTableDef("tblYourName")
   
    tdf.Connect = "Excel 5.0;DATABASE=X:\YourPath\TestEmp.xls"
    tdf.SourceTableName = "SheetName"
   
    db.TableDefs.Append tdf
    Set tdf = Nothing
   
End Sub
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
RohitPattniCommented:
jerryb30,

you are assuming that the table has been linked already. I believe the question is more like having issued the link table command how can johnny pass the path & file name for the file that he wants to link. perhaps someone on EE can tell him.

As for the query create a make table or append query in access UI which you can call via macros or VBA.

Rohit
0
 
Leigh PurvisDatabase DeveloperCommented:
"perhaps someone on EE can tell him"
Naaaa
0
 
RohitPattniCommented:
Use the following to link your spreadsheet

DoCmd.TransferSpreadsheet acLink, 8, "Tablename", "\\path\filename.xls", True, ""

Sorry to have offended you LPurvis.
0
 
johnnywong71Author Commented:
Thanks for all of your answer.

I cannot assume the path link until the user choose the path from the wizard.
After the user choose the file and link into the database, how could I know the name he/she choosed?

For example,
User click the button and enter into the link wizard.   He pick the file.  Can we display the file name in "msgbox chosedfilename"?

Thanks!
0
 
Leigh PurvisDatabase DeveloperCommented:
Noo not offended.  Just messin.
0
 
Leigh PurvisDatabase DeveloperCommented:
You can navigate to the file yourself (certainly don't need the Link Table wizard).

http://www.mvps.org/access/api/api0001.htm
0
 
johnnywong71Author Commented:
Thanks for all of you guys:

I find the way that caprion suggested:

Dim td As DAO.TableDef
Dim datLastUpdate As Date
Dim strNewLinkedFile As String
DoCmd.RunCommand acCmdLinkTables

For Each td In CurrentDb.TableDefs
  If Len(td.Connect) > 0 Then
        If td.LastUpdated > datLastUpdate Then
                datLastUpdate = td.LastUpdated
                strNewLinkedFile = td.Name
        End If
  End If
Next
MsgBox strNewLinkedFile & datLastUpdate

So, I know which one is the last one updated
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.