Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Posts
    5

    Unanswered: Replication between SQL and Access97?

    I have an Access 97 database that I would like to "mirror" in my SQL server 2000 database.

    Basically I would like SQL server to keep a current copy of an Access 97 database table which exists on remote machine on our network. Any time there is a change made to the Access table, I would like the SQL server to be updated automatically.

    Is this something that is possible to do with replication/synchronization, or do I have to push the data up with another app?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    To my knowledge you can not set up relication from Access to SQL Server. Are you sure your tables are in Access? Or are you using an Access Data Project or tables linked into a mdb file?

    If your tables are truly located locally in your access database the only way I can think of doing what you want to do is to link in your SQL Server tables and for every insert, update and delete you run the same queries against the SQL tables at the same time.

    Why not just move your tables in your access application to SQL Server?
    “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
    Feb 2005
    Posts
    5
    The tables are definately in an Access 97.

    I thought about moving the tables into the SQL database but I really need a copy locally in the event of a network failure. (The app which generates the data and fills the tables runs on the machine remote to the SQL database.)

    Thanks for your suggestions.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    move the tables to sql. and then you can have DTS packages that push them down to your local access app.

    Is'nt your SQL Server running backups on itself anyways?
    “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
    Feb 2005
    Posts
    78
    Depending on why you want to do this, you could
    1)put a copy of sql server on the remote server and then use those tables in your access app. Replication to the other server is then easy if you still want this.
    2)use dts packages to copy the data into your sql server tables using a scheduled job.
    3)use heterogenous transactional replication but it looks a little complicated at first sight and I know nothing of the implications and practicality of implementing this type of replication

  6. #6
    Join Date
    Feb 2005
    Posts
    5
    I clearly need to bone this. I did find some info in the SQL Server Books Online under "Access Subscribers" which looks promising.

    I believe this is item 3 refered to in the previous post.

    Thanks for your help.

Posting Permissions

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