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
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
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
MsgBox "The date you entered did not have the correct amount of characters, try again"
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.
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.
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).
Sub Map(blLocal As Boolean)
Dim strFile As String
Dim tblDef As DAO.TableDef
Dim tblDefs As DAO.TableDefs
On Error GoTo MapError
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 & " ..."
tblDef.Connect = strFile
Me.Label1.Caption = "Done."
Me.Label1.Caption = "Unexpected error!"
Private Sub cmdNet_Click()
Private Sub cmdLocal_Click()
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.