Change path of Excel link in Access

  • Thread starter Thread starter SSweez
  • Start date Start date
S

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????
 
Delete the TableDef object, and create a new one, changing the Connect
property.
 
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.
 
Back
Top