I'm using Access 2003. In my database, I have a link to an Excel spreadsheet and I treat it like a table. The problem I am having is that I seem to be locked into this file at the location I first specified it to be (actually, it wasn't me; it was the developer before me). For example, if I link to it in C:\MyDirectory\file.xls how can I change this location? If I right click, no properties come up for it. If I hold my mouse over the file name where its listed (Table), I get a tooltips-type pop-up that says the location. But how can I change this? Furthermore, my ultimate goal is to have this location specified externally to my .mdb so I don't have to recompile should the location change.
To change the source of this linked file, go to Tools and then Database Utilities, and then Linked Table Manager. When the Linked Table Manager popup window appears, click in the check box to select the file that you want to change the link for and also click in the check box next to "Always prompt for new location" and then click OK. You will then be able to specify a different file and locaction to link to.
How often do you expect the file name and location to change?
If it's rarely, secure the menus so that only an administrator can get to the Database Utilities options. There are good walkthroughs here and at Microsoft.com that show you how to set up menu security.
If it changes as often as the user does, I would prompt the user for the file name and location and use code to import the file when it's needed.
Dim mySpreadsheet as String
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_mySpreadsheet", mySpreadsheetPathName , True >
You'll have to add all of the error handling - ie, what to do if the file isn't where the user says and how to deal with import errors (all tricks I learned from the gurus, here) - but once the spreadsheet is in your database as a table, you can do whatever you need to while the users can continue playing with the spreadsheet itself.
You may be hearing more from me on this later, or may not be. Currently, this project is pending customer approval. I posted a new thread this a.m. asking for advice on estimating my time. Maybe I'll never even get to work on this..., sad - it seemed interesting!