Change path of Excel link in Access

[SSweez]

I need some help as I just can't seem to find a good answer to what
should be an easy question (but maybe not). If I have an Access
database with a link to an Excel file how can I change that path using
VBA? Say I have a linked table called "Address" and I want to change
the linke from: C:\2005\Address.xls to C:\2006\Address.xls. Assume
the first file is not deleted and the tab name is the same, say
"Street." Is there any easy was to do this in VBA????

 

[Douglas J. Steele]

Delete the TableDef object, and create a new one, changing the Connect
property.

 

[SSweez]

Thanks. Can you provide some sample code that would do this?

 

[Douglas J. Steele]

Are you letting the first row of the spreadsheet serve as field titles? If
so, you want something like:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef

Set dbCurr = CurrentDb()
dbCurr.TableDefs.Delete "MyLinkedTable"
Set tdfCurr = dbCurr.CreateTableDef("MyLinkedTable")
tdfCurr.Connect = "Excel 5.0;HDR=YES;IMEX=2;" & _
"DATABASE=C:\MyFolder\MySpreadsheet.XLS"
tdfCurr.TableDefs.Append tdfCurr
tdfCurr.TableDefs.Refresh

If that doesn't work, link through the GUI (File | Get External Data | Link
Tables) and look at the Connect property of the table to see what exact
string you need to use.