As a general rule I store all my data in Access and analyse it using pivot tables. To get the data into excel, I tend to use OLEDB because i found the Access ODBC driver had too many syntax limitations.
So to get at your data in Access:
- select Data\Import External Data\ Import data.
- Select 'Connect to new datasource'
- In the wizard, use 'Other/Advanced', then select Jet OLE DB provider
- then browse to your database, put any login information in. I don't tend to change the advanced settings but you could set read only.
- Then click OK.
- In the next part of the wizard, don't connect to a specific table - it makes life more flexible
- You can then save the file somewhere so you don't have to go through these steps to connect to the same db everytime. You can save it anywhere but (annoyingly) Excel always saves a copy in your 'My Data Sources' directory anyway.
- When you click Finish, you'll be taken to select a specific table or query - NOTE, only tables and select queries show in the dialog, union queries don't - god knows why, but they don't!
- Select any one, go for small so that it's quick to move to the next step. Click OK
- On the next dialog, in the bottom right corner, click 'edit query'. From here you can either put the name of a table/query (including unions!) or just change command type to SQL and then you can write any sql that access would understand*. Click ok
- Click 'Create a pivot table report' and you are magically at step 3 of the pivot table wizard. Hooray
(* note that i thought it would understand any but since discussing this at dailydoesofexcel, i have noticed that some functions (NZ() and InStrRev() so far) don't work - i think this is because they are actually VB formulas that access pretends are its own..??)
And then to update your pivot, just use it as normal - click the little red exclamation mark. To edit the query OR the connection, just select the pivot table, then go Data\Import External Data\ Edit query which will give you the dialog from before when you edited the query. From here you can change the sql and change the connection string to point to a different db - just edit the database path in the middle of the string.
There you go easy peasy. To make life a little easier you can get the PivotPlay via Debra's site (http://www.contextures.com/xlPivotPlay01.html) but, unfortunately, it doesn't deal with OLE DB connections too well (when you save the connection changes, it appears to try to use ODBC and fails). There's also Rob's QueryEditor (http://vangelder.orcon.net.nz/excel/queryeditor.html) but it doesn't recognise pivots. So for the moment I'm still using the built in 'edit query' dialog.
The only thing you can't use with OLE DB is parameters, so to use them you're stuck with ODBC.
Hope that's some help
Rob