How can my application use both an MS Access database and a SQL Server database at the same time?

Question

What strategies and techniques have you used to produce a data driven application that needs to access multiple diverse data sources? Specifically, Microsoft Access and SQL Server. Ideally, I'd like to make an abstraction layer that hides the physical data source from the application.

Something like the ADO.NET Entity Framework would be ideal, but it doesn't have an MS Access provider. Is there a different framework or technique that supports simultaneous Access and SQL Server connections?

How much work would it be to make an Access provider for the Entity Framework?

Answer 1

I'm pretty sure LLBLGen supports Access (and, of course, it supports MS SQL). It costs money though, but IMHO it's the best OR/M for .NET.

Answer 2

Look at NHibernate. It has providers for SQL Server and Access (and others).

Answer 3

As far as I know, there are only two choices:

1. Multiple connection strings
2. Single connection string.

In the first scenario, you join any data you need from the disparate sources in the middle tier code. In the second scenario, you access all sources but one indirectly through the one source. For example, you'd retrieve data from Access via a Linked Server in SQL Server.

They each have their advantages and disadvantages. Using a single connection string is certainly simpler to code against in the middle-tier. Ultimately it depends on the need of two sources. If one is simply for logging for example, then two connection strings might be simpler. If joins and such are needed, then using a linked server might be easier depending on the sources. If one of the sources is Access and you are not using Access security, then using a Linked Server can work fine depending scale. If you are using Access security and have different logins that need to access the database, then using a linked server will be a pain.

Answer 4

Another approach is to create Linked Servers in SQL Server, perform joins using views in SQL Server and completely hide the fact there are multiple data sources from your application.