ODBC link from Access to Excel based on Query

vavs

Well-known Member
Joined
Jul 2, 2004
Messages
514
I have a table in Access 2007 and Access 2003 which contains the YTD sales information for my company. I import the file from a csv file, then I have a query that adds month and year fields based on an existing field. Finally, I use a make table query to create the final table result. From this "base table", I run a query that creates a table that limits the results to each specific sales person. I have approximately 15 queries. On the Excel side, I have pivot tables based on the query results of each sales person. I am having two problems with this configuration. First, I need to run an import and two queries just to get the base table. Secondly, when I open the Excel spreadsheets and refresh the pivot tables, I am not getting the results I look for. Often I do not have any update to the pivot table. I then go in and reestablish the ODBC connection and I get an update. Is there any way around this?

Can I update the pivot tables by calling the query in the Access db? How do I retain the ODBC connection so that I don't have to update it each time?

Thanks in advance for your input.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Watch MrExcel Video

Forum statistics

Threads
1,127,334
Messages
5,624,088
Members
416,010
Latest member
NJT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top