By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,293 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

Dynamic Linked Spreadsheet?

P: 5
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!
Jan 28 '10 #1

✓ answered by 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)

Share this Question
Share on Google+
10 Replies


P: 5
Whoops, forgot to mention this is Access 2007 on Windows XP.
Jan 28 '10 #2

nico5038
Expert 2.5K+
P: 3,072
The variable currentdb.Name will return the database name and the full path.
So using:
Expand|Select|Wrap|Line Numbers
  1. Replace(currentdb.name,"<name of your accdb or mdb>","2008-01-28_data.xls")
  2.  
will give what you want.

Nic;o)
Jan 28 '10 #3

ADezii
Expert 5K+
P: 8,597
If you wish to dynamically translate
Expand|Select|Wrap|Line Numbers
  1. C:\YaDa\YaDa\YaDa\db_yyyy-mm-dd.accdb
TO
Expand|Select|Wrap|Line Numbers
  1. C:\Path\yyyy-mm-dd_data.xls
for each day, then
Expand|Select|Wrap|Line Numbers
  1. "C:\Path\" & Replace(Mid$(CurrentDB.Name, Instr(CurrentDb.Name, "db_")+3),".accdb","") & "_data.xls"
will do the trick.
Jan 29 '10 #4

P: 5
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!
Jan 29 '10 #5

ADezii
Expert 5K+
P: 8,597
Isn't that what post #4 does?
Jan 29 '10 #6

P: 5
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.
Jan 29 '10 #7

ADezii
Expert 5K+
P: 8,597
You can programmatically create the Link with VBA code.
Jan 29 '10 #8

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 29 '10 #9

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 30 '10 #10

P: 5
Thank you everyone. Given my exact situation these solutions won't work (since the database is read only), but I certainly have ideas now!
Feb 2 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.