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.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.|||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.|||Use a SQL Server Agent job that fires a DTS job.|||Use a SQL Server Agent job that fires a DTS job.

Thanks. Will do.sql

interesting problem..related to selection criteria..

Have report where the user should be able to enter some selection critera to EXCLUDE from the result set displayed.

I deally Iwould liek to include it in the sql parameters to avoid retunring a larger then necessary result set, but Couldn't I also use it inthe filter criteria?

Has anyone done anything like this.

ie: a drop down box where people could add exclusions.

ie: Apples, Oranges

would be excluded from the result list.

So If I entered Apples, Oranges

the resultset would eclude these from the returned dataset.

I realize using a multi param box is fruitless. WOndering if there is another way to do this

Why wouldn't multi-param work?

Select something from sometable where (@.multiparam is null or fruit not in (@.multiparam))

Does this look ok?

cheers,

Andrew