TransferSpreadSheet Is Not Reliable Within Loop

[Gary]

I am using Access 2000-2003 on a XP OS and I have a reliable issue in the
transferspreadsheet issue. Once in a while it transfer correctly, and in
most cases it doesn't. I am trying to transfer about 15 worksheets with
about 400 to 700 rows per worksheet.

This is the VBA code that I am using:

For Each xlSheet In xlBook.Worksheets
ws = xlSheet.Name & "$"

DoCmd.SetWarnings False
DoCmd.OpenQuery "DeleteFormation Table Buffer"
DoCmd.TransferSpreadsheet A_IMPORT, 8, "Formation Table_
Buffer", "C:\x\yy.xls", False, ws
DoCmd.OpenQuery "AppendFormation Table"
DoCmd.SetWarnings True
Next xlSheet


The "AppendFormation Table" query does work, and sometimes I see funny
things in my table like missing rows or duplicate row, and other funny things.

It works sometime. I added delays between them, but that doesn't work
either.

Does anyone have suggestions for me??


Thank you,


Gary

 

[vanderghast]

It may be a bug hard to reproduce which exists since at least Access 2.0. A
possible way around, I then used, was to launch another Access.exe app
(shell) which just does ONE DoCmd.TransferXXXX, and then close that app. As
you said, the hard to reproduce problem seems to occur only when in a loop
(but not always), so having just ONE shot to DoCmd.Transfer does not raise
the problem. Sure, you can then loop over the shell- process, but basically,
one Access app sees only one DoCmd.Transfer! My scenario was not implying
the DoCmd.Transfer in 'sandwich' between two DoCmd.OpeQuery, though, so I
don't know if it is applicable to your case. And check if the whole loop
does not generate memory leak, in the end.


Vanderghast, Access MVP

 

[ryguy7272]

If you are looping, try the code samples here:
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

HTH,
Ryan---