Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Posts
    72

    Unanswered: suggestion to copy data from Sql Server into Access database

    Situation:
    I have a Sql Server in Rome and a shared server dir (with the access database) on a Server in New York.

    I have permission on sever dir and databse.

    The database on sql Server have exactlly the same structure of Access database. (the table on Sql Server have 200.000 records and 48 columns).

    Now i want to recopy all records from Rome to NewYork.

    Wath is the best way in terms of time to recopy records from Sql Server to acces Table?

    note:
    I want to use in my app the ADO jET drive.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it might be best for the sql server to push the data to access via ssis or dts.
    “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
    Oct 2004
    Posts
    72
    Quote Originally Posted by Thrasymachus View Post
    it might be best for the sql server to push the data to access via ssis or dts.
    OK... i know this way.
    but during the copy from sql to access i need to make other operation on sql records...

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    have SSIS\DTS drop the data in staging tables in the access DB and do whatever you need to it.
    “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.

  5. #5
    Join Date
    Oct 2004
    Posts
    72
    Quote Originally Posted by Thrasymachus View Post
    have SSIS\DTS drop the data in staging tables in the access DB and do whatever you need to it.
    Ok but i need to work with vb6...

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    are you talking to me through a wormhole from 1997? you asked for the best way. VB6 is not the best way.
    “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.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Thrasymachus View Post
    are you talking to me through a wormhole from 1997? you asked for the best way. VB6 is not the best way.
    HEY NOW

    That's the age of my Camaro

    Wanna race?

    In any case, just dump the damn database and restore it...use native format
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by sal21 View Post
    Now i want to recopy all records from Rome to NewYork.
    How about installing SQL Server in New York. That will make it much easier to keep the two in sync. SQL Server is also a much better way to share data on a server.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by Brett Kaiser View Post

    Wanna race?

    In any case, just dump the damn database and restore it...use native format
    using vb6 to call the command line tools does not count. i suggest ssis\dts because it is a little more familiar to prograammers accustomed to graphical programming tools.

    personally i luv spinning batch files full of dos and osql and bcp too, but it aint for everyone.
    “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
  •