Prompt for new location of Linked Table

[jprochelle]

I have a linked table ...an Excel file called "Business Leaders" on a share drive. Every month, new data comes in and there is a new spreadsheet. Same format, different data. What coding do I need to use to set it so that when the user opens the database, a prompt is made for the new linked table location/data to be updated. Somewhat like when you click on properties and put "always prompt for new location"...

Thanks!

 

[PHV]

Take a look at the AutoExec macro.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jprochelle]

How do I set the Macro to go to: File, Database Utilities, Linked Table Manager, and lastly, click the Always prompt for new location box so that the only thing the user has to do is browse to find the new location of the file.

Thanks.

 

[PizMac]

I think you need to run a module in the autoexec similar to the example in the NWind database for re-linking the tables at start up. It's cumbersome but will come in useful later. If you get totally stuck I'll try and send an e.g.

 

[ClydeDoggie]

Just a suggestion:
If you make the name and location of the Excel file the same each month you won't have to worry about relinking. If you don't want to do this because you keep them as history I might suggest something like this - Each month you copy the new file into ...\CurrentData\ThisData.xls and move the previous ThisData.xls into a history folder with a new name like JulyData.xls.
I just envision an ongoing nightmare training new users how to use the Linked Table wizard and counting on users to do it each month.