Dynamic Linked Spreadsheet?

[laughlin]

Hello,

I was wondering if it were possible in Access to make a linked spreadsheet's path be based upon the filename of the Access database that it is in.

For instance, if the database is db_2008-01-28, I'd want the linked spreadsheet path to be c:\path\2008-01-28_data.xls. This way, I could create dozens of small (<1MB) databases with different names that would be based on their respectives day's data.

Alternatively, if someone could help me think of a better solution that would be great too. Essentially I have a database that linkes to daily data in the form of excel files. The excel files are too many and too large each to have them all in the database. The database is essentially just a data crucher that takes the daily data that it's linked to, does joins/filters/etc., then spits out one query that a pivot table in an excel application I've developed makes use of.

Further complicating things is that no one has write access to the directory that the database is in. That's why I need the database to update its linked table paths by itself whenever the main query is called from the excel application.

Thank you for any help!

[laughlin]

Whoops, forgot to mention this is Access 2007 on Windows XP.

[nico5038]

The variable currentdb.Name will return the database name and the full path.
So using:

Replace(currentdb.name,"<name of your accdb or mdb>","2008-01-28_data.xls")
 

will give what you want.

Nic;o)

[ADezii]

If you wish to dynamically translate

C:\YaDa\YaDa\YaDa\db_yyyy-mm-dd.accdb

TO

C:\Path\yyyy-mm-dd_data.xls

for each day, then

"C:\Path\" & Replace(Mid$(CurrentDB.Name, Instr(CurrentDb.Name, "db_")+3),".accdb","") & "_data.xls"

will do the trick.

[laughlin]

Hey guys,

Thank you for the replies. However, I realize my original question was worded confusingly.

When I say that I want to make a linked spreadsheet's path be based upon the filename of the Access database that it is in, I'm talking about the path to the spreadsheet in Access. So in this database: db_2008-01-28, I want the actual link to a spreadsheet (c:\path\YYYY-MM-DD_data.xls) to dynamically update based on what the database is named. So if I were to close the database, rename it to db_2009-04-15, then I when I open it again I want the linked spreadsheet to be c:\path\2009-04-15_data.xls. I don't think I can use a macro for this because I need this solution to work in a directory where the user (everyone but me) does not have write access.

So I'm not trying to do any spreadsheet renaming. I'm trying to make the link in the database be variable- i.e. based upon the filename of the database it's in.

Thanks!

[ADezii]

Isn't that what post #4 does?

[laughlin]

That will certainly help me to create the proper string, but when I go in to the linked table manager I don't see a place where I could use such code. As far as I can tell I can only manually update the link to the spreadsheet. Instead, I want it to self-update based on the filename (using the code you gave).

Does that make sense?

Thank you.

[ADezii]

You can programmatically create the Link with VBA code.

[nico5038]

See my "Function fncRelink()" code in my last post in http://bytes.com/topic/access/answer...r-password-vba

Using the replace() will enable you to relink your excel tables.

Nic;o)

[NeoPa]

That code should indeed give you what you're looking for. Let us know if you manage to get that to work. I'm not sure if the fact that the database is read-only may interfere with that working.

[laughlin]

Thank you everyone. Given my exact situation these solutions won't work (since the database is read only), but I certainly have ideas now!