MS Access 2003 - Analyze with Excel

[mark1080p]

Hi,

I have a weird issue, but found it very hard to get an answer via search on here and google.

At work - I was using Office 2000 package. I have a few queries built, where I would export them into excel (via "analyse with excel" tool) and from that exported sheet, would use Ctrl H (edit & replace) any blank cells with "0". The process took a second. maybe 2 secs max.

I have now been given a new PC with 3ghz dual core and 2gb ram (in other word far far superior to what i was on previously) and Office 2003. When I run through the exact same process as above the "edit & replace takes ages (between 1 to 2 mins)

I have tried the following tests

Save the exported sheet in various versions of excel prior to edit replace
Convert DB to 2002 version instead of running in compatibilty 2000 version

Is there anything I should be looking into since my PC upgrade. What I have done with some of the queries i own, is use the nZ([data])+0 formula, which works but would rather have the issue fixed, rather than working around it. I also dont own some of the databases personally, so dont really want to mess around with formulas they may not understand.

Can someone give me a solution which doesnt involve any vb editing or formula building? Im hoping its a setting with the software/registry itself?!

Thanks in advance

Mark

 

[mark1080p]

Just a quick update

If I highlight, copy and paste into new excel spreadsheet. Then edit & replace then that works instantly.

It only takes an age if I use "analyse with excel" via office links.

Hope this helps narrow down the cause of issue.

 

[mark1080p]

Can anyone help?
thanks

 

[Norie]

Mark

Is using Nz really a workaround?

That 'fixes' the data at source so you don't need to do the find and replacw in Excel.

Are you having problems using Nz?

 

[mark1080p]

Well it is a workaround, in the sense that I will have to go through every single query and update every column within them queries with nZ formulas

Edit & replace used to take a second but now takes minutes? I want to fix Access so that it only takes a second again.

I need to understand why my Access is doing this? Is it to do with the version of excel it exports to?

 

[Norie]

Have you tried using some of the other methods of exporting data to Excel?

Or even doing things from Excel by importing the data.

As for the Nz thing, you might need to update a bunch of queries but that should just be a one-off.

Once you've done it you wouldn't need to do the find/replace in Excel every time.

 

[mark1080p]

Have you tried using some of the other methods of exporting data to Excel?

Or even doing things from Excel by importing the data.

As for the Nz thing, you might need to update a bunch of queries but that should just be a one-off.

Once you've done it you wouldn't need to do the find/replace in Excel every time.

I have worked out ways of getting round it, and appreciate your different methods of getting around it. but doesnt answer my original question.

I want to know why a simple function, that I used in previous Access versions, now takes longer?

Is this a known bug/issue
do other 2003 users experience the same issue
can it be fixed
is it an install issue or windows registry