• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 499
  • Last Modified:

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
0
johnnywong71
Asked:
johnnywong71
  • 4
  • 2
  • 2
  • +2
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rey Obrero (Capricorn1)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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now