Access 2003 query to Excel 2007 problem?

  • Thread starter Thread starter EPP
  • Start date Start date
E

EPP

I have a query that I'd set up a while back for a user on an Access
2003 db. The main table has keys that point to other tables (schools
and departments). She needs the results in Excel, so we have been
exporting it directly to an excel file (v97-2003), which has been
working just fine. However, the other day when I was working with her
on this year's stuff, we'd run it once as a test, it worked fine, then
I made a minor change to add a field, and re-ran it, and got quite
different results.

The first time we ran it, the resulting Excel file had "resolved" the
keys as it were, so that the resulting Excel file showed the actual
school/department names. However after saving it in Access and
exporting it to Excel again, the names were NOT resolved, but rather
showed the actual key numeric values instead. Same query, just resaved
after adding one field. It looks fine in Access 2003, tho. None of the
table(s) structure has changed.

Here's the kicker. We have just upgraded to Office 2007, and many of
our users still have both Access 2003 and 2007 on their systems-we
know that's been known to cause weirdnesses from time to time, and
mostly have been removing A2007 if the person doesn't need it. I'm not
sure if she does or not, sorry, but *I* do. The Excel file was viewed
in Excel 2007.

Has anyone seen this or heard of anything like this? Clearly something
fubar'd the original query, but it was run in A2003, so not sure
whether the problem was in Access or Excel, unless she does have both
Access 2003 AND 2007--I did try her query on my system, and it didn't
work for me either, but then, I do have both versions of Access.

TIA

elaine
charlottesville, va
 
Does the table have lookup fields to other tables? Tha't my guess. Those
things are evil and cause all sorts of weird problems.
 
Does the table have lookup fields to other tables? Tha't my guess. Those
things are evil and cause all sorts of weird problems.
Click to expand...

It does, Jerry, and I agree, but I didn't create it :) That said, it
worked fine in the all-Office 2003 products, as well as before I
changed the query in Access 2003. This is probably the first time I'd
had to change that particular query since I had O2007 installed
earlier this year--the query is an annual one.

THAT said, I personally believe that O2007 is evil :) Not to mention
very real issues when both versions, or at least selected products,
are on the same system. Unavoidable in this case, tho :(

Is there any way to kill the keys as lookup fields? I know I can
create a query that will populate the department titles (from the
other tables), would that do it? Just don't want to create any kind of
ripple effect if the key values in the main table are affected or
changed.

elaine
 
I'd recommend the proper query that links the tables instead of the lookup
bringing in the expected values. It should work just fine for any future data
changes.
 
I'd recommend the proper query that links the tables instead of the lookup
bringing in the expected values. It should work just fine for any future data
changes.
Click to expand...

Knowhachamean, I personally never use the lookup, but sadly, I'm stuck
with this. Is there anything else that can be done (otherwise) to fix
this? It did work in excel 2007 until we had to change the query,
after all.

elaine
 
Back
Top