Linking Access records to Excel files

views:

answers:

Linking Access records to Excel files

I've been trying to create a complete system using MS Access, but i really need to use the functionality of excel spreadsheets.

I wish to do the following; when I create a record, i create a directory for that record, and copy an excel spreadsheet to that directory. The spreadsheet will be able to link to the corresponding record. So for example if I have a field 'Name' In the database record, the value of this field will show up on the excel spreadsheet.

Is it possible to do this?

Sounds overly complex and unmaintainable. Why not just embed a spreadsheet into your record?

http://support.microsoft.com/kb/209990

Nissan Fan 2010-04-14 17:41:11

Advice given like a person who has never actually tried what is being recommended -- OLE fields are highly problematic for a whole host of reasons, not least of which is bloat (though the A2007 Attachment field is supposed to obviate some of that).

David-W-Fenton 2010-04-14 17:55:17

+1 A:

It might help for you to explain your concept of "links" between Access rows and separate spreadsheets. That baffles me.

Rather than "link" each Access record to a separate spreadsheet, perhaps you could have the spreadsheet query the database to retrieve whatever updated information it needs.

Or use automation driven from Access to revise the spreadsheet.

Edit: To get start driving Excel from Access, look at Sample Excel Automation by Dev Ashish

HansUp 2010-04-14 18:34:51

Hi HansUp. What Im trying to do is update values in the record that would correspond in a spreadsheet. For example, currently I enter details into a spreadsheet such as name, address, etc... And I have linked Word documents that are automatically updated using these values. So a letter will automatically have the address fillled in etc ... What I want to do is use the access database to store such details, but automatically update relevant ms-office files. Hope that makes sense

glinch 2010-04-14 21:15:50

@glinch So could it work for you to have Access write updated values to the spreadsheet?

HansUp 2010-04-14 22:19:45

Yep, that sounds exactly what I am trying to do. That is within the realms of access?

glinch 2010-04-15 07:58:39

@glinch Look at the link in added.

HansUp 2010-04-15 14:43:08

Sounds like a horrible setup, what with having important data spread out across a number of file formats. I'd say make Excel or Word or Access your main data store and go with that, and then generate the output in the other apps based on the main data store. To me, it seems obvious that Access is the best way to store data, since it's a database! It also has the most advanced capabilities in terms of interfacing with the other apps.

David-W-Fenton 2010-04-15 19:48:06

Cheers for the link HansUp, that looks like what I'm after

glinch 2010-04-15 21:01:16

Hi David, what I would like to achieve is to use access as the main storage point for the data. This data (such as names/addresses etc..) would then be outputed/linked to word docs(letters etc..) and spreadsheets. To me that would appear to be a good way of integrating the various apps, and utilising each one. I dont intend to edit the data in word/excel, merely use the data in those apps. I hope/think that is also what you are recommending?

glinch 2010-04-15 21:12:14

I question the suggestion that the calculations cannot be done in Access and need Excel. Sure, there are some functions that Excel has that Access lacks, but you can use those in Access via automation without needing to actually have a spreadsheet in Excel autoamted by Access.

David-W-Fenton 2010-04-16 18:40:55

ansaurus

tags:

views:

answers:

Linking Access records to Excel files

related questions