Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    Unanswered: Changing Linked Table Lookup path/name

    In VBA, how do I change the path for a linked table in my database? For example, I currently am linking to a CSV file with the date in the name. How would I change the date portion of the path so that the linked table would instead pull the same file but for a different day provided in a variable.

    I've never changed an existing linked table path in Access but there have been tons of times I could have used this. Does anybody have any sample code for how to do this? Is this as simple as changing a property value for the linked table? Thanks! Joshua

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    it is, as you say, as simple as changing a property or two.

    Using the TableDef object (a DAO object variable referring to a Table object).

    See here: LINK

    The property for the linked connection is: Connect
    The table in the foreign data source is: SourceTableName
    After changing, you need to use the Refresh method

    Use a loop to make a mass change: For Each tblDef in CurrentDB.TableDefs

    To get some insight to what the connection strings need to look like, use the immediate window to print the property of tables set different ways.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    I put together the following code but am still having some issues. This is intended to change the linked table names (same as before but with date value replaced with new date value which is prompted in iReply). At first I tried without using TempSourceTableName but got runtime error 3268 "Cannot set this property once object is part of collection." Then after I added TempSourceTableName, tried to run and got runtime error 91 "Object variable or With block variable not set". Any idea what I'm doing wrong? Thanks - Josh

    Code:
    Public Function LinkToHome()
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim iReply As String
        Dim TempSourceTableName As DAO.TableDef
        
        Set dbs = CurrentDb
            
    'Requests the date
        iReply = InputBox("Pull reports for what date? Formatted YYYYMMDD")
      
    'Checks the date value given to make sure it is 8 characters long
        If Len(iReply) = 8 Then 
            'Loop through tabledefs and refresh link to each table
            For Each tdf In dbs.TableDefs
                If tdf.Connect <> "" Then ' This speeds up the process
                    TempSourceTableName.SourceTableName = Left(tdf.SourceTableName, 31) & iReply & Mid(tdf.SourceTableName, 40, 100) 'This should change the table name
                    tdf.SourceTableName = TempSourceTableName.SourceTableName
                    tdf.RefreshLink 
                  End If
            Next tdf
        Else
            MsgBox "The date you entered did not have the correct amount of characters, try again"
        End If
    End Function

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would think deleting the link and recreating it would be easier than modifying the TableDef properties (never tried it). Any reason why you wouldn't delete and recreate?

    Code:
    docmd.DeleteObject acTable,"TableName"
    
    DoCmd.TransferText acLinkDelim,"","TableName","FileName"
    If you have created a File Specification you would put that in the second parameter.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The TempSourceTableName tabledef is not allocated anywhere in your code. You must allocate it (Set TempSourceTableName = ...) before you try to use it.
    Have a nice day!

  6. #6
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    The reason I don't delete and recreate is that it is not actually delimited. It is fixed width. Also, I'm pretty sure I've had issues before in access deleting and recreating an identical table in Access that is referrenced in a query.

    I think I recall there being some function where one can provide the import specifications for fixed width importing or delimited where you want to customize the imported field formats. I was just hoping I could change the referrence as it seems it would only be a referrence property.

  7. #7
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Sinndho,

    Where should I put that and what should I set it to? Sorry, I've not dealt with this before and have limited VBA experience. Thanks!

    Josh

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Creating a File Specification for a fixed width file is fairly easy. When you link to the file, the link wizard has an Advanced button. When you click it you have alot more flexibility for converting data types, defining field lengths, etc. When you have finished defining the file spec you can save it and then reuse the named spec in your DoCmd.TransferText statement.

    Also, the only time I have had problems with a query based on a linked table is when you try to desing the query when:

    1. The file that you linked to is no longer in the location where you linked to it.
    2. You have deleted the table just before you try to desing the query.

    I have not had any problems deleting and recreating the link when I don't try to design a query based on the table. I do ALOT of work with files and I use this strategy quite often. I have a database that has about 50 different file specifications that I link to depending on the file I am trying to process. It has worked well for me.

    I have never taken the time to try to change the Linked table's properties. So if that is the route you are going I won't be able to help you.

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Using code to change the linked table location is not uncommon and like many dev tasks, it is just one of several ways to get the job done.

    I like to work with a "local version" of the BE Data so that I can work without network, without affecting live data and when designing queries and reports, it's simply faster (how much faster depends on a lot of things, including a slow network).

    Another reason to relink a table in code is when the app is portable. A frequent example is when the app could be anywhere and simply needs the BE to be in the same folder. In this instance, I use code in the startup form to verify the tables have a valid link. If the link is not valid, then it looks for the BE in a typical location (such as the current folder, or a sub folder of the current app folder called Data or similar). If it is still not found, it then prompts the user for the location using a file open dialog. If that doesn't work, it thanks the user for playing and gracefully closes.

    I could come up with a half dozen more examples and various levels of complexity from simple relinking to managing multiple link to locations, to switching between ODBC, Access and other etc. etc. that I have in use.

    Here's a sample of a really basic procedure. Make sure you have DAO referenced (in code window, View menu then References).

    Code:
    Sub Map(blLocal As Boolean)
    
        Dim strFile As String
        Dim tblDef As DAO.TableDef
        Dim tblDefs As DAO.TableDefs
    
        On Error GoTo MapError
        
        DoCmd.Hourglass True
        
        If blLocal Then strFile = Nz(Me.txtLocal, "") Else strFile = Nz(Me.txtNet, "")
        If strFile = "" Then Exit Sub
        
        strFile = ";DATABASE=" & strFile
        Set tblDefs = CurrentDb.TableDefs
        
        For Each tblDef In tblDefs
            If tblDef.Connect <> "" Then ‘ an empty string means it’s a local table
                Me.Label1.Caption = "Rerouting " & tblDef.Name & " to " & strFile & " ..."
                Me.Repaint
                tblDef.Connect = strFile
                tblDef.RefreshLink
            End If
        Next
        
        Me.Label1.Caption = "Done."
        
    MapExit:
        DoCmd.Hourglass False
        Exit Sub
        
    MapError:
        Me.Label1.Caption = "Unexpected error!"
        Me.Repaint
        Resume MapExit
    
    End Sub
    
    Private Sub cmdNet_Click()
        
        Call Map(False)
    End Sub
    
    Private Sub cmdLocal_Click()
        
        Call Map(True)
    End Sub
    Two text boxes hold the "local" and "live" paths (which I set using code that calls on the built in file open dialog). Of course, they also default to the typical places so all I have to do is press the appropiate button.

    Although I run this in Acc2010, is should work all the way back to Acc97.

    Enjoy!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

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