Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312

    Unanswered: Exporting To Access Very Slow

    Has anyone else exported data to Access? If so, is it a slow process in SQL Server 2005?

    I am exporting 3,000 records to an Access database using a view and it has taken 20 minutes. I am using the Export Data wizard. Any tips on how to speed it up would be appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    just a shot in the dark here, but does someone have the Access file open? If you view hidden files, do you see a *.ldb file?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    how about this. How long does your view take to execute in the Query Analyzer? It might be that the view just has to be tuned.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The view executes very quickly, a couple of seconds. The database is not in use, has three tables and two reports in it (size 11,000 Kb). I am just exporting it to a file so I can send the data and the report to a colleague. I do it periodically and it is annoyingly slow.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    why don't you turn you access thing into a Access Data Project that talks to SQL Server directly and then you can run your report in Access directly off of the SQL Server data. Then there is no need for that pesky slow export.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Because the Access file will not have access to the SQL Server database once I email it to my colleague. The Access database has to contain all the objects I need, tables, reports, modules, etc.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    the good old sneakernet. have fun with that.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what are you using to do the export? Is Access pulling the data or is the SQL server pushing the data?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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