Excel shutting down while pulling access query as Pivot

[umasriram2]

Hi, I'm very new to access but somehow could figure out ways to get my output in access building queries with dlookup formula. However when I open up excel and try inserting a pivot table to get data from the access query, it gives me the error message that "Microsoft excel has encountered an error and needs to shut down". The error report refers acecore.dll error. Could someone help asap pl.....

 

[lemo]

are you in excel 2007?

not exactly sure what it is, but i would try, as a test, to use a simple basic query for your pivot table source. if it works, it means the problem is with the syntax and you may need to simplify your query somehow, if possible.

or maybe, instead of inserting pivot table and then selecting 'external source...', try to go to 'data' dropdown menu and get the external data from access.

not much help, sorry, but who knows.
l

 

[umasriram2]

Thank you...Though I've tried those options all of them crash the excel with the same error message "Microsoft excel has encountered an error and needs to shut down" pops up. However I'm able to do an export of the query. But I'm looking for a way to have a pivot that can auto refresh. Any thoughts....Also the query pulls the pivot in excel if I upload just one month data. However when I upload further months, it gives the same error message...Help pl!!!

 

[lemo]

these paths between Excel and Access are so weird and thoroughly beyond my comprehension.
for example, a few minutes ago i figured how to import access query into a pivot table with a resulting file about 8 times smaller than what i was doing before. yes!.. (you can try it too - use import data connection wizard and choose ODBC DSN)

are your data uniform? maybe there is some data mismatch? like month is text in one table and number in the other? or something along those lines..

 

[lemo]

ignore that ODBC DSN part - it wasn't that, it was just smaller query, the size reduction is almost negligible.. unfortunately..
l

 

[umasriram2]

Unfortunately still doesn't resolve the issue I checked the formats as well

 

[lemo]

i give up.. sorry..
since the elite of the forum keeps silence, maybe try other forums, google or yahoo excel groups or somesuch..
good luck.
l
p.s. one more idea - try running it on another computer, maybe it's a software issue, who knows..

 

[chergh]

Having a quick look at the thread this caught my eye:

Also the query pulls the pivot in excel if I upload just one month data. However when I upload further months, it gives the same error message

Have you checked how many rows are being returned by the query? If you are using 2003 or earlier and it is return 65k + rows it could cause it to crash. I don't really work much with databases these days so don't really use this functionality so I can't really offer any more suggestions. If this isn't the problem I would recommend posting on "mr excel" forums I've always received good advice there.

 

[umasriram2]

Thanks! I'll try posting it in other forums..The number of rows is only 8k and when excel tries to read data from access it collapses at 75 rows