Excel Automation - Access Hangs

O

Obese

Hello,
Excel as an Automation server is proving to be a challenge for me!

Would 10000 rows be too many to get into Access with the
TransferSpreadsheet Method? I am using Access 2002, Win2000 and a 256MB PC.
The code runs successfully but then Access freezes afterwards (although I
can minimize, restore, close etc., I do not see the database window
anymore.

When I run the code on a smaller number of records (say, 3000), it is not a
problem. Hence my conclusion that it is caused by the large number of
records.

Any help on how I could get around this is appreciated.

Regards,
Alex
 
B

Brian

Obese said:
Hello,
Excel as an Automation server is proving to be a challenge for me!

Would 10000 rows be too many to get into Access with the
TransferSpreadsheet Method? I am using Access 2002, Win2000 and a 256MB PC.
The code runs successfully but then Access freezes afterwards (although I
can minimize, restore, close etc., I do not see the database window
anymore.

When I run the code on a smaller number of records (say, 3000), it is not a
problem. Hence my conclusion that it is caused by the large number of
records.

Any help on how I could get around this is appreciated.

Regards,
Alex
Click to expand...

Using TransferSpreadsheet does not involve automation.

Anyway, I seem to recall that, under some circumstances, TransferSpreadsheet
can be sensitive to whether Excel has the file open or not. If you are
doing it with the file closed, try opening it first, and vice versa.

Another possibility is that the problem is being caused, not by the size of
the file, but by some weird data somewhere between the 3000'th and 10000'th
records. Might be worth doing a binary chop on the file to find out if
there is a specific record that causes the failure.
 
O

Obese

Brian said:
Using TransferSpreadsheet does not involve automation.

Anyway, I seem to recall that, under some circumstances, TransferSpreadsheet
can be sensitive to whether Excel has the file open or not. If you are
doing it with the file closed, try opening it first, and vice versa.

Another possibility is that the problem is being caused, not by the size of
the file, but by some weird data somewhere between the 3000'th and 10000'th
records. Might be worth doing a binary chop on the file to find out if
there is a specific record that causes the failure.
Click to expand...

Thanks, Brian. My problem was caused by a DoCmd.Echo False which I had at
the end of the code. Everything is fine now.
 
Top