Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Relinking a single table (XP/2003)

    I am having difficulty trying to relink a specific table via visual basic. I used the following code from previous post similar to my current challenge"
    'Start Code
    Public Function RefreshLinks(strFileName As String) As Boolean
    ' Refresh links to the supplied database. Return True if successful.
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strTableName As String
    Dim Err As Integer

    strTableName = "tblBaseline"
    Set dbs = CurrentDb
    dbs.TableDefs.Delete strTableName
    Set tdf = dbs.CreateTableDef(strTableName)
    ' Set the properties of the new link
    ' and append to the tabledefs collection
    tdf.SourceTableName = strTableName
    tdf.Connect = "JetTable" & _
    ";DATABASE=" & strFileName & _
    "," & strTableName
    dbs.TableDefs.Append tdf

    End Function


    Private Sub Command0_Click()
    On Error GoTo Err_Command0_Click

    Dim strFullPath As String
    Dim strFileName As String

    strFullPath = "C:CostAllocation"
    strFileName = strFullPath & "dbBaseline" & Format(Forms![frmMain]![txtPreviousQtr], "MM_DD_YY") & ".mdb"
    RefreshLinks (strFileName)

    Exit_Command0_Click:
    Exit Sub

    Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click

    End Sub
    'End Code

    I keep getting "Cannot find installable ISAM". Am I getting close here?

    Thanks in advance for your assistance.

    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relinking a single table (XP/2003)

    Your Connect string is not quite correct. Change

    tdf.Connect = "JetTable" & _
    ";DATABASE=" & strFileName & _
    "," & strTableName

    to

    tdf.Connect = ";DATABASE=" & strFileName

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relinking a single table (XP/2003)

    By the way, you don't need to delete and recreate the table, it is sufficient to set the Connect property and refresh the link. You can change the RefreshLinks function to
    <code>
    Public Sub RefreshLinks(strFileName As String)
    ' Refresh links to the supplied database.
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strTableName As String
    strTableName = "tblBaseline"
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTableName)
    ' Set the properties of the new link
    tdf.Connect = ";DATABASE=" & strFileName
    tdf.RefreshLink
    End Sub</code>

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Relinking a single table (XP/2003)

    Hans,

    Thanks for the heads up. I probably did not make it clear in my original post. Initially the database is linked to a table (tblBaseline) in C:dbBaseline01_31_07.mdb, and I want to link to the same table name but in a different database (C:dbBaseline03_31_07.mdb).

    When I ran the code graciously provided, I get "Item not found in collection" error at:
    Set tdf = dbs.TableDefs(strTableName)

    I am ignorant of how this tabledefs work.

    Any ideas?

    Thanks,
    Ken

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relinking a single table (XP/2003)

    TableDefs is the collection of all tables in a database. As usual, you can refer to a member of this collection by its index number:

    CurrentDb.TableDefs(3)

    or by its name:

    CurrentDb.TableDefs("tblEmployees")

    The error you get means that there is no table whose name is equal to the value of the variable strTableName. In your original code, you deleted the table; you shouldn't do that in this version, because it re-links the existing table instead of creating a new one.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Relinking a single table (XP/2003)

    Hans,

    You amaze me!

    Thanks so much for your help.

    Regards,

    Ken

  7. #7
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relinking a single table (XP/2003)

    I am wondering if I can use the RefreshLinks function in my application. There are 23 linked tables, 3 of which are linked to text files.

    Also, am I correct in thinking that strFileName is the database name the tables are linked to? If so I would obviously have to treat my text file links differently.

    How should I approach this?
    Thanks
    chuck

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relinking a single table (XP/2003)

    Yes, strFileName is the path+filename of the database containing the source tables.

    If you have linked text files, why would you want to change the links, and how?

  9. #9
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relinking a single table (XP/2003)

    The links should never change to the database tables or to the text files. I was thinking of refreshing the links just as a safeguard.
    Thanks
    chuck

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relinking a single table (XP/2003)

    If the path doesn't change, there is no real need to do that.

  11. #11
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relinking a single table (XP/2003)

    Very well. I cannot say I have ever known a link to fail anyway. I am retiring July 25th so I was just thinking of any last minute things I might do before I pass my work on to someone else.

    And just in case I have no further need to post on Woody's Lounge, I want to say it has been a tremendous experience and education to read the posts and receive the help I have needed. Many thanks to you Hans, and all the great folks who contribute to helping people like me.
    Thanks
    chuck

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relinking a single table (XP/2003)

    Thank you, it was a pleasure helping you. I hope that you'll have a great time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •