Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    3

    Unanswered: Interfacing SQL Server with Access...

    I have a web based SQL Server application that I need to modify to include data that is currently stored in an Access mdb. The mdb file and SQL Server are running on the same box. The data that is on the Access system cannot be migrated to SQL Server since it is a commercial package. But in our web application we want to query this data (and that in the SQL Server database) and present both current information to the users. We don't need to edit the Access data. Just view it and combine it with data we now have in SQL Server.

    I was thinking I would build views or stored procedures that would pull info from both the SQL Server tables and the Access tables. But I'm not sure how efficient this is. One issue is that the users are remote, meaning many will not be in the same building that the SQL Server box is located at.

    What is the best way to accomplish this? Any ideas? I've thought about using DTS but maybe it is overkill.

    Thanks for the help.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I would not query the access data from a web app. It will choke and die once you get about 20 connectins to it at a time. If you can live with the access data not being real time I would setup a job that fires daily or hourly that pulls the data into sql server daily or hourly or every 15 minutes. Or if you have the development manpower, redevelop the Access app to use sql as the datasource with an Access Data Project. Access as a backend to a web app is a recipe for disaster. Seen it before.
    “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
    Dec 2005
    Posts
    3
    Thanks for the advice Sean. Seeing as we cannot redevelop the backend for SQL Server, our only option appears to be batch updating of data into our system from Access on a scheduled basis. Would we use something like DTS for this? What can we do in SQL Server to facilitate the transfer of data from Access to SQL Server?

    Thanks Sean.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Use a SQL Server Agent job that fires a DTS job.
    “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
    Dec 2005
    Posts
    3
    Quote Originally Posted by Thrasymachus
    Use a SQL Server Agent job that fires a DTS job.
    Thanks. Will do.

Posting Permissions

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