Access writing wrong data to spreadsheet

Access writing wrong data to spreadsheet

I have a rather strange issue with Access (seems to happen with both
2003 and 2007) and SQL Server.  It  seems to happen both with Express
(local instance) and Standard over a WAN.  The form enables the user
to generate a spreadsheet from select date range.  Two things happen
to this user

1.  The routine generates a blank spreadsheet even though there is
data for the selected date range (confirmed because the data is
written to a table in SQL server)
2.  The routine generates a spreadsheet with the previously selected
date range rather than the currently selected date range.  That is,
the user generates a spreadsheet using 1-29 Jan, then selects 1-31
March.  The second spreadsheet still has the 1-29 Jan data, even
though the data in the SQL Server table is for 1-31 March.

I was able to replicate the problem only once using the mde in 2003.
While running the routine the form seemed to repaint, all of the data
disappeared from the form listbox and when completed the form had
reverted to the previous dates.  But this only happened for me on the
mde once.   It happens to this user both using the mdb and the mde).

I verified that he is writing a new spreadsheet each time (not trying
to overwrite the file).
Any ideas for next steps in troubleshooting this problem?  Is it
possible that Access is not refreshing the cache and if so is there
any way to work around this issue?

Thanks
Cheryl

date: Fri, 18 Jul 2008 09:47:24 -0700 (PDT) author: unknown

Re: Access writing wrong data to spreadsheet

"The routine ..."

What routine?


Regards

Jeff Boyce
Microsoft Office/Access MVP

 wrote in message 
news:df29351d-90f8-4b52-8f45-7f56b6247e09@m73g2000hsh.googlegroups.com...
>I have a rather strange issue with Access (seems to happen with both
> 2003 and 2007) and SQL Server.  It  seems to happen both with Express
> (local instance) and Standard over a WAN.  The form enables the user
> to generate a spreadsheet from select date range.  Two things happen
> to this user
>
> 1.  The routine generates a blank spreadsheet even though there is
> data for the selected date range (confirmed because the data is
> written to a table in SQL server)
> 2.  The routine generates a spreadsheet with the previously selected
> date range rather than the currently selected date range.  That is,
> the user generates a spreadsheet using 1-29 Jan, then selects 1-31
> March.  The second spreadsheet still has the 1-29 Jan data, even
> though the data in the SQL Server table is for 1-31 March.
>
> I was able to replicate the problem only once using the mde in 2003.
> While running the routine the form seemed to repaint, all of the data
> disappeared from the form listbox and when completed the form had
> reverted to the previous dates.  But this only happened for me on the
> mde once.   It happens to this user both using the mdb and the mde).
>
> I verified that he is writing a new spreadsheet each time (not trying
> to overwrite the file).
> Any ideas for next steps in troubleshooting this problem?  Is it
> possible that Access is not refreshing the cache and if so is there
> any way to work around this issue?
>
> Thanks
> Cheryl

date: Tue, 22 Jul 2008 08:24:51 -0700 author: Jeff Boyce

Re: Access writing wrong data to spreadsheet

On Tue, 22 Jul 2008 08:24:51 -0700, "Jeff Boyce"
 wrote:

>"The routine ..."
>
>What routine?
>

I agree with Jeff - there isn't enough information here.  We need to
see the code that is generating the spreadsheet.

But the problem with the spreadsheet using the last criteria instead
of the current criteria may be easily explained.  Maybe the
spreadsheet is being generated from a work table, but *before* the
work table is reloaded with the new records.  Then your spreadsheet
would always be built from the work table from the *last* time the
code ran.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

date: Tue, 22 Jul 2008 08:57:22 -0700 author: Armen Stein

Re: Access writing wrong data to spreadsheet

> date range rather than the currently selected date range.  That is,
> the user generates a spreadsheet using 1-29 Jan, then selects 1-31
> March.  The second spreadsheet still has the 1-29 Jan data, even

Yes, Access (all versions) does that. I've never had trouble writing a
single spreadsheet, but writing a series of spreadsheets seems to be
unreliable.

Or rather, just reliable enough to trick you into thinking that it
works.

I had more problems with the Access methods than with the DAO
methods, but I decided that both were unreliable.

I did not have similar problems doing repeated CSV, RTF or Word
(tab) exports for mail merge, but they were always much smaller (often
single record) exports.

(david)

 wrote in message 
news:df29351d-90f8-4b52-8f45-7f56b6247e09@m73g2000hsh.googlegroups.com...
>I have a rather strange issue with Access (seems to happen with both
> 2003 and 2007) and SQL Server.  It  seems to happen both with Express
> (local instance) and Standard over a WAN.  The form enables the user
> to generate a spreadsheet from select date range.  Two things happen
> to this user
>
> 1.  The routine generates a blank spreadsheet even though there is
> data for the selected date range (confirmed because the data is
> written to a table in SQL server)
> 2.  The routine generates a spreadsheet with the previously selected
> date range rather than the currently selected date range.  That is,
> the user generates a spreadsheet using 1-29 Jan, then selects 1-31
> March.  The second spreadsheet still has the 1-29 Jan data, even
> though the data in the SQL Server table is for 1-31 March.
>
> I was able to replicate the problem only once using the mde in 2003.
> While running the routine the form seemed to repaint, all of the data
> disappeared from the form listbox and when completed the form had
> reverted to the previous dates.  But this only happened for me on the
> mde once.   It happens to this user both using the mdb and the mde).
>
> I verified that he is writing a new spreadsheet each time (not trying
> to overwrite the file).
> Any ideas for next steps in troubleshooting this problem?  Is it
> possible that Access is not refreshing the cache and if so is there
> any way to work around this issue?
>
> Thanks
> Cheryl

date: Sun, 27 Jul 2008 11:40:35 +1000 author: david