Linked Access query isn't updating

**jas.** · 06-23-2014, 05:21 PM

I'm not sure this is the right forum -- the Access forum seems to be about Access, which I'm not having a problem with.

The problem is this: I have a UNION query in Access, which puts together data from different sources into one view, and splits them into three categories using the Switch() function. Excel can't see a UNION. So I created a separate SELECT query, since Excel can pull them in as a data source.

I changed one expression in the Access query, so it now splits things into four categories rather than three. I run the UNION query, it's fine. I run the SELECT query, it's fine.

I pull it into Excel and it's still three categories. The fourth is gone; it's like it has reverted.

I created a new workbook and started over - new workbook, new connection, all is well, right? WRONG. It still is only using the three categories.

I restarted excel, created yet another new workbook and connected to a different query - just the regular SELECT query that is one of the sources of the UNION query. Still only returns the three categories.

This query is saved in Access, it works in Access, but it's like Excel is hanging on to its concept of what this connection is, and it's hanging on between workbooks, between sessions, everything. I can't figure out what to do to fix this. I compacted and repaired the db. I opened PowerPivot and wrote a query to pull everything from that query.

I'm looking in the "Existing Connections" dialogue, but I can't figure out how to basically delete them so that I can force it to refresh. Is that a path that could work? I mean, they're set to refresh, so I don't understand why, in a new workbook, the query wouldn't refresh itself.

Anyone have ANY idea what I should do here? It's driving me nuts.

**5631** · 06-24-2014, 04:43 AM

What's the number of rows?

**jas.** · 06-24-2014, 08:59 AM

Not many - like 2800 total, and only 20 or so are affected. But they're a significant percentage of the total, if we're adding up the figures they represent.

**jas.** · 06-24-2014, 09:25 AM

Update: I was mistaken in how I set up the query. There is a Switch function in there, but the expression that's causing me trouble is an Iif statement, and the problem appears to be that it's evaluating multiple fields. So it's basically this:

Iif(cm="X", "END", Iif(cm="Y", "FMP", Iif (fn="Z", "TBD", "OSI")))

As I mentioned, TBD is a subset of OSI.

I thought that basically when you have a data connection to Access, then Access would run the query, return the results, and Excel would just look at the results as data -- apparently there's something more going on there, and it's a query that Excel can't handle. I'll have to figure out a workaround, but I have no idea what it could be.