Access automation from Excel seems to time out

[SBendBuckeye]

Hello all,

I have a user with a multistep process which involves 3 different applications all of which end up creating or modifying a set of Excel spreadsheets.

The middle pieces are Access pieces. The first one imports 3 Excel files into tables and then exports a new Excel file by running a query which combines the 3 tables with inner joins, etc.

The import files are around 15,000 rows each and up to around 6 Meg in size.

This part works fine.

Later, a second Access piece imports the Excel file created above by the first Access piece. It uses identical code except for different file names.

The second piece hangs (appears to time out). If I kick the Excel VBA into single step mode and run it manually it works fine (although it takes a while and I get a message in the Excel workbook area saying Excel is waiting for an OLE process).

Does anyone have any ideas on this?

Thanks in advance for any help and/or suggestions!

 

[SBendBuckeye]

I don't know why this helps but here is what I did to solve the problem.

Originally I was doing 2 things in the Access piece:

1. DoCmd.TransferSpreadSheet acImport, etc
2. DoCmd.TransferSpreadSheet acExport, etc

I split them up into 2 pieces where I create the Access Application object each time and only do 1 thing and it works.

Maybe this will help someone somewhere.

Have a great day!