Click here to Skip to main content
15,614,766 members
Please Sign up or sign in to vote.
0.00/5 (No votes)

I have created an accounting application using + Sql server using typed Dataset and Linq2Sql.

Now I want to create a second version for my application using MS-Access

What is the best way to go for that? creating a second copy of my application?

Can I have one form (item list) using Sql Server or Access depending on client choice?

What I have tried:

- I tried to cast sqlserver dataset to access dataset (failed)

- I created one dataset and tried to change the connection string (did not worked)

- I created 2 class for each database, but i could not figure out how to use one variable to handle both class (this below worked, but before each data processing I had to create IF statement to check what to use) Is this right?

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

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
            Dim o As New SqlServer_Class
            Dim dt = o.GetTable_ITEM
            Me.DataGridView1.DataSource = dt
        End If
    End Sub
Updated 5-Jan-21 22:55pm

That's a bit complicated, depending on how you wrote the original application.
If you wrote it in layers, with a Presentation Layer (PL) to handle user interaction, a Business Layer (BL) to handle the business rules for your data, and a separate Data Layer (DL) to handle the actual storage then it becomes pretty simple (ish) - particularly if each Layer is in a separate Assembly.
All you'd have to do is create a new DL (based on eth original, but targeted to Access rather than MS SQL) and reference that in your app instead of the SQL layer.

But ... if you've written a monolithic app which uses the DB from all over the place then you have a major job of work on your hands, and it will be extremely difficult to test and maintain the software.

You can't cast SQL objects to Access, and even if you could, it wouldn't work: the Acccess syntax is different to MS SQL in some places - notably parameterised queries which I hope you are using throughout!
Share this answer
Samir Ibrahim 17-Aug-19 3:56am    
When I created my application, using 2 database was not in consideration. So yes, its "complicated"

Let Say I have one PL one BL two DL

How to tell the form from where to fetch the data?
OriginalGriff 17-Aug-19 4:19am    
The easiest way is to create an abstract DL class, which presents "agnostic" methods to the outside world.
Your two (or three, or four) different concrete DB classes derive from that and implement the methods for each specific DB engine.
The rest of the app uses the abstract class and it's methods, having set an instance of the appropriate concrete class at startup (or config, whichever).

But unless your app was designed for that, it's going to be a fair amount of work.
And looking at the code sample below ... you've thrown the existing code together without planning much in advance at all. It's going to be "complicated" for you do change!
Samir Ibrahim 17-Aug-19 4:41am    
Thank you for guiding.
OriginalGriff 17-Aug-19 4:53am    
You're welcome!
Samir Ibrahim 17-Aug-19 4:07am    
I can create 3rd DL?

Public Class dal3_class
Private _DbEngine As String
Sub New(DbEngine As String)
_DbEngine = DbEngine
End Sub

Function GetTable_ITEM As DataTable
Select Case _DbEngine
Case "A"
Dim o As New Access_Class
Dim dt = o.GetTable_ITEM
Return o

Case "S"
Dim o As New SqlServer_Class
Dim dt = o.GetTable_ITEM
Return o
End Select
End Function
End Class

Is this a good concept?
When you use an ORM it's easy to switch between databases (except for some complicated ones like NHibernate).
Here's an overview:[^]

Also see: XAF_how-to-change-connection-to-the-database-at-runtime[^]
And: A.Bekiaris's .Net / XAF Blog: Connecting to any database at runtime[^]
Share this answer
Samir Ibrahim 19-Aug-19 3:32am    
I downloaded the XPO ORM and checking how it work.

If I use it. I will end up creating class to handle the usage of 2 Data Model?
RickZeeland 19-Aug-19 4:44am    
Haven't used XPO myself, but it seems to me that you need only 1 data model.
Samir Ibrahim 19-Aug-19 7:24am    

The example given is for two database of the same engine..

I will check if it can be tweaked for two database of different engine.

it possible to connect multiple database with one application." Yes. Very straightforward - two connection strings, two different connections. If they're actually two different platforms like MySQL and SQL-Server, then you may need separate code libraries to access them, unless you use an ORM or other abstraction layer which can handle both without changes. Basically it's no different to using one database, except that you have 2. Same again if you have 20.
Share this answer
I ended up creating my own ORM using OleDbCommandBuilder

In my case MS ACCESS and MSSQL Server both worked fine using OleDb.OleDbConnection

Thank you everyone who put lights IN front of me.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900