From Excel, link to specific Access field

[TomM]

From inside Excel 2010, I want to link to a specific field in an Access 2010 db. I have looked at Excel's Get External Data / From Access, but it does not seem to do what I want.

In Excel, I will enter a function into a cell to specify the Access file, table, record and field to retrieve. The result will be displayed in Excel. Perhaps it would update every time I open the Excel file.

Can this be done?

Thanks,
Tom

 

[xenou]

In DB-speak you simply want to run a query on the database. If you want it to be completely flexible with any database, table, field and criteria, you'll need to script it. You can have a look here at using DAO or ADO:

http://www.erlandsendata.no/english/index.php?t=envbadac
http://www.datawright.com.au/excel_resources/excel_access_and_ado.htm

 

[TomM]

Hi xenou-

Having little VBA experience, I am struggling with your script version. I will be pointing to only one DB and it will always be Access, if that makes things easier.

Imagine a custom Excel function of form:
GetAccessData(AccessFileName, TableName, Item#, FieldName)

- VBA code would implement the connection to database and define the custom function.
- Each row in my Excel table corresponds to a record in Access, with Item# being a unique identifier.
- In my Excel table, column A contains the Item# (manually entered).
- So, if I want to display a value from Access in cell D7, I would insert the custom function with Item# pointing to cell A7 (the Item#) and specify the FieldName in Access that I want.

Is that possible? Does your method do essentially that, and I am missing it? User will be routinely inserting new calls to Access, so I want it to be reasonably easy to use.

Thanks,
T

 

[xenou]

Getting information out of databases is a very common function that can be done dozens of different ways. If this particular setup is effective or not is hard to say without knowing more context.