Changing Linked Table Lookup path/name

scrtchmstj · #1 (**permalink**) 11-05-10, 17:28

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

tcace · #2 (**permalink**) 11-05-10, 18:45

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.

scrtchmstj · #3 (**permalink**) 11-10-10, 14:38

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

DCKunkle · #4 (**permalink**) 11-10-10, 18:01

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.

Sinndho · #5 (**permalink**) 11-11-10, 12:14

The TempSourceTableName tabledef is not allocated anywhere in your code. You must allocate it (Set TempSourceTableName = ...) before you try to use it.

scrtchmstj · #6 (**permalink**) 11-15-10, 10:08

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.

scrtchmstj · #7 (**permalink**) 11-15-10, 12:39

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

DCKunkle · #8 (**permalink**) 11-16-10, 10:08

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.

tcace · #9 (**permalink**) 11-16-10, 10:25

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech