Connect to either SQLS or JET/MDB

[Olaf Rabbachin]

Hi everybody,

I am building an application that needs to alternatively connect to either
SQL Server (2000) or a local Access backend. That is, if users are
connected to the LAN, they may use data from the SQL Server, but when
they're out in the field, they'll use the Access backend on their local PC.

Assuming that, on the SQLS side, a class handles all data-access and is
thus declared on the form-level I could also create a class that exposes
the exact same methods/properties as the SQLS-class.
A property in forms could then determine whether SQLS or MsAccess is to be
used and return the appropriate class'es object. In that case though, I
could not use Option Strict.
OTOH, each call to one of the both classes could be determined separately,
resulting in either the SQLS- or the MsAccess-class actually being used.
This, however, would involve many lines of redundant code.

Is there a more "elegant" way to switch between the two and still use
strong types?

Cheers & TIA,
Olaf

 

[Cor Ligthert [MVP]]

Olab,

Here a sample about the DBfactories

http://www.vb-tips.com/DataBaseFactory.aspx

Cor

 

[Guest]

Hi everybody,

I am building an application that needs to alternatively connect to
either SQL Server (2000) or a local Access backend. That is, if users
are connected to the LAN, they may use data from the SQL Server, but
when they're out in the field, they'll use the Access backend on their
local PC.

Better to use SQL Server Compact Edition - comes with database
synchronization built in.

Is there a more "elegant" way to switch between the two and still use
strong types?

Most OR/M frameworks provide this sort of functionality by providing a
common API for all database - LLBLGen Pro is the one I use and it's very
good. Although LLBLGen Pro typically targets 1 database at a time, I'm
pretty sure its possible to load up a different database connector layer
easily.

Another possiblity is to use a custom ADO.NET driver like UniDirect from
Corelabs:

http://crlab.com/unidirect/

Lastly as another posted mentioned, you could create a database factory.

 

[Olaf Rabbachin]

Hi,

Olab,

ahem. That would mean you'd be Con rather than Cor! ;-)

Here a sample about the DBfactories

http://www.vb-tips.com/DataBaseFactory.aspx

thanks for that - I didn't know about the DataBaseFactory class even though
that's not really what I was looking for as I have a class for both SQLS
and JET access.

However, I have found a way of dealing with this. Since both the SQLS and
the JET classes publish the exact same functionality, I have created a set
of interfaces that regulate/facilitate strongly typed access to the
respective class.
In the forms, I am now using a wrapper property that will return the
appropriate class. This works like a charm.

->
Private ReadOnly Property DataClass() As ILogBook_Conversation
Get
If My.Settings.UseSQLS Then
Return _cSQL_Conversation
Else
Return _cJET_Conversation
End If
End Get
End Property

Cheers,
Olaf

 

[Olaf Rabbachin]

Hi,

Better to use SQL Server Compact Edition - comes with database
synchronization built in.

I downloaded SQLSC 3.5 today and checked it out. Obviously, SQLSC only
supports Views and Tables, i.e. no SPs, no UDFs.
Since syncs are being carried out over a web-service (no 1:1 rep),
replication is not an option.

Is there a more "elegant" way to switch between the two and still use
strong types?

Most OR/M frameworks provide [...]

Thanks for that - I'll dig into that, too.

Cheers,
Olaf

 

[Guest]

I downloaded SQLSC 3.5 today and checked it out. Obviously, SQLSC only
supports Views and Tables, i.e. no SPs, no UDFs.
Since syncs are being carried out over a web-service (no 1:1 rep),
replication is not an option.

In this case look at using Microsoft Synchronization for ADO.NET, I believe
it supports web service synchronization.

SPs are personal preference really. UDFs, I rarely use them - but maybe
different in your case.

If you require these features, there is always SQL Server Express edition.

 

[Olaf Rabbachin]

Hi,

In this case look at using Microsoft Synchronization for ADO.NET, I believe
it supports web service synchronization.

well, another thing I never heard of. It seems that the number of times I
need to make that statement seems to grow exponentially these days ...

If you require these features, there is always SQL Server Express edition.

Yep. Too large of a footprint though for somewhat tiny requirements which
is why SQLSC looked interesting with its ~1.8 MB footprint. Seems I'll
stick with JET for now. Since the original problem with strongly typed
access has been solved, it's time to get done with the real work!

Thanks for your input!

Cheers,
Olaf