Dynamic OLE link to Excel spreadsheet - is it possible? [ACC2010]

[jonathanchye]

I need to create a spreadsheet data entry form which contains certain formatted cells like what you can do in Excel. For example record 5 of column 1 value would be record 2 - record 1 of column 1. In excel you just click on the cell and enter something like " =A2-A2" but you can't do that in Access...

Hard as I try there's no way to do this inside of Access.

So, I am wondering if it is possible to create a dynamic link inside Access to Excel? For example when users wants to enter values they open a form in Access which contains a direct link to Excel spreadsheet - basically using Access as a window to Excel interface wise.

I would basically still need to extract information from the spreadsheet though.

Is this even possible?

 

[jonathanchye]

Did some digging and I've found out due to certain legal issues you can't edit linked Excel spreadsheets in Access anymore

 

[Access_guy49]

The best thing I can think of that would get you close to the function of Excel would be to create a form with calculated fields and have code populate that into a table.

The difficult part is that while excel has a defined location for a value, Access does not.
What i mean:
In excel, the value in Cell A1 is always the same. So calculations using cell A1 will always yield the same result.
In access, records have no set order and so to say "Take the top most record" will not always be the same value, making calculations more difficult.

What you can do is create an unbound form and populate it with data from your database using VBA code, this allows you to requery your data and make sure you get the record you need. Then through code you can do calculations and even store those values. (Though i'm not a fan of storing calculated values)