How to re-link excel files everytime? (1 Viewer)

[prabha_friend]

I want the access database to show a FileOpen Dialog each and everytime an access database opened to relink and refresh the Excel Sheet to link.

 

[GPGeorge]

You can open a form to support the appropriate function when the accdb is loaded. There are two ways to do that, one using the AutoExec macro, the other by designating that form as the start-up form for the accdb.

 

[The_Doc_Man]

This usually means you would have some kind of opening form or opening macro that calls a dialog to ask for that linkable file.

FileDialog object (Office)

Office VBA reference topic

learn.microsoft.com

If you have a switchboard or dispatcher form that gets launched then call from that form's OnLoad event. If you were using a macro it would probably need to do a RunCode action to call a function because macro RunCode action doesn't call subs, only functions.

 

[prabha_friend]

How to relink the files programmatically? Thats where the complexity lies...

 

[The_Doc_Man]

Look at this article:

Dynamically relink Access tables - Split Database

NO HELP REQUIRED TODAY, IM JUST PROVIDING INFORMATION SO THAT OTHERS CAN USE. I have a split database with the tables in an Access database (2013) as the backend and all the forms and queries in another for the front end. The system will be distributed to various users on different setups for...

www.access-programmers.co.uk

 

[prabha_friend]

Is there a way to control the linked table manager programmatically?

I am looking for an easy option like "Relink on Database Open"

 

[prabha_friend]

I remember there was an option present in earlier versions...

 

[GPGeorge]

I remember there was an option present in earlier versions...

Did you look at the code posted by The_Doc_Man yet?

 

[prabha_friend]

Did you look at the code posted by The_Doc_Man yet?

Yes I did. But I doubt whether the excel files also need connection strings...

 

[ebs17]

DoCmd.TransferSpreadsheet method (Access)

Office VBA reference topic

learn.microsoft.com

This allows you to import an Excel table, but also link it. Before linking, the existing link must be deleted.

How exactly does the new Excel table differ from the existing one?
If the name of the workbook and the name of the table are the same, you can simply overwrite the old workbook with the new workbook (=> same path), and the existing link will work without any further action.