Prev: owc10.dll version 1.2 error on loading Access 2007
Next: Equipment Database
From: FmEdit on 3 Mar 2010 09:11
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
From: a a r o n _ k e m p f on 3 Mar 2010 11:08
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




On Mar 3, 6:11 am, FmEdit <raymond_al...(a)blueyonder.co.uk> wrote:
> 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

From: De Jager on 13 Mar 2010 12:50

"FmEdit" <raymond_allan(a)blueyonder.co.uk> wrote in message
news:34e2fdcc-e2db-4ae1-bda9-972621b62579(a)a18g2000yqc.googlegroups.com...
> 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

 | 
Pages: 1
Prev: owc10.dll version 1.2 error on loading Access 2007
Next: Equipment Database