Standardised Database Access

[George1111]

I want to create a method which will allow me to deploy my application which has code written in both VB6 and VB.Net

I want to be able to work with either Access (Jet) Database or SQL Server 2005 Databases

ie,

VB6 with Access
VB6 with SQL Server 2005
VB.NET with Access
VB.NET with SQL Server 2005

I presume if I write all Data Access using ADO, then I will be compatible with Access and SQL Server 2005

But what about connection strings for the above 4 combinations

Ideally I would have a connection method which will work with all methods

Is this close to the realms of possibility OR do I have to write substantial code to achive the combinations I need

For example, is ODBC the ONLY way to achieve this ? (If it can) or is there some neat way to do this

Thanks for any ideas - it will help with developing a substantial application which can then be deployed with a lot of flexibility

[cjard]

While I can understand in part the want to support two databases (but really, SQLServer comes in file form just like access,..) I really cannot understand wanting to provide it in VB6 / VB.NET variations. Just because the two languages share a syntax does not mean that they are really related or that one is a successor to the other..

Can you tell me why youre taking this route? There comes a point where flexibility provision becomes self limiting and you run into problems because you have to operate within the union of the capabilities of the combinations.. In short, it's not flexible, or futureproof at all (we are already struggling at work, with a VBA/access app that does most of the donkey work - we're hitting the serious limitaitons of how the language works and having to write dlls in .net to provide the functionality.. it's just silly, and the whole thing needs doing in .net)

[George1111]

I have an ERP System developed substantially in VB6 using Access.

I am now wanting to use SQL Server 2005 with it, and will gradually convert the application to VB.Net as time permits.

I cant just stop and rewrite it in VB.Net as this will take over 12 months - plus - by gradually upgrading sections of the system in VB.Net I have the opportunity to debug smaller sections of the system rather than have to worry about a complete system

The key issue is to simplify the Data Access so that I am dealing with essentially the same code whether it is Access or SQl Server