Looking to automatically re-link tables in Access

  • Thread starter Thread starter FmEdit
  • Start date Start date
F

FmEdit

Hi,

Using Office 2007

Looking for a way to automatically re-link access database tables.

I currently have a database (IBD.MDB) that has links to 30+ Excel
files

Excel files are auto updated on a daily basis

The links to IBD.MDB are C:\Materials\Accss (Master)

My problem is I have 2 othe MDB files with the same name and uses the
same files, 1 is on a network server and the links are O:\Access, the
other is on another site and the links are J:\Access

To maintain this I have to manually copy any querie changes or new
changes from the master to the other 2 IBD.MDB files

If I add an additional table then I have to manually link this to the
appropriate drive

It is not possible to change the locations of all 3 into 1 master
location.

My question is, is there any way to autonmatically re-link when
changes have been made or additional files have been linked

TIA for any help on this ...

Raymond Allan
 
SQL Server Integration Services is literally designed to import a
bunch of spreadsheets without writing any code

If you're serious about doing it in Access, I'd add a reference to
'Windows Script Host' and then do something along these lines:

(WARNING AIRCODE)

Dim wsh As New IWshRuntimeLibrary.FileSystemObject
Dim fld As Folder
Dim fil As File

Set fld = wsh.GetFolder("O:\Spreadsheets")

For Each fil In wsh.GetFiles
If Right(fil.Name, 4) = ".XLS" Or Right(fil.Name, 5) = ".XLSX"
Then
DoCmd.TransferDatabase acImport, etc, etc, acTable, fil.Path +
"\" + fil.Name, Replace(Replace(fil.Name, ".xlsx", ""), ".xls", "")

End If

Next fil
 
FmEdit said:
Hi,

Using Office 2007

Looking for a way to automatically re-link access database tables.

I currently have a database (IBD.MDB) that has links to 30+ Excel
files

Excel files are auto updated on a daily basis

The links to IBD.MDB are C:\Materials\Accss (Master)

My problem is I have 2 othe MDB files with the same name and uses the
same files, 1 is on a network server and the links are O:\Access, the
other is on another site and the links are J:\Access

To maintain this I have to manually copy any querie changes or new
changes from the master to the other 2 IBD.MDB files

If I add an additional table then I have to manually link this to the
appropriate drive

It is not possible to change the locations of all 3 into 1 master
location.

My question is, is there any way to autonmatically re-link when
changes have been made or additional files have been linked

TIA for any help on this ...

Raymond Allan
Click to expand...
 
Back
Top