Using EXCEL Formulas with data stored on MS ACCESS: possible?

[zazathedog]

Hi there,

I am trying to find out if it possible to directly "access" (no play on words here) data stored on MS ACCESS by using Excel's formulas.

I have a set of tables stored on a Microsoft ACCESS 2010 database, and I want to use Excel to analyze that data, using formulas, such as SUMIF.

Is it possible, once I have established an Jet OLEDB connection from Excel to Access, to then directly exploit the data stored on Access without having to copy the data to Excel, and then use it.

I would like Excel to solely act as a tool to analyze, not to store data.

Cheers,

Thanks for helping me out!

 

[Trevor G]

This sounds as though you should look to set the queries in the database then run the queries from Excel.

If you don't want to extract data from the database into Excel why would you want to use Excel Formula when Access Formula should do what you want?

 

[zazathedog]

I want Excel to act as a Dashboard, so I will create and layout different charts in the workbook. I dont know much of ACCESS capabilities; could I create the charts from there and then link them to Excel?

The end-user is only having access to Excel, the Access database should solely be used to build the charts, and Excel should retrieve the end product.

 

[Trevor G]

I want Excel to act as a Dashboard, so I will create and layout different charts in the workbook. I dont know much of ACCESS capabilities; could I create the charts from there and then link them to Excel?

The end-user is only having access to Excel, the Access database should solely be used to build the charts, and Excel should retrieve the end product.

Look to utilise queries in Access then bring in the end result or use the External Data in Excel and create a query then save the query if it works for you then look to record a macro to run the query and that should work.

 

[zazathedog]

Thanks for your advice Trevor; even with SQL queries to retrieve the data, I would be storing it in Excel at some point right? What do you mean when talking about external data in Excel? Is it possible to create a direct link from Excel to Access? If I have to store the data in Excel at some stage, I may as well store the data directly in Excel and forget about Access.

I have to say that I had higher expectations about the possibilities Access can deliver, but it seems like its only really useful as a Database if several users are using the data for different purposes. Though I think Excel should not be used to store large quantities of Data, I don't see the point of going through the hassle of using Access if you need to copy/paste the data to Excel, and delete the data once exploited.

Cheers.

 

[MikeWard100]

Thanks for your advice Trevor; even with SQL queries to retrieve the data, I would be storing it in Excel at some point right? What do you mean when talking about external data in Excel? Is it possible to create a direct link from Excel to Access? If I have to store the data in Excel at some stage, I may as well store the data directly in Excel and forget about Access.

I have to say that I had higher expectations about the possibilities Access can deliver, but it seems like its only really useful as a Database if several users are using the data for different purposes. Though I think Excel should not be used to store large quantities of Data, I don't see the point of going through the hassle of using Access if you need to copy/paste the data to Excel, and delete the data once exploited.

Cheers.

Did you find out how to do this?
I agree with you in that I do not want to pull an entire table from Access into my Excel file just so I can get data from it. It makes the file way to big. I want a formula that would lookup a customer name from an Access table based on a customer number in Excel.

 

[zazathedog]

Did you find out how to do this?
I agree with you in that I do not want to pull an entire table from Access into my Excel file just so I can get data from it. It makes the file way to big. I want a formula that would lookup a customer name from an Access table based on a customer number in Excel.

Hi Mike,

This post is not that recent but if I recall correctly the issue was IF it was possible to use Excel's built-in formulas to query an Access Data Base directly. Unfortunately you cannot and need to use SQL queries or similar methods.

You can build SQL queries from Acces, Miscrosoft SQL query builder or manually.

Hope this helps.

 

[MikeWard100]

Hi Mike,

This post is not that recent but if I recall correctly the issue was IF it was possible to use Excel's built-in formulas to query an Access Data Base directly. Unfortunately you cannot and need to use SQL queries or similar methods.

You can build SQL queries from Acces, Miscrosoft SQL query builder or manually.

Hope this helps.

That helps, Zaza. Thank you.