Results 1 to 7 of 7

Transfering data between Access and Excel freezes computer

This is a discussion on Transfering data between Access and Excel freezes computer within the Microsoft Access forums, part of the Question Forums category; Using Access to import data via an ODBC connection to an Oracle database, once data has been imported, a simply ...

  1. #1
    New Member
    Join Date
    Feb 2007
    Posts
    3

    Default Transfering data between Access and Excel freezes computer

    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.

  2. #2
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,212

    Default

    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
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  3. #3
    New Member
    Join Date
    Feb 2007
    Posts
    3

    Default

    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

  4. #4
    Board Regular
    Join Date
    Apr 2003
    Location
    Jacksonville, FL
    Posts
    1,344

    Default

    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/viewto...ht=usercontrol

    http://www.mrexcel.com/board2/viewto...ht=usercontrol

    Mike

  5. #5
    New Member
    Join Date
    Feb 2007
    Posts
    3

    Default

    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

  6. #6
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,212

    Default

    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
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  7. #7
    Board Regular
    Join Date
    Apr 2003
    Location
    Jacksonville, FL
    Posts
    1,344

    Default

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com