Re: Supporting both access and sql
From: Ralph (msnews.20.nt_consulting32_at_spamgourmet.com)
Date: 01/29/05
- Next message: Steve: "mdb file as a .Net resourve"
- Previous message: D: "Re: Supporting both access and sql"
- In reply to: D: "Re: Supporting both access and sql"
- Next in thread: D: "Re: Supporting both access and sql"
- Reply: D: "Re: Supporting both access and sql"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 29 Jan 2005 11:36:45 -0600
"D" <Dave@nothing.net> wrote in message
news:%23TEe39fBFHA.208@TK2MSFTNGP12.phx.gbl...
>
> "Ralph" <msnews.20.nt_consulting32@spamgourmet.com> wrote in message
> news:OTNNAebBFHA.2568@TK2MSFTNGP11.phx.gbl...
> >
> > "D" <Dave@nothing.net> wrote in message
> > news:OyXLMJXBFHA.3616@TK2MSFTNGP11.phx.gbl...
> >> I have a client with a rather large vb app that utilizes vb
environments.
> > He
> >> wants to move from access to sql BUT he would like to keep access
around
> > for
> >> his smaller clients who don't want to upgrade to sql and he doesn't
> >> really
> >> want to have 2 versions of his code base.
> >>
> >> What are my options to accomplish this?
> >>
> >> 1. Tons and tons of If WeAreInAccess = True Then .... Else ... End If
> > type
> >> statements?
> >>
> >> 1.a I know in c++ I could do a #if and it would depend on compiler
> >> variable during compilation as to which version to include in the final
> > exe,
> >> I'm not sure if vb has something like that or not
> >>
> >> 2. Still use Access but link the tables to the SQL database. Would that
> >> be
> > a
> >> big performance hit?
> >>
> >> Any other thoughts or ideas are appreciated.
> >>
> >> Thanks
> >>
> >
> > First, this is what ADO was designed for. You could essentially used
> > 'almost' identical code for both environments making it rather
> > transparent.
> > (Oddities would be date formats, some datatypes, wildcards, etc.).
> >
> > Create a Interface for two connection classes (MSAccess, SQLServer),
then
> > instance one or the other and pass that class to the rest of the
program.
> > You could probably 'port'/'convert' about 95% of your code this way
> > (depending on what you are doing - obviously using Access queries, data
> > reports, etc will not 'port' conveniently).
> >
> > On the long run what you will likely need to do is make sure your app is
> > structured with business objects.
> >
> > Not much detail, but should get you going in the right direction. Just
> > remember when ever you have a 'parameterized' class/object it likely
needs
> > to be specialized (inheritance).
> >
> > hth
> > -ralph
>
> Thanks for the info.
>
> The problems as I see them are
>
> 1. The code is laced with lots of sql queries throughout. They are just
> everywhere and in half of the forms where they're not, they are using vb
> environments
>
> 2. The sql queries are very access dependent where they use the brackets
and
> other fun Access formatting characters. And I'm sure there is alot of SQL
> reserved words too.
>
> 3. The program uses 5-7 Access databases one of which resides locally and
is
> used like a view is used in SQL.
>
> I suppose I could create a business object class and season it with 'if
> we're using Access' type things but it just seems like another layer. I
mean
> other than organizing the sql statements into a single class what benefit
> does it have? I'm still doing the 'if we're using Access' statements so it
> seems like alot of work for little benefit.
>
> Isn't there something where I can make the compiler use the correct
> environment depending on a switch?
>
> And how much of a performance hit am I taking by just using linked tables
> from access?
>
> Thanks
>
Where is a white board when you need one? <g>
First, as for performance. Whenever you use polymorphism in VB you have to
use another layer of indirection, usually thru Interfaces. There can be a
performance hit. How much? Is it tolerable? Worth doing? These are all
questions you need to determine based on YOUR problem domain and YOUR
service level agreements. There is no way of knowing up front. You just have
to test. Also there are always more than one way to structure things - which
one will be best for any one circustance? Test. There will likely not be one
single pattern you can apply to everywhere. In any application of any
complexity there will be sections best engineered one way, others will need
a different approach.
That is the main problem with attempting to optimize 'up front'. There may
be methods that appear a bit slower, but they are the only way to do it to
meet other requirements (a la polymorphism). As Bruce McKinney quoted in his
book - "It doesn't matter how fast it is, if it doesn't work!" <g>
As for 'switches', Select...Case and If...Then statements - forget them.
Don't go there, except in the very beginning to get things rolling. There
several methods of supporting polymorphism in VB, but they generally break
down into two basic versions - use a single interface two objects, use a
single object supporting two interfaces.
Using Two Interfaces/single object
' Some Interface
'Interface ISQLConnect
Public Function Conn() As ADODB.Connection
' Another Interface
'Interface IACCConnect
Public Function Conn() As ADODB.Connection ' or Get
' Some Object = CConn
' code for both in one module
Interface ISQLConnect
Interface IACCConnect
Private Function ISQLConnect_Conn() As ADODB.Connection
' create sqlserver connection
End Function
Private Function IACCConnect_Conn() As ADODB.Connection
' create msaccess connection
End Function
' some junk code - warning air code
Public Sub Main()
Dim MyConn As ADODB.Connection
Dim ISQL As ISQLConnect
Dim IACC As IACCConnect
If UsingSQL Then
Set ISQL = New CConn
MyConn = ISQL.Conn()
Else
Set IACC = New CConn
MyConn = IACC.Conn()
End If
...
Dim rs As ADODB.Recordset
Set rs = MyConn.Recordset()
...
End Sub
' Using separate object/modules (better usually, even though there is extra
work - copy and paste will do about 80% of the work for you.
' Interface IConn
Public Function Conn() As ADODB.Connection ' or procedure get
' SQLServer CSQLConn Object
Interface IConn
Private Function IConn_Conn() As ADODB.Connection
' create sql connection
IConn = whatever
End Function
' MSAccess CACCConn Object
Interface IConn
Private Function IConn_Conn() As ADODB.Connection
' Some code
Public Sub Main()
Dim IConnn As IConn
If usingSQL Then
Set IConn = New CSQLConn
Set MyConn = IConn.Conn()
Else
Set IConn = New CACCConn
Set MyConn = IConn.Conn()
End If
...
End Sub
hth
Hope I didn't take you to far around the south forty. <g>
Define you presentation with interfaces/modules then just 'swap-out' what is
needed at the time.
Another use technique is to not bring your data into the presentation and
fill a control, but send the control to the data class. As in...
' Interface ICustomer
Public Sub FetchCustNames( ctrLB As ListBox )
Public Sub SaveCustNames( ctrCB As ComboBox )
' Some Object CCustSQL
Interface ICustomer
Private Sub ICustomer_FetchCustNames( ctrLB As ListBox)
' call SQLServer SP
' retrieve rs
' code to fill the listbox from rs
End Sub
Private Sub ICustomer_SaveCustNames( ctrCB As ComboBox)
create parameter list from combo
' for each ....
call sp
ctrCB.Clear
End Sub
'Some Object CCustACC
Interface ICustomer
Private Sub ICustomer_FetchCustNames(ctrLB As ListBox)
' create an Access SQL string
' getrows()
' code to fill the listbox from getrows
End Sub
Private Sub ICustomer_SaveCustNames( ctrCB As ComboBox)
'create sql string
' command
ctrCB.Clear
End Sub
' Some code (air again)
Public Sub Main()
Dim Cust As ICustomer
If ....
Set Cust = New CCustACC or CCustSQL
... ' the rest of your code uses 'Cust'
Cust.FetchCustNames( lstCust )
Cust.SaveCustNames( cbCust )
make any sense?
-ralph
- Next message: Steve: "mdb file as a .Net resourve"
- Previous message: D: "Re: Supporting both access and sql"
- In reply to: D: "Re: Supporting both access and sql"
- Next in thread: D: "Re: Supporting both access and sql"
- Reply: D: "Re: Supporting both access and sql"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
