We deliver an application that is currently supported by an Access database. This allows for easy setup and deployment in small organizations. Recently, we're getting in to larger and larger organaizations and Access just doesn't cut the mustard.

What I would like to do is to continue supporting Access while also providing the option to support SQL Server/Express & MySql in the same application.

I've been using the Adapter templates so from what I understand, I can provide a solution by instantiating the DBSpecific adapter for the type of DB that the user is currently working with, and operate on the entity classes generated into the generic db project.

Knowing that there are some schema differences between Access and SQL such as datatypes and some relationship differences (the relationship differences are due to a limitation in SQL where you can't have relationships that cascade deletes to the same table down multiple relationship paths), I'm wondering what kinds of issues I may face and how to solve them.

Is there a better way to go about this whole thing?

Before sliding down a potentially slippery slope of inexperience, I'm hoping that I can get some guidance so I can aviod the major pitfalls. We're currently using the latest 2.5 version.

one adapter for multiple databases works when the product itself accesses multiple databases. example orders is queried from sales db and employees are queried from hr db.

your situation leads me to believe there is 1 database which can have multiple platforms. the customer chooses one at pre/during installation.

if this is correct you would need to generate the dbspecific project for each db type (sql, oracal, access, mysql, etc...) then include the appropiate the adapter/dqe when building an installation package for the client.

the model shouldn't change as it's db egnostic.

The catch is that I want to provide the ability for users to connect to a database (either Access or SQL) at runtime, not at install time. Currently, users can create multiple Access databases (all with the same schema) and select which database they would like to connect to when logging in to the application. Going forward, I would like for users to be able to select either an Access or SQL database (having the same schema) to connect to and work with.

Here's what I was thinking...

1 - Generate DBSpecific projects for both Access and SQL. 2 - Include both DBSpecific projects in my sln, along with a generic DB project 3 - At runtime, determine the type of database that the user wishes to connect to. At that time, instantiate an instance of the DataAccessAdapter using a method such as this:

public static DataAccessAdapterBase adapter;
public static DataAccessAdapterBase CreateAdapter(string databaseType)
        {
            if (databaseType == "Access")
            {
                adapter = new AccessDAL.DatabaseSpecific.DataAccessAdapter();
            }
            if (databaseType == "SQL")
            {
                adapter = new SQLDAL.DatabaseSpecific.DataAccessAdapter();
            }
            return adapter ;
        }

At this point, I "think" I should be able to switch adapters by calling this method.

I'm about to give it a try to see if it works.

My concern is that the generic model won't work for all situations due to datatype differences and differences in relationships between the Access and SQL database. As it turns out, the schemas are not entirely identical...I'm not able to create the same constraints in the SQL database as there are in the Access database.

Even more so, I'm concerned that I'll encounter unforeseen issues with this approach that will make go insane (I'm delicate like that). Really though, I have a deadline for this solution and I can't afford to do it wrong 3 times before getting it right.

Any insights, suggestions, warning flags, and or idiot alerts would be greatly appreciated.

the model is completely independent of the db schema, so this shouldn't be a problem. reviewing your sample above, you shouldn't have any issues with this approach.

If the client can generate multiple databases with the same schema, then you will need to use the CatalogNameOverwrite property for sql server. For Access you will need to use entirely different connection strings. while this isn't impossible it adds complexity.

one thought you may want to recommend to you clients is use Sql Server Express (free) and drop the option of access support (at least for new clients). this way you don't need to mess with mulitple connection strings. instead just use the CatalogNameOverwrite property.

My concern is that the generic model won't work for all situations due to datatype differences and differences in relationships between the Access and SQL database. As it turns out, the schemas are not entirely identical

Matching schemas is a pre-requisite here You should further work on it.