Transfering data between Access and Excel freezes computer

[cfriess]

Using Access to import data via an ODBC connection to an Oracle database, once data has been imported, a simply query is run using the tables (2) that were imported, the data is either exported directly to Excel or cut and pasted into Excel. Either way the computer locks up and must be rebooted.

 

[SydneyGeek]

Hi, and welcome to the Board!

Is the data physically imported into Access, and written to another table, or are you linking to Oracle and creating the query direclty from those linked tables?

How many records are you working with?

Denis

 

[cfriess]

Thanks, the data is physically being imported into Access. The query is based on Access tables, the number of records is... 10,750. Thanks for your help, Chris

 

[mdmilner]

Please be more precise.

I see three major steps in your description -

1- import from Oracle
2- query generation
3- export to excel

At what stage of each of these steps does it lock up?
Have you verified each of the prior steps are complete?
How are you doing the process? Manually or code?
Is it an automated process or one interactive (clicking buttons on a form?)?

My personal guess is, based on how you wrote this out, is that the first two processes are working and it's failing with the export to excel.

Based on that premise, I would ask whether the process has ever worked? Does it, perhaps, work once and then all future attempts fail?

If yes - it is highly likely that you are not closing the excel object properly. Doing a quick control-alt-delete should show you one or more excel.exe processes still running - and, if so, manually killing them with End_Process should allow it to work one more time.

Assuming the above is a good guess, try these links which may refer to other posts.

http://www.mrexcel.com/board2/viewtopic.php?t=147132&highlight=usercontrol

http://www.mrexcel.com/board2/viewtopic.php?t=102394&highlight=usercontrol

Mike

 

[cfriess]

You got it, those are the steps, it hangs when exporting to Excel and even if you cut the data from Access to paste it in Excel. This is all a manual process, no code involved. Chris

 

[SydneyGeek]

If it works with a small sample (like the top 500 records), it may be a memory issue. You may find it better to use code to push the data to Excel from Access using TransferSpreadsheet, or to import in into an Excel file using ADO.

Which would you prefer to do?

Denis

 

[mdmilner]

I'm afraid all of my suggestions were premised on it being a code based approach.

I would also suggest some experiments with other file formats. Can you, for example, export the table itself into another database? Can you use MSQuery from inside Excel to extract the data?

Depending on the answer and possibly other particulars, you could have missing office components or some other software oriented problem?

Mike