Export Spreadsheet not matching source query

[GeorgeDurkee]

I am trying to export a series of crosstab queries from Access 2000 to Excel. The final excel spredsheet data does not match the query results....data has changed, columns have been added, data moved from one column to another, etc.

this is not consistent. I'll run it on time and all of the data is screwed up...run it again, and the data appears to be correct.

Anybody run into this?

Thanks

George

 

[MichaelRed]

to quote someone within these forums " ... problem located between chair and keyboard ... ". Otherwise stated. you (obviously | aparently) have a problem in who/what/when/why/where/how this is accomplished. I have used crosstab queries as export objects on numerous occassions without these issues. The only way I could even see some of these occurances would be if the crosstab is based on one (or more) other recordsets which are beign re-structured by other elements of the process. this colud occur if -for instance- some paart(s) of the crosstab query recordsource(s) are being re-written (changing the SQL property of the QueryDef object). In a large scale app, some other users execution thread may be doing this via code.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[GeorgeDurkee]

This is a single user application that worked perfectly under Access 97 and Office 97. I upgraded to Access 2000 and Office 2000 and this started to happen.

The queries are being exported using the docmd.transferspreadsheet command of VBA.

Even wierder is that if I run it again, using the same set of commands, it seems to work fine.

For what it is worth, I have been using queries - crosstab and select for export, using pretty much this series of commands, since Access version 1.1. I have never run into anything like this until I upgraded to 2000.

Any other suggestions?

George

 

[MichaelRed]

Hmmmmmmmmmmmm,

[/i]Suggestions[/i]? Yes.

HELPFUL Suggestions? Hmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm?


On a brighter note.

Is this 'process' encapsulated in a procedure? If not, can you (reasonably) put it in a "pill" form?

Do you already have it in a procedure - and somewhere in the overall process have warnings turned off? If so, comment that out for the time being. If not, start using the debug / immediate mode to step through the procedure (pill). Make LIBERAL use of the immediate screen to "capture" the various values of thinnggyys as you step through (to some extent, you can just do the ole "debug.print ... " two-step, but supplement this with little bits and pieces of the data stuff ... If pratical, build a table where you can "log" your various operations and the value of variables. Let the old lady (Ms. Access) do the walking for you - have the proces log the time, variable and value of LOTS of stuff to the tracelog. Inspect these to see WHAT is changing that you are not expecting.

A GUESS is that there is some [un | Mis]-initalized variable?



MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over