Create application using two database (SqlServer & MS Access)

Question

Hello

I have create a accounting application using vb.net + sqlserver express and my application is working properly.

I have used typed dataset mostly and sometimes Linq2Sql

Now, I want to make my application work with MS ACCESS database

I can do it by duplicating the forms and change the connection string to match the db engine. Is this is the way to go?

What is the standard for creating application that works in multi database engines?

TIA

---

.Net Blog VFP Blog

Answer 1

Hello,

Using typed data via the data wizards is the wrong path, the proper path to allow sharing is to have a class project for each database that have the same methods which are called from your form project. Using typed data classes are hard coded to a data provider and also data types may be the same yet the underlying provider OleDb and SqlClient have different types for underlying data.

With that although it may be hard at this point is to look at using managed data providers rather than typed data classes.

---

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

Answer 2

I got your point

If I created a class project for each data provider. How to switch between them in my application? using IF End If before each data processing ?

---

.Net Blog VFP Blog

Answer 3

Let's say that the application starts up and checks to see if the MS-Access database exists, if so this is your decision point, use the MS-Access classes while if the MS-Access database does not exists use SQL-Server classes. Does this make sense?

---

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

Answer 4

You can use a DAL.

http://geekswithblogs.net/joriente/archive/2008/03/31/120886.aspx

Answer 5

Hello Karen

<<see if the MS-Access database exists, if so this is your decision point>>

This is not what i am asking for.

Let say client_A chose to work with access database, and client_B with Sql Server

I have my start form frm_start where in the load event i check if "as you suggested" if MS-Access database exists" to decide my application database engine it will use

now, I have a form called frm_item_list which shows the list of items in grid

I have also 2 classes or 2 datasets ot 2 DAL (whatever) for my 2 database

Public Class Access_Class
    Function GetTable_ITEM As DataTable
        '''' some code to return data
    End Function
End Class

Public Class SqlServer_Class
    Function GetTable_ITEM As DataTable
        '''' some code to return data
    End Function
End Class

in the frm_item_list load event, what/how should i code?

    Private Sub frm_itemlist_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        If My.Computer.FileSystem.FileExists("myaccess.accdb") Then
            Dim o As New Access_Class
            Dim dt = o.GetTable_ITEM
            Me.DataGridView1.DataSource = dt
        Else
            Dim o As New SqlServer_Class
            Dim dt = o.GetTable_ITEM
            Me.DataGridView1.DataSource = dt
        End If
    End Sub

I am asking, if in each form, in each data processing I should create if statement? is this the way to go?

---

.Net Blog VFP Blog

Answer 6

Hello

Yes, I can use 2 DAL for each DB. My question is how to code the use of 2 DAL in application? 

Please check my response to Karen.

Ty

---

.Net Blog VFP Blog

Answer 7

I have solved this by creating a class that works on OleDb Connection

OleDb can work with MSACCESS and MSSQL

I only had to change the connection string

I am using CommandBuilder so it will handle the correct Update/Delete statement depends on my select

something like this

Dim OleOv4 as new OleOv4(my_connection_string)
OleOv4.GetDataByTable("ITEM")
Me.Datagridview1.Datasource = OleOv4.BindingSource

Thanks everyone who helped

---

.Net Blog VFP Blog

Answer 8

Indeed you can use the ole provider.

But better yet? While most overlook ODBC these days? I would if from day one the intention is to change the connection strings then consider using ODBC providers. And you find the sql syntax is "more" compatible when using ODBC providers. 

To to be really fair? oleDB providers are a windows only technology. ODBC is a true universal standard (not limited to windows platforms). And SQL Azure "sort of" supports oleDB, but the official position is that oleDB end of life was announed 9 years agao:

This blog (dated 2011 - NINE years ago!!!) states this:

Microsoft is Aligning with ODBC for Native Relational Data Access

quote:

ODBC is the de-facto industry standard for native relational data access, which is supported on all platforms including SQL Azure. Cloud is universal and in order to support all client applications connecting from any platform to the cloud, Microsoft has been fully aligned with ODBC on SQL Azure, as ODBC is the only set of APIs that are available on all platforms including non-Windows platforms. 

And:

The commercial release of Microsoft SQL Server, codename “Denali,” will be the last release to support OLE DB.

and

We encourage you to adopt ODBC in the development of your new and future versions of your application. You don’t need to change your existing applications using OLE DB, as they will continue to be supported on Denali throughout its lifecycle. While this gives you a large window of opportunity for changing your applications before the deprecation goes into effect, you may want to consider migrating those applications to ODBC as a part of your future roadmap.

Link for above: (now dead)

http://blogs.msdn.com/b/sqlnativeclient/archive/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access.aspx

(above quotes pulled from wayback machine).

If you use ODBC providers, then you can change the connection string and not have to change the providers. You "can" do this with oleDB providers - but ODBC is simply better at allowing this to occur. And given that a whopping NINE years ago, end of oleDB for sql server was announced? Well, then ODBC is the better choice.

And given that they released a SLEW of new "native xx (now at 17) drivers with support for SQL Azure, and the  magic ability to re-connect due to using the internet between the client and the server? Then even MORE compelling reasons exist to adopt ODBC - including that you find the sql and changes are LESS then when adopting oleDB providers. So even date delimiters and wild cards with ODBC between Access and sql server are the SAME - not so with oleDB.

Anyway, it sounds like you done what you done, and oleDB can consume both sources (Access or sql server), but all in all, I would use + choose a ODBC provider for this.

Regards,

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada