If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

more ADO stuff

I have been experimenting a little further with ADO and the use of DLL class
files for connectivity to an Access database table etc . Now I can not seem
to work out how to utilise the connection's recordset (two fields for two
text boxes) and display it in the two text boxes in the main project. I am
specifically trying to keep the database functions in the class file (referenced
from the main project files) and the display (GUI) stuff) in the main project................Can
someone please help ?

Re: more ADO stuff

Hmm, it looks like you've got the start of a Connection class, but you're
likely going to need either a recordset wrapping class to actually expose
records, or a collection container and data classes to use the connection
class to read, then store and retrieve the data as your form requires it.

I've tried the recordset approach with limited success, but I find the biggest
drawback is the record navigation and ensuring that the data on the form
is always in sync with the row pointed to by the RS. The bonus of this approach
is that it gives you access to built in sorting, filtering, and finding.

Typically I use a collection container that uses the connection to open a
recordset then create instances of data for each row and add them to a private
collection. This method indexes quite fast, but isn't ideal for large number
of rows. This way, your form can retrieve one or more of the objects from
the contained collection and access the data values through properties.

Re: more ADO stuff

Hi Steve. Is there any way that you can give me further info or source code
to get a better idea of what you refer to in your reply. Don't forget, I
Am a novice in this area and I do not understand everything about containers
etc, basis , yes!

Re: more ADO stuff

I understand that you want to separate the database functions and the GUI
stuff. That is good. Your running into complications because you've taken
the separation a little too far.

The biggest problem with your code is you have tightly coupled the ADO recordset
with the connection. With the design, each recordset will have it's own
connection. That is not good. Connections are an expensive, slow process
and in most DBs (not Access) you will have a licence limit on the number
you can open. So, if your making a client-server architecture, you want
to share "one" connection for all the queries (and recordsets) that you
run.

So, to start, you want a class that opens a connection and does the basic
DB operations. It should also be able to execute queries (Select and Update/Delete/Insert
(sometimes called CRUD). In addition it must allow transaction handling
(start/ commit) transactions.

Sub ExecuteQuerySelect (strSQL as string, adoRec as ADODB.Recordset )
'only thing left to handle are selects, which return data
set adoRec = new ADODB.Recordset
set adoRec = mADOConn.Execute (strSQL)
End Sub

Sub BeginTrans
moADOConn.BeginTrans
End Sub

Sub CommitTrans
moADOConn.CommitTrans
End Sub

====================================

When you use this class, you want to create to create only one instance of
it and then use that instance throughout your project. If you create more
than one instance you'll make more than one connection.

You'll notice that I did not try to encapsulate the ADO Recordset in this
class. Think of the ADO Recordset as an application form in some office
building. The application get filled out by one person, partially approved
in one department, the rest approved in another, and it is filed in yet
another (must be a govt office!). The application form simply acts to hold
and transfer information between the departments (layers). It is not a part
of any department. You refered to the Recordset as a part of the DB funtions.
Don't think of it that way. Think of it as something that is used to transfer
info between layers (i.e. the DB and the UI).

So feel free to use Recordsets in the UI layer. To use this class, you want
to create an ADO recordset in the UI layer, send it to the DB via the class,
then manipulate it locally.

Re: more ADO stuff

I'm not sure I see much of the benefit here...maybe it's just me...but wrappering
all this in a class seems to be serious overkill...it's one thing to wrapper
your connection properties into an object, so that the connection to the
datasource is isolated and can be reused over and over...but, I would think
you'd be better off just returning an ADO recordset to your application and
leave it at that.

I've seen (over the years) a number of projects where the developers 'wrappered'
recordsets into objects...and while there were some code 'reuse' advantages
in some (not all) of those projects...mostly I just found performance problems
that didn't end up being a good tradeoff for the functionality developed...i.e.
the stuff they were doing just ended up being a 'class' that did the same
darn stuff that the recordset supported natively...move, search, sort, etc...and
it was just extra code to execute (performance).

All I'm saying is make sure your 'design' here doensn't get in the way of
your application...not everything needs to be 'classed' to death just for
the sake of building objects...that's what ADO is, your object into the database...not
much need to class it unless your app REALLY requires the data it deals with
to be a 'class'...

Re: more ADO stuff

It depends on the problem. It can certainly be overkill if you're working
on a simple small application that isn't going to be changed very often,
but a couple of scenarios where it can be useful are:

When your schema can be changed rapidly. If your Forms and objects access
an ADO recordset directly, accessing fields directly can be a pain in the
butt if field names change. Aliasing can avoid most of these situations.

When you need the flexibility to attach to different data sources. If a class
wraps your recordset, it doesn't matter what datasource schema you connect
to, your presentation layer only talks to business objects, not the data
layer directly. It is much easier to "mark" what fields are and are not available
to keep your PL isolated from the data.

Encapsulating validation code. As properties are changed, or as you issue
a Save call, validation code can be executed before the data is actually
changed. (Rule validation, simple things like length checking, [for fields
that don't support MaxLength] etc.)

Overall I'll wrap RS's or introduce collection containers to define a contract
for accessing data. Exposing a recordset offers free reign over who changes
values, and performs actions like updates, or even introduces bugs by doing
something like closing the RS.

Steve.

"Chris Hylton" <cchylton@hotmail.com> wrote:
>
>I'm not sure I see much of the benefit here...maybe it's just me...but wrappering
>all this in a class seems to be serious overkill...it's one thing to wrapper
>your connection properties into an object, so that the connection to the
>datasource is isolated and can be reused over and over...but, I would think
>you'd be better off just returning an ADO recordset to your application
and
>leave it at that.
>
>I've seen (over the years) a number of projects where the developers 'wrappered'
>recordsets into objects...and while there were some code 'reuse' advantages
>in some (not all) of those projects...mostly I just found performance problems
>that didn't end up being a good tradeoff for the functionality developed...i.e.
>the stuff they were doing just ended up being a 'class' that did the same
>darn stuff that the recordset supported natively...move, search, sort, etc...and
>it was just extra code to execute (performance).
>
>All I'm saying is make sure your 'design' here doensn't get in the way of
>your application...not everything needs to be 'classed' to death just for
>the sake of building objects...that's what ADO is, your object into the
database...not
>much need to class it unless your app REALLY requires the data it deals
with
>to be a 'class'...
>
>Chris

Re: more ADO stuff

Guy, thanks for all that. It makes things a bit clearer. However, there is
one little point, as a novice to ADO, that I must ask and that is

In your ExecuteQuerySelect sub...why do you have two lines with
'set adoRec' ? (I'm not sure about this bit)

"Guy Smith" <no@email.com> wrote:
>
>I understand that you want to separate the database functions and the GUI
>stuff. That is good. Your running into complications because you've taken
>the separation a little too far.
>
>The biggest problem with your code is you have tightly coupled the ADO recordset
>with the connection. With the design, each recordset will have it's own
>connection. That is not good. Connections are an expensive, slow process
>and in most DBs (not Access) you will have a licence limit on the number
>you can open. So, if your making a client-server architecture, you want
>to share "one" connection for all the queries (and recordsets) that you
>run.
>
>So, to start, you want a class that opens a connection and does the basic
>DB operations. It should also be able to execute queries (Select and Update/Delete/Insert
>(sometimes called CRUD). In addition it must allow transaction handling
>(start/ commit) transactions.
>
>So (writing on the fly....) in a Class Module
>
>====================================
>Private mADOConn as ADOConnection
>
>Sub OpenConnection(strConnect as string)
> Set mADOConn = New ADODB.Connection
> mADOConn.Open strConnect
>End Sub
>
>Sub CloseConnection()
> mADOConn.Close
> (probably want to set moADOConn = Nothing in the class destructor)
>End Sub
>
>Sub ExecuteQueryAction(strSQL as string)
> 'this will handle "Deletes", "Updates" and "Inserts"
> mADOConn.Execute(strSQL)
>End Sub
>
>Sub ExecuteQuerySelect (strSQL as string, adoRec as ADODB.Recordset )
> 'only thing left to handle are selects, which return data
> set adoRec = new ADODB.Recordset
> set adoRec = mADOConn.Execute (strSQL)
>End Sub
>
>Sub BeginTrans
> moADOConn.BeginTrans
>End Sub
>
>Sub CommitTrans
> moADOConn.CommitTrans
>End Sub
>
>====================================
>
>When you use this class, you want to create to create only one instance
of
>it and then use that instance throughout your project. If you create more
>than one instance you'll make more than one connection.
>
>You'll notice that I did not try to encapsulate the ADO Recordset in this
>class. Think of the ADO Recordset as an application form in some office
>building. The application get filled out by one person, partially approved
>in one department, the rest approved in another, and it is filed in yet
>another (must be a govt office!). The application form simply acts to hold
>and transfer information between the departments (layers). It is not a
part
>of any department. You refered to the Recordset as a part of the DB funtions.
> Don't think of it that way. Think of it as something that is used to transfer
>info between layers (i.e. the DB and the UI).
>
>So feel free to use Recordsets in the UI layer. To use this class, you
want
>to create an ADO recordset in the UI layer, send it to the DB via the class,
>then manipulate it locally.
>
>Hope this helps!?
>
>Guy Smith

Re: more ADO stuff

"Garry Charles" <grc-the-master@brain-cpu.freeserve.co.uk> wrote:
>
>
>Guy, thanks for all that. It makes things a bit clearer. However, there
is
>one little point, as a novice to ADO, that I must ask and that is
>
>In your ExecuteQuerySelect sub...why do you have two lines with
>'set adoRec' ? (I'm not sure about this bit)

Oops... I apologize, that is my fault (typing too fast). You don't want
to set it twice. Remove the line "set adoRec = new ADODB.Recordset". It
shouldn't be there.

Re: more ADO stuff

Yea, I definitely see your point...I typically don't use recordsets in that
manner anyway...I'm a bigger fan of coding this into the database, but that's
probably 'old hat' for most OO folks...business objects are nice, but most
of my projects have always ended up stressing performance over flexibility...I
guess that's why I have concern over wrapping recordsets in classes. I've
seen orders of magnitude on performance hits when doing this. I guess if
you load the class and then destroy the recordset this isn't near the issue
in the cases I've seen where the recordset is continually accessed inside
the object.

I tend to use disconnected recordsets and never allow direct access to the
database through a recordset. Even when I build business objects, I still
access the database via stored procedures, so the recordset never becomes
an issue, it's only a data retrieval mechanism. This is pretty much the
direction of .NET anyway, so changing mindsets won't be too difficult.

I was just pointing out a common issue I see with wrappering objects with
things already developed that handle the majority of application needs.
But, when the business rules require it, there can definitely be a big advantage
to the way a business object is designed...hope you didn't think I was trying
to rain on your parade...just pointing out an observation to keep in mind
in general design work.

Chris

"Steve" <stevepyn@hotmail.com> wrote:
>
>It depends on the problem. It can certainly be overkill if you're working
>on a simple small application that isn't going to be changed very often,
>but a couple of scenarios where it can be useful are:
>
>When your schema can be changed rapidly. If your Forms and objects access
>an ADO recordset directly, accessing fields directly can be a pain in the
>butt if field names change. Aliasing can avoid most of these situations.
>
>When you need the flexibility to attach to different data sources. If a
class
>wraps your recordset, it doesn't matter what datasource schema you connect
>to, your presentation layer only talks to business objects, not the data
>layer directly. It is much easier to "mark" what fields are and are not
available
>to keep your PL isolated from the data.
>
>Encapsulating validation code. As properties are changed, or as you issue
>a Save call, validation code can be executed before the data is actually
>changed. (Rule validation, simple things like length checking, [for fields
>that don't support MaxLength] etc.)
>
>Overall I'll wrap RS's or introduce collection containers to define a contract
>for accessing data. Exposing a recordset offers free reign over who changes
>values, and performs actions like updates, or even introduces bugs by doing
>something like closing the RS.
>
>Steve.
>
>"Chris Hylton" <cchylton@hotmail.com> wrote:
>>
>>I'm not sure I see much of the benefit here...maybe it's just me...but
wrappering
>>all this in a class seems to be serious overkill...it's one thing to wrapper
>>your connection properties into an object, so that the connection to the
>>datasource is isolated and can be reused over and over...but, I would think
>>you'd be better off just returning an ADO recordset to your application
>and
>>leave it at that.
>>
>>I've seen (over the years) a number of projects where the developers 'wrappered'
>>recordsets into objects...and while there were some code 'reuse' advantages
>>in some (not all) of those projects...mostly I just found performance problems
>>that didn't end up being a good tradeoff for the functionality developed...i.e.
>>the stuff they were doing just ended up being a 'class' that did the same
>>darn stuff that the recordset supported natively...move, search, sort,
etc...and
>>it was just extra code to execute (performance).
>>
>>All I'm saying is make sure your 'design' here doensn't get in the way
of
>>your application...not everything needs to be 'classed' to death just for
>>the sake of building objects...that's what ADO is, your object into the
>database...not
>>much need to class it unless your app REALLY requires the data it deals
>with
>>to be a 'class'...
>>
>>Chris
>

Re: more ADO stuff

Firstly, I’d like to say thanks to Guy and Steve for their help so far.
Anyway, here’s my code (after adding Steve’s latest help hint). The project
basically comprises of text boxes to display data (through the recordset)
and buttons for navigation. My “add” record functions do not yet work properly,
typical for a beginner eh?

Any additional constructive criticism is welcome…(via email if necessary)